Undo table space is always 99% full, what can I do to release it? Why it is always full?
I have production database of 96Gig, which have 8Gig of undo table spacewith undo retention 3 Hrs. Undo table space seems to be full all the time.
According to below thread, I ran the script posting the result. I am not sure, what the result of the script trying to tell me and how it will help by problem.
520963
set linesize 120
set pagesize 60
alter session set nls_date_format = "dd-Mon-yyyy hh24:mi:ss";
COL TXNCOUNT FOR 99,999,999 HEAD 'Txn. Cnt.'
COL MAXQUERYLEN FOR 99,999,999 HEAD 'Max|Query|Sec'
COL MAXCONCURRENCY FOR 9,999 HEAD 'Max|Concr|Txn'
COL bks_per_sec FOR 99,999,999 HEAD 'Blks per|Second'
COL kb_per_second FOR 99,999,999 HEAD 'KB per|Second'
COL undo_mb_required FOR 999,999 HEAD 'MB undo|Needed'
COL ssolderrcnt FOR 9,999 HEAD 'ORA-01555|Count'
COL nospaceerrcnt FOR 9,999 HEAD 'No Space|Count'
break on report
compute max of txncount -
maxquerylen -
maxconcurrency -
bks_per_sec -
kb_per_second -
undo_mb_required on report
compute sum of -
ssolderrcnt -
nospaceerrcnt on report
SELECT begin_time,
txncount-lag(txncount) over (order by end_time) as txncount,
maxquerylen,
MAXCONCURRENCY,
ROUND(UNDOBLKS/((END_TIME - BEGIN_TIME)*86400),4) as BKS_PER_SEC,
ROUND((UNDOBLKS/((END_TIME - BEGIN_TIME)*86400)),4) * T.BLOCK_SIZE/1024 as KB_PER_SECOND,
ROUND(((UNDOBLKS/((END_TIME - BEGIN_TIME)*86400)) * T.BLOCK_SIZE/1024) * TO_NUMBER(P2.value)/1024,4) as UNDO_MB_REQUIRED,
ROUND(SSOLDERRCNT,4),
round (nospaceerrcnt,4)
FROM v$undostat s,
dba_tablespaces t,
v$parameter p,
v$parameter p2
WHERE t.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
and P2.name = 'undo_retention'
and TO_CHAR(begin_time,'DD-MM-YYYY') = '13-09-2011'
ORDER BY begin_time;
show parameter undo
Results
line 1: SQLPLUS Command Skipped: set linesize 120
line 2: SQLPLUS Command Skipped: set pagesize 60
session SET altered.
line 4: SQLPLUS Command Skipped: COL TXNCOUNT FOR 99,999,999 HEAD 'Txn. Cnt.': only "column .. new_value .." supported
line 5: SQLPLUS Command Skipped: COL MAXQUERYLEN FOR 99,999,999 HEAD 'Max|Query|Sec': only "column .. new_value .." supported
line 6: SQLPLUS Command Skipped: COL MAXCONCURRENCY FOR 9,999 HEAD 'Max|Concr|Txn': only "column .. new_value .." supported
line 7: SQLPLUS Command Skipped: COL bks_per_sec FOR 99,999,999 HEAD 'Blks per|Second': only "column .. new_value .." supported
line 8: SQLPLUS Command Skipped: COL kb_per_second FOR 99,999,999 HEAD 'KB per|Second': only "column .. new_value .." supported
line 9: SQLPLUS Command Skipped: COL undo_mb_required FOR 999,999 HEAD 'MB undo|Needed': only "column .. new_value .." supported
line 10: SQLPLUS Command Skipped: COL ssolderrcnt FOR 9,999 HEAD 'ORA-01555|Count': only "column .. new_value .." supported
line 11: SQLPLUS Command Skipped: COL nospaceerrcnt FOR 9,999 HEAD 'No Space|Count': only "column .. new_value .." supported
BEGIN_TIME TXNCOUNT MAXQUERYLEN MAXCONCURRENCY BKS_PER_SEC KB_PER_SECOND UNDO_MB_REQUIRED ROUND(SSOLDERRCNT,4) ROUND(NOSPACEERRCNT,4)
------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
13-Sep-2011 00:00:04 0 3 0.0667 0.5336 5.625 0 0
13-Sep-2011 00:10:04 289 0 1 0.02 0.16 1.6875 0 0
13-Sep-2011 00:20:04 -330 0 0 0 0 0 0 0
13-Sep-2011 00:30:04 157 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 00:40:04 -157 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 00:50:04 175 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 01:00:04 -123 0 3 0.0567 0.4536 4.7813 0 0
13-Sep-2011 01:10:04 233 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 01:20:04 -283 0 0 0 0 0 0 0
13-Sep-2011 01:30:04 154 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 01:40:04 -154 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 01:50:04 171 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 02:00:04 -141 0 3 0.0533 0.4264 4.5 0 0
13-Sep-2011 02:10:04 254 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 02:20:04 -286 0 0 0 0 0 0 0
13-Sep-2011 02:30:04 155 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 02:40:04 -154 0 0 0 0 0 0 0
13-Sep-2011 02:50:04 216 0 3 0.0883 0.7064 7.4531 0 0
13-Sep-2011 03:00:04 -217 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 03:10:04 295 0 1 0.02 0.16 1.6875 0 0
13-Sep-2011 03:20:04 -295 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 03:30:04 153 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 03:40:04 -152 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 03:50:04 175 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 04:00:04 -136 0 3 0.0683 0.5464 5.7656 0 0
13-Sep-2011 04:10:04 248 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 04:20:04 -288 0 0 0 0 0 0 0
13-Sep-2011 04:30:04 158 0 1 0.0083 0.0664 0.7031 0 0
13-Sep-2011 04:40:04 -157 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 04:50:04 173 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 05:00:04 -128 0 3 0.0767 0.6136 6.4688 0 0
13-Sep-2011 05:10:04 234 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 05:20:04 -281 0 0 0 0 0 0 0
13-Sep-2011 05:30:04 150 0 1 0.0083 0.0664 0.7031 0 0
13-Sep-2011 05:40:04 -147 0 0 0 0 0 0 0
13-Sep-2011 05:50:04 216 0 3 0.0783 0.6264 6.6094 0 0
13-Sep-2011 06:00:04 -217 0 0 0 0 0 0 0
13-Sep-2011 06:10:04 279 0 1 0.0183 0.1464 1.5469 0 0
13-Sep-2011 06:20:04 -280 0 0 0 0 0 0 0
13-Sep-2011 06:30:04 156 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 06:40:04 -154 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 06:50:04 173 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 07:00:04 -140 0 3 0.0683 0.5464 5.7656 0 0
13-Sep-2011 07:10:04 243 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 07:20:04 -278 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 07:30:04 161 0 3 0.015 0.12 1.2656 0 0
13-Sep-2011 07:40:04 -159 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 07:50:04 210 0 3 0.08 0.64 6.75 0 0
13-Sep-2011 08:00:04 -212 0 0 0 0 0 0 0
13-Sep-2011 08:10:04 281 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 08:20:04 -282 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 08:30:04 162 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 08:40:04 -161 0 0 0 0 0 0 0
13-Sep-2011 08:50:04 168 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 09:00:04 -118 0 3 0.09 0.72 7.5938 0 0
13-Sep-2011 09:10:04 233 111 1 0.015 0.12 1.2656 0 0
13-Sep-2011 09:20:04 -283 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 09:30:04 160 0 1 0.01 0.08 0.8438 0 0
13-Sep-2011 09:40:04 -158 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 09:50:04 199 0 2 0.06 0.48 5.0625 0 0
13-Sep-2011 10:00:04 -183 0 3 0.0183 0.1464 1.5469 0 0
13-Sep-2011 10:10:04 255 0 1 0.0183 0.1464 1.5469 0 0
13-Sep-2011 10:20:04 -271 0 0 0 0 0 0 0
13-Sep-2011 10:30:04 158 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 10:40:04 7 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 10:50:04 -128 0 3 0.0667 0.5336 5.625 0 0
13-Sep-2011 11:00:04 240 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 11:10:04 -278 0 0 0 0 0 0 0
13-Sep-2011 11:20:04 152 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 11:30:04 -150 0 0 0 0 0 0 0
13-Sep-2011 11:40:04 156 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 11:50:04 -159 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 12:00:04 323 0 3 0.08 0.64 6.75 0 0
13-Sep-2011 12:10:04 -324 0 0 0 0 0 0 0
13-Sep-2011 12:20:04 155 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 12:30:04 -155 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 12:40:04 161 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 12:50:04 -123 0 3 0.0667 0.5336 5.625 0 0
13-Sep-2011 13:00:04 248 0 1 0.015 0.12 1.2656 0 0
13-Sep-2011 13:10:04 -228 0 0 0 0 0 0 0
13-Sep-2011 13:20:04 131 0 1 0.0183 0.1464 1.5469 0 0
13-Sep-2011 13:30:04 -24 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 13:40:04 -163 0 1 0.0017 0.0136 0.1406 0 0
13-Sep-2011 13:50:04 165 0 1 0.0133 0.1064 1.125 0 0
13-Sep-2011 14:00:04 -117 0 3 0.0833 0.6664 7.0312 0 0
13-Sep-2011 14:10:04 228 0 1 0.0167 0.1336 1.4063 0 0
13-Sep-2011 14:20:04 -277 0 0 0 0 0 0 0
13-Sep-2011 14:30:04 168 0 1 0.0117 0.0936 0.9844 0 0
13-Sep-2011 14:40:04 -165 495 1 0.0033 0.0264 0.2813 0 0
13-Sep-2011 14:50:04 -5 799 0 0 0 0 0 0
90 rows selected
NAME TYPE VALUE
-------------------------------------------------------------------------------- -------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDO_TS