--1. 确认当前的UNDO表空间
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS1
--2. 创建新的UNDO表空间
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/oradata/ESUITE/undotbs2.dbf'
size 4096M reuse autoextend on next 10M maxsize unlimited;
--3. 修改默认UNDO表空间的标识
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS2
--3. 删除原来的UNDO表空间
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
--提示ORA-30013错误, 检查是否还有在UNDOTBS1表空间上online状态的回滚段:
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ --------------- ------------------------------ ----------
_SYSSMU13$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU28$ PUBLIC UNDOTBS1 ONLINE
--果然还有, 过会儿alert.log有如下提示, 因为原undo表空间还有活动事务, 还在迁移中:
Wed Jun 24 18:06:02 2009
Undo Tablespace 1 moved to Pending Switch-Out state.
--等到alert.log出现如下提示后, 表示undo表空间迁移完毕:
Wed Jun 24 18:16:02 2009
Undo Tablespace 1 successfully switched out.
--此时再看是否还有在UNDOTBS1表空间上online状态的回滚段:
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
no rows selected
--此时虽然还有offline状态的回滚段, 但可以删除原来的UNDO表空间了:
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1';
no rows selected
--END--
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS1
--2. 创建新的UNDO表空间
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u06/oradata/ESUITE/undotbs2.dbf'
size 4096M reuse autoextend on next 10M maxsize unlimited;
--3. 修改默认UNDO表空间的标识
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86400
undo_tablespace string UNDOTBS2
--3. 删除原来的UNDO表空间
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
--提示ORA-30013错误, 检查是否还有在UNDOTBS1表空间上online状态的回滚段:
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ --------------- ------------------------------ ----------
_SYSSMU13$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU28$ PUBLIC UNDOTBS1 ONLINE
--果然还有, 过会儿alert.log有如下提示, 因为原undo表空间还有活动事务, 还在迁移中:
Wed Jun 24 18:06:02 2009
Undo Tablespace 1 moved to Pending Switch-Out state.
--等到alert.log出现如下提示后, 表示undo表空间迁移完毕:
Wed Jun 24 18:16:02 2009
Undo Tablespace 1 successfully switched out.
--此时再看是否还有在UNDOTBS1表空间上online状态的回滚段:
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
no rows selected
--此时虽然还有offline状态的回滚段, 但可以删除原来的UNDO表空间了:
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs
where tablespace_name='UNDOTBS1';
no rows selected
--END--
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25615456/viewspace-714674/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25615456/viewspace-714674/