Moving Schema to another Tablespace

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

Tag: Moving Schema to another Tablespace