Posts by Prasanna Padmanabhan

Query to generate AWR report

set lines 500; set pages 500; set long 1000000; SELECT X.SQL_ID, X.CPU_TIME, X.EXECUTIONS, T.SQL_TEXT FROM DBA_HIST_SQLTEXT T, ( SELECT S.SQL_ID SQL_ID, SUM(S.CPU_TIME_DELTA/1000000) CPU_TIME, SUM(S.EXECUTIONS_DELTA) EXECUTIONS FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT P…

Read More

Script to generate statspack report

set lines 300; set pages 500; set long 1000000; select A.hash_value, A.text_subset, A.module, trunc((B.cpu_time-A.cpu_time)/1000) “CPU_TIME(ms)”, B.executions-A.executions executions, trunc(decode(B.executions-A.executions, 0, 0, (B.cpu_time-A.cpu_time)/(B.executions-A.executions))/1000) “CPU_TIME_PER_EXEC(ms)” from STATS$SQL_SUMMARY  A, STATS$SQL_SUMMARY  B where A.hash_value =…

Read More

Script to find Session_longops query for datapump

select x.job_name,b.state,b.job_mode,b.degree , x.owner_name,z.sql_text, p.message , p.totalwork, p.sofar , round((p.sofar/p.totalwork)*100,2) done , p.time_remaining from dba_datapump_jobs b left join dba_datapump_sessions x on (x.job_name = b.job_name) left join v$session y on (y.saddr…

Read More

Script to Identify the Datapump Progress

set lines 150 pages 100 numwidth 7 col program for a38 col username for a10 col spid for a7 select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#,…

Read More

Enable trace at User Level by using this trigger

CREATE OR REPLACE TRIGGER “123456_TMP_LOGONTRIG” AFTER LOGON ON DATABASE begin if ora_login_user = ‘123456’ then execute immediate ‘Alter session set events ”10046 trace name context forever, level 12”’; execute immediate…

Read More

Claim Space from Table/User Level

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||’MB’ “TOTAL_SIZE”, round((num_rows*avg_row_len /1024/1024),2)||’Mb’ “ACTUAL_SIZE”, round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE” from dba_tables where owner in(‘a’,’b’,’c’,’d’) and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 1000 order by 8 desc; select table_name,round((blocks*8),2) “size (mb)” , round((num_rows*avg_row_len/1024),2) “actual_data (mb)”,…

Read More

Query to find Fragmentation on Database

select table_name,round((blocks*8),2) “size (mb)” , round((num_rows*avg_row_len/1024),2) “actual_data (mb)”, (round((blocks*8),2) – round((num_rows*avg_row_len/1024),2)) “wasted_space (mb)” from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and owner=’&a’ order by 4 desc; set lines 170 set pages…

Read More