以下脚本可以用于收集Automatic Undo Management的必要诊断信息，以sysdba身份运行:
spool Undo_Diag.outttitle offset pages 999set lines 150set verify offset termout offset trimout onset trimspool onREMREM ------------------------------------------------------------------------REMREM -----------------------------------------------------------------REMset space 2REM REPORTING TABLESPACE INFORMATION:REMREM This looks at Tablespace Sizing - Total bytes and free bytesREMcolumn tablespace_name format a30 heading 'TS Name'column sbytes format 9,999,999,999 heading 'Total MBytes'column fbytes format 9,999,999,999 heading 'Free MBytes'column file_name format a30 heading 'File Name'column kount format 999 heading 'Ext'compute sum of fbytes on tablespace_namecompute sum of sbytes on tablespace_namecompute sum of sbytes on reportcompute sum of fbytes on reportbreak on tablespace_name skip 2select a.tablespace_name, a.file_name, round(a.bytes/1024/1024,0) sbytes, round(sum(b.bytes/1024/1024),0) fbytes, count(*) kount, autoextensiblefrom dba_data_files a, dba_free_space bwhere a.file_id = b.file_idand a.tablespace_name in (select z.tablespace_name from dba_tablespaces z where retention like '%GUARANTEE')group by a.tablespace_name, a.file_name, a.bytes, autoextensibleorder by a.tablespace_name/set linesize 160REMREM If you can significantly reduce physical reads by adding incrementalREM data buffers...do it. To determine whether adding data buffers willREM help, set db_block_lru_statistics = TRUE andREM db_block_lru_extended_statistics = TRUE in the init.ora parameters.REM You can determine how many extra hits you would get from memory asREM opposed to physical I/O from disk. **NOTE: Turning these on willREM impact performance. One shift of statistics gathering should be enoughREM to get the required information.REMREMREM -----------------------------------------------------------------REMset lines 160col tablespace_name format a30 heading "Tablespace"col tb format a15 heading "TB Status"col df format a10 heading "DF Status"col extent_management format a15 heading "Extent|Management"col allocation_type format a8 heading "Type"col segment_space_management format a7 heading "Auto|Segment"col retention format a11 heading "Retention|Level"col autoextensible format a5 heading "Auto?"col mx format 999,999,999 heading "Max Allowed"select t.tablespace_name, t.status tb, d.status df,extent_management, allocation_type, segment_space_management, retention,autoextensible, (maxbytes/1024/1024) mxfrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_nameand retention like '%GUARANTEE'/col status format a20 head "Status"col cnt format 999,999,999 head "How Many?"select status, count(*) cntfrom dba_rollback_segsgroup by status/set termout onset trimout offset trimspool offset lines 120set pages 999set termout offset trimout onset trimspool onalter session set nls_date_format='dd-Mon-yyyy hh24:mi';promptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;promptprompt ############## DATAFILES ##############promptcol retention head "Retention"col tablespace_name format a30 head "TBSP Name"col file_id format 999 head "File #"col a format 999,999,999,999,999 head "Bytes Alloc (MB)"col b format 999,999,999,999,999 head "Max Bytes Used (MB)"col autoextensible head "Auto|Ext"col extent_management head "Ext Mngmnt"col allocation_type head "Type"col segment_space_management head "SSM"select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensiblefrom dba_data_fileswhere tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )group by file_id, tablespace_name, autoextensibleorder by tablespace_name/set termout onset trimout offset trimspool offttitle offset pages 999set lines 150set verify offset termout offset trimout onset trimspool onREMREM ------------------------------------------------------------------------REMREM -----------------------------------------------------------------REMREMREM REPORTING UNDO EXTENTS INFORMATION:REMREM -----------------------------------------------------------------REMREM Undo Extents breakdown informationREMttitle center "Rollback Segments Breakdown" skip 2col status format a20col cnt format 999,999,999 head "How Many?"select status, count(*) cnt from dba_rollback_segsgroup by status/ttitle center "Undo Extents" skip 2col segment_name format a30 heading "Name"col "ACT BYTES" format 999,999,999,999 head "Active|Extents"col "UNEXP BYTES" format 999,999,999,999 head "Unxpired|Extents"col "EXP BYTES" format 999,999,999,999 head "Expired|Extents"select segment_name, nvl(sum(act),0) "ACT BYTES", nvl(sum(unexp),0) "UNEXP BYTES", nvl(sum(exp),0) "EXP BYTES" from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp from DBA_UNDO_EXTENTS where status='ACTIVE' group by segment_name union select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp from DBA_UNDO_EXTENTS where status='UNEXPIRED' group by segment_name union select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp from DBA_UNDO_EXTENTS where status='EXPIRED' group by segment_name) group by segment_name;ttitle center "Undo Extents Statistics" skip 2col size format 999,999,999,999 heading "Size"col "HOW MANY" format 999,999,999 heading "How Many?"col st heading a12 heading "Status"select distinct status st, count(*) "HOW MANY", sum(bytes) "SIZE"from dba_undo_extentsgroup by status/col segment_name format a30 heading "Name"col TABLESPACE_NAME for a20col BYTES for 999,999,999,999col BLOCKS for 999,999,999col status for a15 heading "Status"col segment_name heading "Segment"col extent_id heading "ID"select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS, STATUSfrom dba_undo_extentsorder by 1,3,4,5/REMREM -----------------------------------------------------------------REMREM Undo Extents Contention breakdownREM Take out column TUNED_UNDORETENTION if customerREM prior to 10.2.xREMREM The time frame can be adjusted with this queryREM By default using around 4 hour window of timeREMREM Ex.REM Using sysdate-.04 looking at the last hourREM Using sysdate-.16 looking at the last 4 hoursREM Using sysdate-.32 looking at the last 8 hoursREM Using sysdate-1 looking at the last 24 hoursREMset linesize 140ttitle center "Undo Extents Error Conditions (Default - Last 4 Hours)" skip 2col UNXPSTEALCNT format 999,999,999 heading "# Unexpired|Stolen"col EXPSTEALCNT format 999,999,999 heading "# Expired|Reused"col SSOLDERRCNT format 999,999,999 heading "ORA-1555|Error"col NOSPACEERRCNT format 999,999,999 heading "Out-Of-space|Error"col MAXQUERYLEN format 999,999,999 heading "Max Query|Length"col TUNED_UNDORETENTION format 999,999,999 heading "Auto-Ajusted|Undo Retention"col hours format 999,999 heading "Tuned|(HRs)"select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION, TUNED_UNDORETENTION/60/60 hoursfrom gv$undostatwhere begin_time between (sysdate-.16) and sysdateorder by inst_id, begin_time/set termout onset trimout offset trimspool offttitle offset pages 999set lines 150set verify offset termout offset trimout onset trimspool onREMREM ------------------------------------------------------------------------col name format a30col gets format 9,999,999col waits format 9,999,999PROMPT ROLLBACK HIT STATISTICS:REMREM GETS - # of gets on the rollback segment headerREM WAITS - # of waits for the rollback segment headerset head on;select name, waits, getsfrom v$rollstat, v$rollnamewhere v$rollstat.usn = v$rollname.usn/col pct head "< 2% ideal"select 'The average of waits/gets is '|| round((sum(waits) / sum(gets)) * 100,2)||'%' PCTFrom v$rollstat/PROMPT REDO CONTENTION STATISTICS:REMREM If the ratio of waits to gets is more than 1% or 2%, considerREM creating more rollback segmentsREMREM Another way to gauge rollback contention is:REMcolumn xn1 format 9999999column xv1 new_value xxv1 noprintselect class, countfrom v$waitstatwhere class in ('system undo header', 'system undo block', 'undo header', 'undo block' )/set head offselect 'Total requests = '||sum(count) xn1, sum(count) xv1from v$waitstat/select 'Contention for system undo header = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%'from v$waitstatwhere class = 'system undo header'/select 'Contention for system undo block = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%'from v$waitstatwhere class = 'system undo block'/select 'Contention for undo header = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%'from v$waitstatwhere class = 'undo header'/select 'Contention for undo block = '|| (round(count/(&xxv1+0.00000000001),4)) * 100||'%'from v$waitstatwhere class = 'undo block'/REMREM NOTE: Not as useful with AUM configuredREMREM If the percentage for an area is more than 1% or 2%, considerREM creating more rollback segments. Note: This value is usually veryREM smallREM and has been rounded to 4 places.REMREM ------------------------------------------------------------------------REMREM The following shows how often user processes had to wait for space inREM the redo log buffer:select name||' = '||valuefrom v$sysstatwhere name = 'redo log space requests'/REMREM This value should be near 0. If this value increments consistently,REM processes have had to wait for space in the redo buffer. If thisREM condition exists over time, increase the size of LOG_BUFFER in theREM init.ora file in increments of 5% until the value nears 0.REM ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.REMREM -----------------------------------------------------------------------col name format a15col gets format 9999999col misses format 9999999col immediate_gets heading 'IMMED GETS' format 9999999col immediate_misses heading 'IMMED MISS' format 9999999col sleeps format 999999PROMPT LATCH CONTENTION:REMREM GETS - # of successful willing-to-wait requests for a latchREM MISSES - # of times an initial willing-to-wait request was unsuccessfulREM IMMEDIATE_GETS - # of successful immediate requests for each latchREM IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latchREM SLEEPS - # of times a process waited and requests a latch after anREM initial willing-to-wait requestREMREM If the latch requested with a willing-to-wait request is notREM available, the requesting process waits a short time and requestsREM again.REM If the latch requested with an immediate request is not available,REM the requesting process does not wait, but continues processingREMset head onselect name, gets, misses, immediate_gets, immediate_misses, sleepsfrom v$latchwhere name in ('redo allocation', 'redo copy')/set head offselect 'Ratio of MISSES to GETS: '|| round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||'%'from v$latchwhere name in ('redo allocation', 'redo copy')/select 'Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: '|| round((sum(immediate_misses)/ (sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||'%'from v$latchwhere name in ('redo allocation', 'redo copy')/set head onREMREM If either ratio exceeds 1%, performance will be affected.REMREM Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number ofREM processes copying information on the redo allocation latch.REMREM Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contentionREM for redo copy latches.REMREM -----------------------------------------------------------------REM This looks at overall i/o activity against individualREM files within a tablespaceREMREM Look for a mismatch across disk drives in terms of I/OREMREM Also, examine the Blocks per Read Ratio for heavily accessedREM TSs - if this value is significantly above 1 then you may haveREM full tablescans occurring (with multi-block I/O)REMREM If activity on the files is unbalanced, move files around to balanceREM the load. Should see an approximately even set of numbers across filesREMset space 1PROMPT REPORTING I/O STATISTICS:column pbr format 99999999 heading 'Physical|Blk Read'column pbw format 999999 heading 'Physical|Blks Wrtn'column pyr format 999999 heading 'Physical|Reads'column readtim format 99999999 heading 'Read|Time'column name format a55 heading 'DataFile Name'column writetim format 99999999 heading 'Write|Time'compute sum of f.phyblkrd, f.phyblkwrt on reportselect fs.name name, f.phyblkrd pbr, f.phyblkwrt pbw, f.readtim, f.writetimfrom v$filestat f, v$datafile fswhere f.file# = fs.file#order by fs.name/REMREM -----------------------------------------------------------------PROMPT GENERATING WAIT STATISTICS:REMREM This will show wait stats for certain kernel instances. ThisREM may show the need for additional rbs, wait lists, db_buffersREMcolumn class heading 'Class Type'column count heading 'Times Waited' format 99,999,999column time heading 'Total Times' format 99,999,999select class, count, timefrom v$waitstatwhere count > 0order by class/REMREM Look at the wait statistics generated above (if any). They willREM tell you where there is contention in the system. There willREM usually be some contention in any system - but if the ratio ofREM waits for a particular operation starts to rise, you may need toREM add additional resource, such as more database buffers, log buffers,REM or rollback segmentsREMREM -----------------------------------------------------------------PROMPT ROLLBACK EXTENT STATISTICS:REMcolumn usn format 999 heading 'Undo #'column extents format 999 heading 'Extents'column rssize format 999,999,999 heading 'Size in|Bytes'column optsize format 999,999,999 heading 'Optimal|Size'column hwmsize format 99,999,999 heading 'High Water|Mark'column shrinks format 9,999 heading 'Num of|Shrinks'column wraps format 9,999 heading 'Num of|Wraps'column extends format 999,999 heading 'Num of|Extends'column aveactive format 999,999,999 heading 'Average size|Active Extents'column rownum noprintselect usn, extents, rssize, optsize, hwmsize, shrinks, wraps, extends, aveactivefrom v$rollstatorder by rownum/set termout onset trimout offset trimspool offset lines 120set pages 999set termout offset trimout onset trimspool onpromptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;promptprompt ############## HISTORICAL DATA ##############promptcol x format 999,999 head "Max Concurrent|Last 7 Days"col y format 999,999 head "Max Concurrent|Since Startup"select max(maxconcurrency) x from v$undostat/select max(maxconcurrency) y from sys.wrh$_undostat/col i format 999,999 head "1555 Errors"col j format 999,999 head "Undo Space Errors"select sum(ssolderrcnt) i from v$undostatwhere end_time > sysdate-2/select sum(nospaceerrcnt) j from v$undostatwhere end_time > sysdate-2/promptprompt ############## CURRENT STATUS OF SEGMENTS ##############prompt ############## SNAPSHOT IN TIME INFO ##############prompt ##############(SHOWS CURRENT UNDO ACTIVITY)##############promptcol segment_name format a30 head "Segment Name"col "ACT BYTES" format 999,999,999,999 head "Active Bytes"col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"select segment_name, nvl(sum(act),0) "ACT BYTES", nvl(sum(unexp),0) "UNEXP BYTES", nvl(sum(exp),0) "EXP BYTES"from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp from dba_undo_extents where status='ACTIVE' group by segment_nameunionselect segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 expfrom dba_undo_extents where status='UNEXPIRED' group by segment_nameunionselect segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) expfrom dba_undo_extents where status='EXPIRED' group by segment_name)group by segment_nameorder by 1/promptprompt ############## UNDO SPACE USAGE ##############promptcol usn format 999,999 head "Segment#"col shrinks format 999,999,999 head "Shrinks"col aveshrink format 999,999,999 head "Avg Shrink Size"select usn, shrinks, aveshrink from v$rollstat/set termout onset trimout offset trimspool offset pages 999set termout offset trimout onset trimspool onpromptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;col inst_id format 999 head "Instance #"col Parameter format a35 wrapcol "Session Value" format a25 wrappedcol "Instance Value" format a25 wrappedpromptprompt ############## PARAMETERS ##############promptselect a.inst_id, a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.inst_id=b.inst_id and b.inst_id=c.inst_id and a.ksppinm in ('_undo_autotune', '_smu_debug_mode', '_highthreshold_undoretention', 'undo_tablespace','undo_retention','undo_management')order by 2;set termout onset trimout offset trimspool offset pages 999set termout offset trimout onset trimspool onpromptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;promptprompt ############## WAITS FOR UNDO (Since Startup) ##############promptcol inst_id head "Instance#"col eq_type format a3 head "Enq"col total_req# format 999,999,999,999,999,999 head "Total Requests"col total_wait# format 999,999 head "Total Waits"col succ_req# format 999,999,999,999,999,999 head "Successes"col failed_req# format 999,999,999999 head "Failures"col cum_wait_time format 999,999,999 head "Cummalitve|Time"select * from v$enqueue_stat where eq_type='US'unionselect * from v$enqueue_stat where eq_type='HW'/promptprompt ############## LOCKS FOR UNDO ##############promptcol addr head "ADDR"col KADDR head "KADDR"col sid head "Session"col osuser format a10 head "OS User"col machine format a15 head "Machine"col program format a17 head "Program"col process format a7 head "Process"col lmode head "Lmode"col request head "Request"col ctime format 9,999 head "Time|(Mins)"col block head "Blocking?"select /*+ RULE */ a.SID, b.process,b.OSUSER, b.MACHINE, b.PROGRAM,addr, kaddr, lmode, request, round(ctime/60/60,0) ctime, blockfromv$lock a,v$session bwherea.sid=b.sidand a.type='US'/promptprompt ############## TUNED RETENTION HISTORY (Last 2 Days) ##############prompt ############## LOWEST AND HIGHEST DATA ##############promptcol low format 999,999,999,999 head "Undo Retention|Lowest Tuned Value"col high format 999,999,999,999 head "Undo Retention|Highest Tuned Value"select end_time, tuned_undoretention from v$undostat where tuned_undoretention = (select min(tuned_undoretention) lowfrom v$undostatwhere end_time > sysdate-2)/select end_time, tuned_undoretention from v$undostat where tuned_undoretention = (select max(tuned_undoretention) highfrom v$undostatwhere end_time > sysdate-2)/promptprompt ############## CURRENT TRANSACTIONS ##############promptcol sql_text format a40 word_wrapped head "SQL Code"select a.start_date, a.start_scn, a.status, c.sql_textfrom v$transaction a, v$session b, v$sqlarea cwhere b.saddr=a.ses_addr and c.address=b.sql_addressand b.sql_hash_value=c.hash_value/select current_scn from v$database/col a format 999,999 head "UnexStolen"col b format 999,999 head "ExStolen"col c format 999,999 head "UnexReuse"col d format 999,999 head "ExReuse"promptprompt ############## WHO'S STEALING WHAT? (Last 2 Days) ##############promptselect unxpstealcnt a, expstealcnt b, unxpblkreucnt c, expblkreucnt dfrom v$undostatwhere (unxpstealcnt > 0 or expstealcnt > 0)and end_time > sysdate-2/set termout onset trimout offset trimspool offset pages 999set termout offset trimout onset trimspool onpromptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;col current_scn head "SCN Now"col start_date head "Trans Started"col start_scn head "SCN for Trans"col ses_addr head "ADDR"promptprompt ############## Historical V$UNDOSTAT (Last 2 Days) ##############promptcol end_time format a18 Head "Date/Time"col maxq format 999,999 head "Query|Maximum|Minutes"col maxquerysqlid head "SqlID"col undotsn format 999,999 head "TBS"col undoblks format 999,999,999 head "Undo|Blocks"col txncount format 999,999,999 head "# of|Trans"col unexpiredblks format 999,999,999 head "# of Unexpired"col expiredblks format 999,999,999 head "# of Expired"col tuned format 999,999 head "Tuned Retention|(Minutes)"select end_time, round(maxquerylen/60,0) maxq, maxquerysqlid,undotsn, undoblks, txncount, unexpiredblks, expiredblks,round(tuned_undoretention/60,0) Tunedfrom dba_hist_undostatwhere end_time > sysdate-2order by 1/promptprompt ############## RECENT MISSES FOR UNDO (Last 2 Days) ##############promptset lines 500select * from v$undostat where maxquerylen > tuned_undoretentionand end_time > sysdate-2order by 2/select * from sys.wrh$_undostat where maxquerylen > tuned_undoretentionand end_time > sysdate-2order by 2/promptprompt ############## AUTO-TUNING TUNE-DOWN DATA ##############prompt ############## ROLLBACK DATA (Since Startup) ##############promptcol name format a60 head "Name"col value format 999,999,999 head "Counters"select name, value from v$sysstatwhere name like '%down retention%' or name like 'une down%'or name like '%undo segment%' or name like '%rollback%'or name like '%undo record%'/promptprompt ############## Long Running Query History ##############promptcol end_time head "Date"col maxquerysqlid head "SQL ID"col runawayquerysqlid format a15 head "Runaway SQL ID"col results format a35 word_wrapped head "Space Issues"col status head "Status"col newret head "Tuned Down|Retention"select end_time, maxquerysqlid, runawayquerysqlid, status, decode(status,1,'Slot Active',4,'Reached Best Retention',5,'Reached Best Retention', 8, 'Runaway Query',9,'Runaway Query-Active',10,'Space Pressure', 11,'Space Pressure Currently', 16, 'Tuned Down (to undo_retention) due to Space Pressure', 17,'Tuned Down (to undo_retention) due to Space Pressure-Active', 18, 'Tuning Down due to Runaway', 19, 'Tuning Down due to Runaway-Active', 28, 'Runaway tuned down to last tune down value', 29, 'Runaway tuned down to last tune down value', 32, 'Max Tuned Down - Not Auto-Tuning', 33, 'Max Tuned Down - Not Auto-Tuning (Active)', 37, 'Max Tuned Down - Not Auto-Tuning (Active)', 38, 'Max Tuned Down - Not Auto-Tuning', 39, 'Max Tuned Down - Not Auto-Tuning (Active)', 40, 'Max Tuned Down - Not Auto-Tuning', 41, 'Max Tuned Down - Not Auto-Tuning (Active)', 42, 'Max Tuned Down - Not Auto-Tuning', 44, 'Max Tuned Down - Not Auto-Tuning', 45, 'Max Tuned Down - Not Auto-Tuning (Active)', 'Other ('||status||')') Results, spcprs_retention NewRetfrom sys.wrh$_undostatwhere status > 1/promptprompt ############## Details on Long Run Queries ##############promptcol sql_fulltext head "SQL Text"Col sql_id heading "SQL ID"select sql_id, sql_fulltext, last_load_time "Last Load",round(elapsed_time/60/60/24,0) "Elapsed Days"from v$sql where sql_id in(select maxquerysqlid from sys.wrh$_undostatwhere status > 1)/set termout onset trimout offset trimspool offset pages 999set termout offset trimout onset trimspool onpromptprompt ############## RUNTIME ##############promptcol rdate head "Run Time"select sysdate rdate from dual;promptprompt ############## IN USE Undo Data ##############promptselect((select (nvl(sum(bytes),0))from dba_undo_extentswhere tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )and status in ('ACTIVE','UNEXPIRED')) *100) /(select sum(bytes)from dba_data_fileswhere tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )) "PCT_INUSE"from dual;select tablespace_name, extent_management, allocation_type,segment_space_management, retentionfrom dba_tablespaces where retention like '%GUARANTEE'/col c format 999,999,999,999 head "Sum of Free"select (nvl(sum(bytes),0)) c from dba_free_spacewhere tablespace_name in(select tablespace_name from dba_tablespaces where retention like '%GUARANTEE')/col d format 999,999,999,999 head "Total Bytes"select sum(bytes) d from dba_data_fileswhere tablespace_name in(select tablespace_name from dba_tablespaces where retention like '%GUARANTEE')/PROMPTPROMPT ############## UNDO SEGMENTS ##############PROMPTcol status head "Status"col z format 999,999 head "Total Extents"break on reportcompute sum on report of zselect status, count(*) z from dba_undo_extentsgroup by status/col z format 999,999 head "Undo Segments"select status, count(*) z from dba_rollback_segsgroup by status/promptprompt ############## CURRENT STATUS OF SEGMENTS ##############prompt ############## SNAPSHOT IN TIME INFO ##############prompt ##############(SHOWS CURRENT UNDO ACTIVITY)##############promptcol segment_name format a30 head "Segment Name"col "ACT BYTES" format 999,999,999,999 head "Active Bytes"col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"select segment_name, nvl(sum(act),0) "ACT BYTES", nvl(sum(unexp),0) "UNEXP BYTES", nvl(sum(exp),0) "EXP BYTES"from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp from dba_undo_extents where status='ACTIVE' group by segment_nameunionselect segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 expfrom dba_undo_extents where status='UNEXPIRED' group by segment_nameunionselect segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) expfrom dba_undo_extents where status='EXPIRED' group by segment_name)group by segment_nameorder by 1/promptprompt ############## UNDO SPACE USAGE ##############promptcol usn format 999,999 head "Segment#"col shrinks format 999,999,999 head "Shrinks"col aveshrink format 999,999,999 head "Avg Shrink Size"select usn, shrinks, aveshrink from v$rollstat/set termout onset trimout offset trimspool offspool off