[oracle] How to move table from one tablespace to another in oracle 11g

I run oracle 11g and need to move table (tbl1) from one tablespace (tblspc1) to another (tblspc2). What is the easiest way to do that?

I tried many scripts but they didn't work 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 a different tablespace.

Below is the command:

nohup expdp \"/ as sysdba\" DIRECTORY=test_dir DUMPFILE=users.dmp LOGFILE=users.log TABLESPACES=USERS &

You can check this link for details.

Use sql from sql:

spool output of this to a file:

select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

spoolfile will have something like this:

alter index OWNER.PK_INDEX rebuild tablespace CORRECT_TS_NAME;

Try this to move your table (tbl1) to tablespace (tblspc2).

alter table tb11 move tablespace tblspc2;

Moving tables:

First run:

SELECT 'ALTER TABLE <schema_name>.' || OBJECT_NAME ||' MOVE TABLESPACE '||' <tablespace_name>; '
FROM ALL_OBJECTS
WHERE OWNER = '<schema_name>'
AND OBJECT_TYPE = 'TABLE' <> '<TABLESPACE_NAME>';

-- Or suggested in the comments (did not test it myself)

SELECT 'ALTER TABLE <SCHEMA>.' || TABLE_NAME ||' MOVE TABLESPACE '||' TABLESPACE_NAME>; ' 
FROM dba_tables 
WHERE OWNER = '<SCHEMA>' 
AND TABLESPACE_NAME <> '<TABLESPACE_NAME>

Where <schema_name> is the name of the user. And <tablespace_name> is the destination tablespace.

As a result you get lines like:

ALTER TABLE SCOT.PARTS MOVE TABLESPACE USERS;

Paste the results in a script or in a oracle sql developer like application and run it.

Moving indexes:

First run:

SELECT 'ALTER INDEX <schema_name>.'||INDEX_NAME||' REBUILD TABLESPACE <tablespace_name>;' 
FROM ALL_INDEXES
WHERE OWNER = '<schema_name>'
AND TABLESPACE_NAME NOT LIKE '<tablespace_name>';

The last line in this code could save you a lot of time because it filters out the indexes which are already in the correct tablespace.

As a result you should get something like:

ALTER INDEX SCOT.PARTS_NO_PK REBUILD TABLESPACE USERS;

Paste the results in a script or in a oracle sql developer like application and run it.

Last but not least, moving LOBs:

First run:

SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';

This moves the LOB objects to the other tablespace.

As a result you should get something like:

ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);

Paste the results in a script or in a oracle sql developer like application and run it.

O and there is one more thing:

For some reason I wasn't able to move 'DOMAIN' type indexes. As a work around I dropped the index. changed the default tablespace of the user into de desired tablespace. and then recreate the index again. There is propably a better way but it worked for me.

Try this:-

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>

Very nice suggestion from IVAN in comments so thought to add in my answer

Note: this will invalidate all table's indexes. So this command is usually followed by

alter index <owner>."<index_name>" rebuild;