SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';
System altered.
On another session which connect as scott user
SQL> insert into test select * from all_objects;
47327 rows created.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
On another session which connect as scott user
SQL> commit;
Commit complete.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
11 rows selected.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.
You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
Hope this helps
Taj
4 comments:
thanks
this was simple and clear
jga
erm the last statement got error, how to rectify that one
Hi
i got my undo datafile corrupt so in that cas at mount stage.I have drop the datafile with
alter database datafile '/DB/testdb/undotbs01.dbf' offline drop. then open the database now exec
SQL> drop tablespace undotbs1 including contents ;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
could u help me why this error is been flagged
thnx
Hi,
do the following
1. create new undo tbs
2. make default tbs
3. check new created undo tbs set as default
4. delete the corrupted undo tbs
note: pls post the output of select * from v$rollstat;
thanks
Post a Comment