Shulou(Shulou.com)06/01 Report--

Alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

Col status format a15

Col description format a40 word_wrap

Set serverout on size 999999 lines 150 verify off pages 50 echo off trimspool on

Break on report

Compute Sum LABEL SUM of undosize_MB on report

Compute Sum LABEL SUM of block_count on report

Col undo_alloc new_value undo_alloc noprint

Col undo_pct format 9999

Spool undo_info.log

SELECT SUM (bytes) / 1024 Universe 1024 undo_alloc

FROM DBA_data_files

WHERE tablespace_name =

(SELECT UPPER (value)

FROM v$parameter

WHERE name = 'undo_tablespace')

Ttitle left'* REPORT 1-Undo Block Status and Free Space Analysis * 'skip 2

Col Description format a40 word_wrap

Col undo_pct format A5 head "UNDO | PCTGE"

SELECT status

DECODE (status,'ACTIVE','UNDO BEING USED','EXPIRED','UNDO AVAILABLE FOR USE','UNEXPIRED','UNDO BEING RETAINED TO SUPPORT UNDO RETENTION. CAN BECOME EXPIRED IF SPACE BECOMES TIGHT') Description

Count (*) block_count

Round (sum (bytes) / 1024ax 1024) undosize_MB

Round ((sum (bytes) / 1024lap 1024) / & undo_alloc*100) | |'% 'undo_pct

FROM dba_undo_extents

GROUP BY status

UNION

SELECT 'FREE SPACE'

'UNDO FREE SPACE AVAILABLE FOR USE'

COUNT (*)

ROUND (SUM (bytes) / 1024ax 1024)

Round ((sum (bytes) / 1024lap 1024) / & undo_alloc*100) | |'%'

FROM dba_free_space

WHERE tablespace_name =

(SELECT value

FROM v$parameter

WHERE name = 'undo_tablespace')

GROUP BY 1

/

Ttitle off

Ttitle left'* REPORT 2-Undo Health Check * 'skip 2

DECLARE

TablespaceName varchar2 (30)

TablespaceSize number

AutoExtend boolean

AutoExtendtf char (5)

UndoRetention number

RetentionGuarantee boolean

RetentionGuaranteetf char (5)

AutotuneEnabled boolean

AutotuneEnabledtf char (5)

LongestQuery number

LongestQueryFormatted varchar2 (30)

RequiredRetention number

RequiredRetentionFormatted varchar2 (20)

BestPossibleRetention number

BestPossibleRetentionFormatted varchar2 (20)

RequireUndoSize number

--

Problem varchar2 (100)

Recommendation varchar2 (100)

Rationale varchar2 (100)

Retention number

Utbsize number

Nbr number

UndoAdvisor varchar2 (100)

InstanceNumber number

Ret boolean

Rettf char (5)

UndoRetentionFormatted varchar2 (50)

Recommended_undo_size number

--

--

BEGIN

Ret: = sys.dbms_undo_adv.undo_info (tableSpaceName, tableSpaceSize, autoExtend, undoRetention, retentionGuarantee)

If ret

Then rettf: = 'TRUE'

Else rettf: = 'FALSE'

End if

If autoextend

Then autoextendtf: = 'TRUE'

Else autoextendtf: = 'FALSE'

End if

If retentionguarantee

Then retentionguaranteetf: = 'TRUE'

Else retentionguaranteetf: = 'FALSE'

End if

SELECT to_char (trunc (max (undoRetention) / (60060)) | | 'hrs')

| | trunc (to_char ((max (undoRetention)-(3600 * trunc (max (undoRetention) / 3600)) / 60)) | | 'mins' |

INTO undoRetentionFormatted

FROM Dual

Longestquery: = dbms_undo_adv.longest_query (sysdate-1,sysdate)

SELECT to_char (trunc (max (longestQuery) / (60060)) | | 'hrs')

| | trunc (to_char ((max (longestQuery)-(3600 * trunc (max (longestQuery) / 3600)) / 60)) | | 'mins' |

INTO longestQueryFormatted

FROM Dual

-- dbms_output.put_line ('')

Dbms_output.put_line ('- -')

-- dbms_output.put_line ('* UNDO Health *')

-- dbms_output.put_line

Dbms_output.put_line ('* REPORT 2-Undo Health Check *')

Dbms_output.put_line ('')

Dbms_output.put_line ('')

Nbr: = dbms_undo_adv.undo_health (problem, recommendation, rationale, retention, utbsize)

SELECT DECODE (utbsize,0,tableSpaceSize,utbsize)

INTO Recommended_undo_size

FROM dual

Dbms_output.put_line (RPAD ('Problem',35,CHR (0)) | |':'| | problem)

Dbms_output.put_line (RPAD ('Recommendation',35,CHR (0)) | |':'| | recommendation)

Dbms_output.put_line (RPAD ('Rationale',35,CHR (0)) | |':'| | rationale)

Dbms_output.put_line (RPAD ('undo_retention (secs)', 35 undo_retention chr (0)) | |':'| | undoRetention)

Dbms_output.put_line (RPAD ('undo_retention (hrs/mins)', 35 undo_retention chr (0)) | |':'| | undoRetentionFormatted)

Dbms_output.put_line (RPAD ('Guaranteed Retention',35,CHR (0)) | |':'| | retentionGuaranteetf)

Dbms_output.put_line (RPAD ('Longest Run Query (secs)', 35 Longest Run Query chr (0)) | |':'| | longestQuery)

Dbms_output.put_line (RPAD ('Longest Run Query (hrs/mins)', 35 Longest Run Query chr (0)) | |':'| | longestQueryFormatted)

Dbms_output.put_line (RPAD ('Recommended Undo Tramp S Size (MB)', 35) chr (0) | |':'| | Recommended_undo_size)

-- dbms_output.put_line (RPAD ('Recommended Undo Thip S Size (MB)', 35 Recommended Undo chr (0)) | |':'| | utbsize)

Dbms_output.put_line (RPAD ('Current Undo Tramp S Size (MB)', 35) chr (0) | |':'| | tableSpaceSize)

END

/

Col SSOLDERRCNT format 999999999 HEAD "SNAPSHOT | TOO OLD | ERROR | COUNT"

Col NOSPACEERRCNT format 9999999 HEAD "NOSPACE | ERROR | COUNT"

Ttitle off

Ttitle left'* REPORT 3-Current Undo Stats * 'skip 2

SELECT BEGIN_TIME

END_TIME

UNDOBLKS

MAXQUERYLEN

MAXQUERYID

SSOLDERRCNT

NOSPACEERRCNT

TUNED_UNDORETENTION

FROM v$undostat

WHERE BEGIN_TIME > sysdate-.090

ORDER BY 1

Ttitle off

Ttitle left'* REPORT 4-Undo Datafiles * 'skip 2

Col filename format a65

Col TSPACENAME format a12

Col CURRENT_SIZE_MB format 999999 head 'CURRENT | SIZE (MB)'

Col AUTOEXTEND_UP_TO_SIZE format 9999999 head 'AUTOEXTEND UP | TO SIZE (MB)'

SELECT d.file_name "FILENAME"

D.bytes/1024/1024 CURRENT_SIZE_MB

D.maxbytes/1024/1024 AUTOEXTEND_UP_TO_SIZE

T.tablespace_name "TSPACENAME"

CASE

WHEN d.autoextensiblekeeper YES 'AND d.bytes > = d.maxbytes THEN' WORKROUND IN PLACE'

WHEN d. Autoextensible.autoextensibleautomatically YES' AND d.bytes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.