Tuesday, January 15, 2013

Moving Undo/Temporary Tablespace

To Move these tablespace Easily you can create them By Following the below Steps :

sqlplus '/as sysdba'
 
SQL>CREATE TEMPORARY TABLESPACE 
TEMPFILE '/new_location/.dbf' 
SIZE 64M REUSE AUTOEXTEND ON NEXT 28 MAXSIZE unlimited;
 
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 
 
Drop the Old One By Run the Below Command :
 
SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
 
 
For Undo Tablespace Check the below Steps:
 
SQL>create undo tablespace 
 datafile '/new_location/.dbf' size 2000m;
 
Make New Tablespace for Database:
 
SQL> alter system set undo_tablespace= undotbs2 ;
 
Drop Old Tablespace:
 
SQL> drop tablespace undotbs including contents;
 
Thank you 
Osama Mustafa 

2 comments: