dba-diary-blog - Tumblr blog

Oracle DBA Cheet Sheet

Tablespace & Datafile Details ============================= set lines 200 pages 200 col tablespace_name for a35 col file_name for a70 select file_id, tablespace_name, file_name, bytes/1024/1024 MB, status from dba_data_files;

Table Analyze Details ===================== set lines 200 pages 200 col owner for a30 col table_name for a30 col tablespace_name for a35 select owner, table_name, tablespace_name, NUM_ROWS, LAST_ANALYZED from dba_tables where owner='&TableOwner' and table_name='&TableName';

Session Details =============== set lines 200 pages 200 col MACHINE for a25 select inst_id, sid, serial#, username, program, machine, status from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and username is not null order by 1; select inst_id, username, count(*) "No_of_Sessions" from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and username is not null and status='INACTIVE' group by inst_id, username order by 3 desc; select inst_id, username, program, machine, status from gv$session where machine like '%&MachineName%' and username is not null order by 1;

Parameter value =============== set lines 200 pages 200 col name for a35 col value for a70 select inst_id, name, value from gv$parameter where name like '%&Parameter%' order by inst_id;

User Details ============= set lines 200 pages 200 col username for a30 col profile for a30 select username, account_status, lock_date, expiry_date, profile from dba_users where username like '%&username%' order by username;

List and Remove Files and directories ===================================== ls |grep -i cdmp_20110224|xargs rm -r

Tablespace Usage (1) ==================== set pages 999; set lines 132; SELECT * FROM ( SELECT c.tablespace_name, ROUND(a.bytes/1048576,2) MB_Allocated, ROUND(b.bytes/1048576,2) MB_Free, ROUND((a.bytes-b.bytes)/1048576,2) MB_Used, ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free, ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used FROM ( SELECT tablespace_name, SUM(a.bytes) bytes FROM sys.DBA_DATA_FILES a GROUP BY tablespace_name ) a, ( SELECT a.tablespace_name, NVL(SUM(b.bytes),0) bytes FROM sys.DBA_DATA_FILES a, sys.DBA_FREE_SPACE b WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+) GROUP BY a.tablespace_name ) b, sys.DBA_TABLESPACES c WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name ) WHERE tot_Pct_Used >=0 ORDER BY tablespace_name;

Tablespace usage (2) ==================== select d.tablespace_name, d.file_name, d.bytes/1024/1024 Alloc_MB, f.bytes/1024/1024 Free_MB from dba_data_files d, dba_free_space f where d.file_id=f.file_id order by 1;

select d.tablespace_name, sum(d.bytes/1024/1024) Alloc_MB, sum(f.bytes/1024/1024) Free_MB from dba_data_files d, dba_free_space f where d.file_id=f.file_id group by d.tablespace_name order by 1;

Datafile added to Tablespace by date ==================================== select v.file#, to_char(v.CREATION_TIME, 'dd-mon-yy hh24:mi:ss') Creation_Date, d.file_name, d.bytes/1024/1024 MB from dba_data_files d, v$datafile v where d.tablespace_name='XXGTM_DAT' and d.file_id = v.file#;

Added in last 72 hours ====================== select v.file#, to_char(v.CREATION_TIME, 'dd-mon-yy hh24:mi:ss') Creation_Date, d.file_name, d.bytes/1024/1024 MB from dba_data_files d, v$datafile v where d.tablespace_name='XXGTM_DAT' and d.file_id = v.file# and v.creation_time > sysdate - 20;

Monitor SQL Execution History (Toad) ==================================== Set lines 200 pages 200 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, rows_processed_total Total_rows, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta)) avg_pio,SQL_PROFILE from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = '9vv8244bcq529' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3;

Check SQL Plan ============== select * from table(DBMS_XPLAN.DISPLAY_CURSOR('9vv8244bcq529'));

OHS Version ============ export ORACLE_HOME=/apps/envname/product/fmw LD_LIBRARY_PATH=$ORACLE_HOME/ohs/lib:$ORACLE_HOME/oracle_common/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

cd /apps/envname/product/fmw/ohs/bin

/apps/envname/product/fmw/ohs/bin > ./httpd -version

Find duplicate rows in a table. =============================== set lines 1000 col ACTIVATION_ID for a50; col USER_ID for a30; SELECT ACTIVATION_ID, LFORM_ID,USER_ID FROM DBA_BTDEL1.LMS_LFORM_ACTIVATION GROUP BY ACTIVATION_ID, LFORM_ID,USER_ID HAVING count(*) > 1;

Partition Tables in database ============================ set lines 200 pages 200 col owner for a30 col table_name for a30 col partition_name for a30 select t.owner, t.table_name, s.PARTITION_NAME, s.bytes/1024/1024 MB from dba_tables t, dba_segments s where t.partitioned = 'YES' and t.owner not in ('SYS','SYSTEM') and t.table_name=s.segment_name order by 2, 4;

Who is using my system tablespace ================================= select owner, segment_type, sum(bytes/1024/1024) MB, count(*), tablespace_name from dba_segments where tablespace_name in ('SYSTEM','SYSAUX') group by owner, segment_type, tablespace_name order by 1;

What are the largest/biggest tables of my DB. ============================================= col segment_name for a30 Select * from (select owner, segment_name, segment_type, bytes/1024/1024 MB from dba_segments order by bytes/1024/1024 desc) where rownum <=30;

ASM Disk Group Details ====================== cd /oracle/product/grid_home/bin ./kfod disks=all asm_diskstring='ORCL:*' -------------------------------------------------------------------------------- Disk Size Path User Group ================================================================================ 1: 557693 Mb ORCL:DBPRD_AR_544G_01 2: 557693 Mb ORCL:DBPRD_DT01_544G_01 3: 557693 Mb ORCL:DBPRD_FRA_544G_01 4: 16378 Mb ORCL:DBPRD_RC_16G_001 5: 16378 Mb ORCL:DBPRD_RC_16G_002 6: 16378 Mb ORCL:DBPRD_RC_16G_003 7: 16378 Mb ORCL:DBPRD_RC_16G_004 8: 16378 Mb ORCL:DBPRD_RC_16G_005 9: 16378 Mb ORCL:DBPRD_RC_M_16G_001 10: 16378 Mb ORCL:DBPRD_RC_M_16G_002 11: 16378 Mb ORCL:DBPRD_RC_M_16G_003 12: 16378 Mb ORCL:DBPRD_RC_M_16G_004 13: 16378 Mb ORCL:DBPRD_RC_M_16G_005 14: 1019 Mb ORCL:GRID_NPRD_3026_CL_A_1G_1 15: 1019 Mb ORCL:GRID_NPRD_3026_CL_A_1G_2 16: 1019 Mb ORCL:GRID_NPRD_3026_CL_B_1G_1 17: 1019 Mb ORCL:GRID_NPRD_3026_CL_B_1G_2 18: 1019 Mb ORCL:GRID_NPRD_3026_CL_C_1G_1 19: 1019 Mb ORCL:GRID_NPRD_3026_CL_C_1G_2

./kfod disks=all asm_diskstring='/dev/oracleasm/disks/*' -------------------------------------------------------------------------------- Disk Size Path User Group ================================================================================ 1: 557693 Mb /dev/oracleasm/disks/DBPRD_AR_544G_01 oracle dba 2: 557693 Mb /dev/oracleasm/disks/DBPRD_DT01_544G_01 oracle dba 3: 557693 Mb /dev/oracleasm/disks/DBPRD_FRA_544G_01 oracle dba 4: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_001 oracle dba 5: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_002 oracle dba 6: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_003 oracle dba 7: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_004 oracle dba 8: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_005 oracle dba 9: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_001 oracle dba 10: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_002 oracle dba 11: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_003 oracle dba 12: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_004 oracle dba 13: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_005 oracle dba 14: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_A_1G_1 oracle dba 15: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_A_1G_2 oracle dba 16: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_B_1G_1 oracle dba 17: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_B_1G_2 oracle dba 18: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_C_1G_1 oracle dba 19: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_C_1G_2 oracle dba

Clear SQL Cache =============== SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS HASH_VALUE ---------------- ---------- 000000085FD77CF0 808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Thread/dump =========== jstack -l <pid> > <file-path> kill -3 pid

Get the object name with block ID ================================== SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300

COLUMN segment_name FORMAT A24 COLUMN segment_type FORMAT A24

SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks ) ) /

DB link details ================ col DB_LINK for a30 col OWNER for a30 col USERNAME for a30 col HOST for a30 select * from dba_db_links;