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

1 comment:

  1. I tried many scripts but didn't worked for all objects. You can't move clustered objects from one tablespace to another, for that you will have to use expdp. So I will suggest expdp is the best option to move all objects to different tablesapce.

    Below is the command.

    nohup expdp \"/ as sysdba\" DIRECTORY=test_dir DUMPFILE=users.dmp LOGFILE=users.log TABLESPACES=USERS &