UNDOTBS2 is the default undo tbs but still not able to offline UNDOTBS1
978360Dec 6 2012 — edited Dec 7 2012SQL> sho parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name like '%UND%';
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU1$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU10$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU11$ PUBLIC UNDOTBS1 OFFLINE
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU12$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU13$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU14$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU15$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU16$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU17$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU18$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU19$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU20$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU21$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU22$ PUBLIC UNDOTBS2 ONLINE
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU23$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU24$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU25$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU26$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU27$ PUBLIC UNDOTBS2 ONLINE
27 rows selected.
I am getting error when I am making UNDOTBS1 offline.
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracleas/oradata/dvctst/undotbs01.dbf'
I want to make UNDOTBS1 offline and then after some days of observing the alert log, I want to drop UNDOTBS1 tablespace. How do I do that?
Edited by: 975357 on Dec 6, 2012 5:50 PM