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

ITPub博客

Switch Undo Tablespace

最新文章