ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Switch Undo Tablespace

Switch Undo Tablespace

原创 Linux操作系统 作者:fjmingyang 时间:2019-04-29 09:54:05 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/1698901/viewspace-613081/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Undo Tablespace
请登录后发表评论 登录
全部评论

注册时间:2006-04-03

  • 博文量
    66
  • 访问量
    48613