Search This Blog

Sunday, January 7, 2007

Switch Undo Tablespace


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:

Anonymous said...

thanks
this was simple and clear
jga

vaginal tightening cream said...

erm the last statement got error, how to rectify that one

Anonymous said...

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

Mohammed Taj said...

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