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

One thought on “Moving Schema to another Tablespace using "move" command.

  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 &

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.