How to Move Tables Another Tablespace in Oracle

Hi,

Sometimes you need moving tables to new tablespace in Oracle.

 

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

 

You can move any table to new tablespace in Oracle with following command.

ALTER TABLE MEHMET.SALIH MOVE TABLESPACE NEW_TABLESPACE_NAME;

 

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.

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.