How to Move Tables and Indexes Another Tablespace in Oracle

Hi,

Sometimes you need moving tables and indexes to the new tablespace in Oracle.

 

Especially you want to drop any tablespace so you need to discharge or move all tables and indexes to the new tablespace.

 

You can move lots of tables to the new tablespace with using generate move scripts.

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'NEW_TBS_NAME;' from dba_tables where tablespace_name='PMDB_DAT1';

 

Above SQL script generated following move scripts.

ALTER TABLE ADMUSER.USEROBS move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UMEASURE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UACCESS move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.UDFTYPE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.ACTVTYPE move tablespace NEW_TBS_NAME;
ALTER TABLE ADMUSER.ACTVCODE move tablespace NEW_TBS_NAME;

 

If you execute above scripts , tables will be moved to new tablespace.

 

If your tables are very big, you can use parallel option and move tables parallel like following.

select 'ALTER TABLE '||owner||'.'||table_name||' move tablespace '||'new_tablespace_name parallel 8;' from dba_tables where tablespace_name='DATA';

 

Use above sql query result and execute them to move tables from one tablespace to new tablespace.

 

You can move lots of indexes to the new tablespace with using generate rebuild scripts.

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||'new_tablespace online parallel 8;' 
from dba_indexes where tablespace_name='PMDB_NDX1';

 

Above SQL script generates following rebuild scripts.

ALTER INDEX ADMUSER.PK_ACCOUNT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_PARENT_ACCT REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_DSI REBUILD TABLESPACE new_tablespace online parallel 8;
ALTER INDEX ADMUSER.NDX_ACCOUNT_UPD REBUILD TABLESPACE new_tablespace online parallel 8;

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.