Undo issue in Oracle 11g
792200May 17 2011 — edited May 17 2011Hi All,
Whenver we run the below query
SQL> SELECT COUNT(LISTING_KEY) FROM DIRECTORY_LISTING AS OF timestamp TO_TIMESTAMP('16-MAY-11 02:11:51','DD-MON-YY HH24:MI:SS');
SELECT COUNT(LISTING_KEY) FROM DIRECTORY_LISTING AS OF timestamp TO_TIMESTAMP('16-MAY-11 02:11:51','DD-MON-YY HH24:MI:SS')
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 552 with name
"_SYSSMU552_1304514044$" too small
We are getting the error. So far we recreated the undo. But now we are facing the issue.
Is there any way to flush the UNDO and drop the not mapped UNDO TBS
SQL> SELECT tablespace_name, status, COUNT(*) AS HOW_MANY
FROM dba_undo_extents
GROUP BY tablespace_name, status;
2 3
TABLESPACE_NAME STATUS HOW_MANY
------------------------------ --------- ----------
UNDOTBS2 UNEXPIRED 17
UNDOTBS EXPIRED 10
UNDOTBS2 ACTIVE 19006
UNDOTBS2 EXPIRED 56
UNDOTBS ACTIVE 766
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1269371249$' found, terminate
dropping tablespace
Can anyone help to solve the issue.
Regards,
Mohanarangan K