tutorailwale Datewise Occurrence Of ORA-1555 SELECT To_char(begin_time, ‘mm/dd/yyyy hh24:mi’) “Int. Start”,ssolderrcnt “ORA-1555s”,maxquerylen “Max Query”,unxpstealcnt “UNExp SCnt”,unxpblkrelcnt “UnEXPblks”,expstealcnt “Exp SCnt”,expblkrelcnt “ExpBlks”,nospaceerrcnt nospaceFROM v$undostatWHERE ssolderrcnt > 0ORDER BY … Read more
To Check Space Related Statistics Of Undo Tablespace selectsum(ssolderrcnt) ‘Total ORA-1555s’, round(max(maxquerylen)/60/60) ‘Max Query HRS’, SUM(unxpstealcnt) ‘UNExpired STEALS’,SUM(expstealcnt) ‘Expired STEALS’FROM v$undostatORDER BY begin_time; Read More … Read more
To Check Space Related Statistics Of Undo Tablespace SELECT undoblks,begin_time,maxquerylen,unxpstealcnt,expstealcnt,nospaceerrcntFROM stats$undostatWHERE begin_time BETWEEN SYSDATE – 90 AND SYSDATEAND unxpstealcnt > 0; Read More Articles To … Read more
To Show Undo Retention Value Show parameter undo_retention; Undo retention in hourscol “Retention” FOR a30col name FOR a30col value FOR a50SELECT name ‘Retention’,value / 60 … Read more
Extent Count And Total Blocks set linesize 152col tablespace_name FOR a20col status FOR a10SELECT tablespace_name,status,Count(extent_id) “Extent Count”,SUM(blocks) “Total Blocks”,SUM(bytes) / (1024 * 1024 * 1024) … Read more
To Show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace SELECT tablespace_name,status,Count(extent_id) “Extent Count”,SUM(blocks) “Total Blocks”,SUM(blocks) * 8 / ( 1024 * 1024 ) total_spaceFROM dba_undo_extentsGROUP BY tablespace_name,status; … Read more
To Check Retention Guarantee For Undo Tablespace SELECT tablespace_name,status,CONTENTS,logging,retentionFROM dba_tablespacesWHERE tablespace_name LIKE ‘%UNDO%’; Read More Articles Inactive JDBC Sessions Inactive Modules Inactive Program Toral Inactive … Read more
Inactive JDBC Sessions set pagesize 50col inst_id FOR 99col spid FOR a15set linesize 155col program FOR a15col action format a15col logon_time format a20col MODULE format … Read more
Inactive Modules SELECT p.spid,s.sid,s.last_call_et / 3600 last_call_et_Hrs,s.status,s.action,s.MODULE,s.program,t.disk_reads,Lpad(t.sql_text, 30) “Last SQL”FROM gv$session s,gv$sqlarea t,gv$process pWHERE s.sql_address = t.addressAND s.sql_hash_value = t.hash_valueAND p.addr = s.paddrAND s.MODULE LIKE … Read more
Inactive Program SELECT p.spid,s.sid,s.last_call_et / 3600 last_call_et_Hrs,s.status,s.action,s.MODULE,s.program,t.disk_reads,Lpad(t.sql_text, 30) “Last SQL”FROM gv$session s,gv$sqlarea t,gv$process pWHERE s.sql_address = t.addressAND s.sql_hash_value = t.hash_valueAND p.addr = s.paddrAND s.status = … Read more