Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 20 March 2013

Frequently Used Stuff (FAQ) for DBA

Query Database Object Information
=======================================================================================================
The following contains information on how to retrieve database information for Oracle ‎objects such as tables, views, indexes, packages, procedures, functions, and triggers. All ‎queries are related with oracle system views located in the SYS schema.‎
List all Tables for current user‎:
Select TABLE_NAME, OWNER from SYS.ALL_TABLES order by OWNER, TABLE_NAME;
The query can be filtered to return tables for a given schema by adding a where OWNER ‎‎= 'some_schema' clause to the query.‎
List all schemas for current user‎:
Select USERNAME from SYS.ALL_USERS order by USERNAME;
List all views for current user‎:
Select VIEW_NAME, OWNER from SYS.ALL_VIEWS order by OWNER, VIEW_NAME;
The query can be filtered to return views for a specific schema by adding a where ‎OWNER = 'some_schema' clause to the query.‎
List all Packages for current user:‎
Select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'PACKAGE' order by OWNER, OBJECT_NAME;
The query can be filtered to return packages for a specific schema by adding a where ‎OWNER = 'some_schema' clause to the query.‎
‎List all Procedures for current user‎:
select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('PROCEDURE') order by OWNER, OBJECT_NAME;
The query can be filtered to return procedures for a specific schema by adding a where ‎OWNER = 'some_schema' clause to the query.‎
List Procedure Columns for current user:‎
select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from SYS.ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE;
‎List all functions for current user‎:
Select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('FUNCTION') order by OWNER, OBJECT_NAME;
The query can be filtered to return functions for a specific schema by adding a where ‎OWNER = 'some_schema' clause to the query.‎
List all Triggers for the current user: ‎
Select TRIGGER_NAME, OWNER from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME;
The query can be filtered to return triggers for a specific schema by adding a where ‎OWNER = 'some_schema' clause to the query.‎
List all indexes for current user:
Select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;
How to find the last record from the table:
Select * from partial_payment_sequence where rowid in(select max(rowid) from partial_payment_sequence);
Select * from partial_payment_sequence minus select * from partial_payment_sequence where rownum < (select count(*) from partial_payment_sequence);
Find 5 Largest Object in Database:
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
Last DDL performed in Database:
Select CREATED, TIMESTAMP, last_ddl_time from all_objects WHERE OWNER='HRMS' AND OBJECT_TYPE='TABLE' order by timestamp desc;
Number of Objects Created in last week:
Select count(1) from user_objects where CREATED >= sysdate - 7;
Count Invalid Object in database:
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by  owner, object_type;
=======================================================================================================
Database User/session Activities:
=======================================================================================================
List users with tablespace:‎
Select Username, Tablespace_Name, (Bytes/1024/1024) Mb  From Dba_Ts_Quotas
where username='&username';
List User Created Date:
SELECT username, created FROM DBA_USERS
where lower(username) = lower ('&username');‎
List user role privilege:‎
Select * From   Dba_Role_Privs Where Grantee='&Grantee';
List user sys privilege:‎
Select * From   Dba_Sys_Privs Where Grantee='&Grantee';
Lock/Unlock User Account:‎
Alter User Shahid1 Account Lock;
Alter User Shahid1 Account Unlock;‎
Changing user password:‎
Alter user SHAHID1 identified by ahmed;
Changing default tablespace of user:‎
Alter user SHAHID1 identified by ahmed default tablespace example;
Assigning new profile to the user:‎
Alter User Shahid1 Profile New_Profile;
Assigning default Role exception some role:‎
Alter User Shahid1  Default Role All Except Role_Manager;
Expiring user password:‎
Alter User Shahid1 Password Expire;
Proxy user authentication:‎
Alter User Hrms Grant Connect Through Oas1  Authenticated Using Password;
Take away the right of proxy user:‎
Alter user HRMS REVOKE connect through OAS1;
Last time a session performed any activity?‎
select username, floor(last_call_et / 60) "Minutes", status from v$session
where username is not null order by last_call_et;
Return session id on remote session:‎
SELECT distinct sid FROM v$mystat;
Return session id of you in remote Env:‎
select sid from v$mystat@sadhan.world where rownum=1;
How to get current session id, process id, client process id?
select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr and b.audsid = userenv('sessionid');
How to check Last Query fired by the users:
Select S.USERNAME||'('||s.sid||')-'||s.osuser UNAME, s.sid||'/'||s.serial# sid,s.status "Status",p.spid,sql_text sqltext from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address and p.addr=s.paddr(+) and t.hash_value = s.sql_hash_value
order by s.sid,t.piece;
How can be determining the size of the log files.
Select sum(bytes)/1024/1024 "size_in_MB" from v$log;
How to find whether the instance is started with pfile or spfile
SELECT name, value FROM v$parameter WHERE name = 'spfile';
=======================================================================================================
Database General Information:
=======================================================================================================
Database Version Information:‎
Select * from v$version;
Database default information:‎
Select username,profile,default_tablespace,temporary_tablespace from dba_users;
Database Character Set Informations:‎
Select * from nls_database_parameters;
Return database last startup time:
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
List Database ID and Name:
SELECT DBID, NAME FROM V$DATABASE;
Return database Global name:
SELECT * FROM GLOBAL_NAME;‎
List Database Host Information:
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
Return Database used space:
SELECT SUM(bytes)/1024/1024/1024 "GB" FROM dba_segments;
List Non-Sys owned tables in SYSTEM Tablespace:
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');
To check Tablespace Free space:
SELECT TABLESPACE_NAME, SUM(BYTES/1024/1024) "Free_Size (MB)"  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
To check  datafile Free space:
SELECT tablespace_name, File_id, SUM(bytes/1024/1024)"Size (MB)" FROM DBA_FREE_SPACE
group by tablespace_name, file_id;
To check Temp segment free space:
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header GROUP  BY tablespace_name;
Logon Time of DB user and OS user:
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') "LOGON_TIME",osuser,status,schemaname,machine from v$session where type !='BACKGROUND';
To Check DB Corruption or Need of Recovery:
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS, r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
SELECT * FROM   v$recovery_status;
SELECT * FROM   v$recover_file;
SELECT * FROM   v$recovery_file_status;
SELECT * FROM   v$recovery_log;
How to get list of all database View?
SQL> SELECT * FROM DICT;
=======================================================================================================
Database Maintenance:
=======================================================================================================
How to Find the Number of Oracle Instances Running on Windows Machine?
C:\>net start |find "OracleService"
How to know Number of CPUs on Oracle?
Login as SYSDBA and query on SQL>show parameter cpu_count;
How you will check flashback is enabled or not?
Select flashback_on from v$database;
How to Remove Oracle Service in windows environment?
C:\Oradim –delete –sid
-or- C:\Oradim –delete –svrc
How to change the topnsql of AWR Snapshot in 10g?
Select * from DBA_HIST_WR_CONTROL
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql => 30);
Select * from DBA_HIST_WR_CONTROL
How to detect who’s causing excessive redo generation?
SELECT S1.SID, S1.SERIAL#, S1.USERNAME, S1.PROGRAM, T1.USED_UBLK, T1.USED_UREC FROM V$SESSION S1, V$TRANSACTION T1 WHERE S1.TADDR = T1.ADDR ORDER BY 5 DESC, 6 DESC, 1, 2, 3, 4;
Tracking undo generation by all session:
SELECT S1.SID, S1.USER_NAME, R1.NAME, T1.START_TIME, T1.USED_UBLK , T1.USED_UREC FROM V$SESSION S1, V$TRANSACTION T1, V$ROLLNAME R1 WHERE T1.ADDR = S1.TADDR AND R1.USN = T1.XIDUSN;
How to check Fragmentation in Tablespace:
Select * from dba_free_space
where tablespace_name = 'RTBS' and bytes <= ( select min(next_extent)
from dba_segments where tablespace_name = 'RTBS') order by block_id;
How to convert database server sysdate to GMT date:
Select sysdate, sysdate+(substr(tz_offset(dbtimezone),1,1)||’1’)*to_dsinterval(‘0
‘||substr(tz_offset( DBTIMEZONE ),2, 5)||’:00’) from dual;
How can we see the oldest flashback available?
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate - f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;
How to determine whether the datafiles are synchronized with checkpoint or not?
select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY ‎HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
Check the results of the above query if it returns one and only one row for the online datafiles, means they are already synchronized in terms of their SCN. Otherwise the datafiles are still not synchronized yet.
How you will find Oracle timestamp from current SCN?
select dbms_flashback.get_system_change_number scn from dual; -- Oracle Ver. 9i
Select to_char(CURRENT_SCN) from v$database;  -- oracle Ver. 10g or above
select current_scn, dbms_flashback.get_system_change_number from v$database;  --standby case
select scn_to_timestamp(8843525) from dual;
How to find parameters that will take into effect for new sessions?
SELECT name, value FROM v$parameter WHERE issys_modifiable = 'DEFERRED';
You can change the parameter using the deferred option:
SQL> alter system set sort_area_size=65538 deferred;
How to see the jobs currently being executed?
select djr.sid, djr.job, djr.failures, djr.this_date, djr.this_sec, dj.what from dba_jobs_running djr, dba_jobs dj where djr.job = dj.job;

0 comments:

Post a Comment