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
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 &
LikeLike