All,
In my database the most of the undo blocks are ACTIVE.
show parameters undo
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 36000
undo_tablespace string UNDOTBS1
*we 've set the retention to 10hrs to enable flashback query..
select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';
SUM(BYTES)/1024/1024/1024
-------------------------
16.1132813
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 , COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------------------------- -------------------- ----------
ACTIVE 15417.625 14620
UNEXPIRED 136 1096
EXPIRED 55.1875 823
select sid , serial#, osuser , USED_UBLK *8192/1024/1024 UNDO_USED_IN_MB ,
logon_time ,sql_id from v$transaction a , v$session b
where a.addr = b.taddr
and a.USED_UBLK > 0
order by 6; 2 3 4 5
SID SERIAL# OSUSER UNDO_USED_IN_MB LOGON_TIME SQL_ID
---------- ---------- ------- --------------- --------------- ---------------------------------------
10 14535 oracle .0078125 20-JAN-13 27977dzwm35zq
888 21467 oracle 7.984375 20-JAN-13 71pjfn7yf83uq
395 12907 oracle .0078125 21-JAN-13 8rg2kjzsxw97v
Why is the ACTIVE so high when there sum(UNDO_USED_IN_MB) is very less ?
*The ACTIVE keeps growing slowly. I already hit ORA-30036, before i resized it.
TIA,
Jon
Edited by: J on 20-Jan-2013 22:24