Tuesday, October 16, 2012

Moving Schema to another Tablespace using "move" command.

Step One: Create New  tablespace

SQL>Create tablespace datafile ‘\osama.dbf’ size 1G;

Step Two : Check User On which Tablespace


SQL> select owner, tablespace_name from dba_tables where owner=''
 
Step three:Create Script 


sqlplus / as sysdba
spool osama.log



SQL> select 'alter table ' || table_name || ' move tablespace mytab ;' from dba_tables where owner='' ;
 
Spool off ;
 
Step Four : Move Index

SQL> select 'Alter index ' || index_name || ' rebuild tablespace mytab ;' from dba_indexes where owner='' ;
 
Step Five : Check Again


SQL> select owner, tablespace_name from dba_tables where owner='';
 
 
Thank you 
Osama mustafa

No comments:

Post a Comment