2018年1月14日 星期日

switch undo tablespace


swtich undo tablespace
Step 1 : Create Tablespace   :  Create undo tablespace undotbs2   
SQL> create undo tablespace UNDOTBS2 datafile  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'  size 100M;

Step 2 : Edit the parameter file
SQL> alter system set undo_tablespace=UNDOTBS2 ;
SQL> alter system set undo_management=MANUAL scope=spfile;

Step 3: Check the all segment of old undo tablespace to be offline
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
If any one the above segment is online then change it status to offline by using below command .
SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Step  5 : Change undo management to auto and restart the database
SQL> alter system set undo_management=auto scope=spfile;
SQL> shut immediate;
SQL> startup
SQL> show parameter undo_tablespace

沒有留言:

張貼留言