Oracle11gUndo tablespace Switch

-- 1. view the undo tablespace currently used by the instance and the undo related parameters SQLshowparameterundoNAMETYPEVALUE using undo_managementstringAUTOundo_retentioninteger86400undo_tablesp

-- 1. view the undo tablespace and undo parameters currently used by the instance SQL show parameter undo NAME TYPE VALUE ------------- ------------------------------ undo_management string AUTO undo_retention integer 86400 undo_tablesp

-- 1. view the undo tablespace and undo parameters currently used by the instance.


SQL> show parameter undo


NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 86400
Undo_tablespace string UNDOTBS2


-- 2. Create an undo tablespace
Create undo tablespace UNDOTBS3 datafile
'+ DATA/hxcx/datafile/undotbs3_01.dbf' size 30G autoextend on next 100 m maxsize unlimited,
'+ DATA/hxcx/datafile/undotbs3_02.dbf' size 30G autoextend on next 100 m maxsize unlimited;


Create undo tablespace UNDOTBS3 datafile
'+ DATA/hxcx/datafile/undotbs1_03.dbf' size 30G autoextend on next 100 m maxsize unlimited,
'+ DATA/hxcx/datafile/undotbs1_04.dbf' size 30G autoextend on next 100 m maxsize unlimited;


-- 3. Switch the current undo tablespace of the instance.


SQL> alter system set undo_tablespace = UNDOTBS3;


System altered.


[54526538] **** active transactions found in undo Tablespace 4-moved to Pending Switch-Out state.
[2, 54526538] active transactions found/affinity dissolution incompletein undo tablespace 4 during switch-out.
Alter system set undo_tablespace = 'undotbs3' SCOPE = BOTH;
Mon Jun 03 09:49:53 2013
[2, 43385080] Undo Tablespace 4 successfully switched out.


-- Alert. log indicates that there are still ongoing tasks in the current undo tablespace during the switchover (so it is best to do this without a transaction)


-- 4. Check whether the undo tablespace switch takes effect
SQL> show parameter undo


NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 86400
Undo_tablespace string UNDOTBS3
SQL>
-- The hybrid rollback segment of the UNDOTBS3 tablespace in the newly switched undo tablespace should be in the online status.
Set linesize 200
Select SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS from dba_rollback_segs where tablespace_name = 'undotbs3 ';


-- The original undo tablespace cannot be deleted immediately due to undo_retention. It can only be deleted after the rollback segments of the original undo tablespace change to offline.
-- You can create an undo tablespace at any time and switch the current undo tablespace of the instance. However, the tablespace can be deleted only when all rollback segments in the original undo tablespace are offline.
-- You can change the status of the original undo tablespace by modifying undo_retention.


Set linesize 200
Select SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS from dba_rollback_segs where tablespace_name = 'undotbs1 'and status = 'offline ';


Select count (*) from dba_rollback_segs where tablespace_name = 'undotbs1 'and status = 'online ';


-- The output value should be 0.


-- 5. After confirming that all the original undo tablespace rollback segments are offline, set the tablespace to offline.


Alter tablespace UNDOTBS1 offline;


SQL> select TABLESPACE_NAME, STATUS, CONTENTS from dba_tablespaces where tablespace_name = 'undotbs2 ';


TABLESPACE_NAME STATUS CONTENTS
------------------------------------------------
UNDOTBS2 ONLINE UNDO


-- 6. Delete the original undo tablespace extremely data file


Drop tablespace UNDOTBS1 including contents and datafiles;

Alter tablespace rename UNDOTBS3 to UNDOTBS1;

Alter system set undo_tablespace = UNDOTBS2;

SQL & gt; alter system set undo_retention = 900;

System altered.

Author: xiangsir

9063573

QQ: 444367417

MSN: xiangsir@hotmail.com