Alter Table Move command - ORACLE

Question

Consider i have TABLE1 in Tablespace TS1. I have another Tablespace TS2.

What is the difference between the below three

1. ALTER TABLE TABLE1 MOVE NOLOGGING PARALLEL;

2. ALTER TABLE TABLE1 MOVE TABLESPACE TS1 NOLOGGING PARALLEL;

3. ALTER TABLE TABLE1 MOVE TABLESPACE TS2 NOLOGGING PARALLEL;

Thanks in advance..

Answer 1

According to the Database SQL Language Reference:

The move_table_clause lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

Therefore, with your first statement Oracle will move the table to a new segment on the same tablespace, while in the other two statements Oracle will move the table to a new segment on the specified tablespace (respectively TS1 and TS2).

If TABLE1 is already on tablespace TS1 then the first and second command will perform the same action.

Using MOVE without changing the tablespace will simply reorganize the segment on the original tablespace.

Answer 2

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 &

https://www.orahow.com/2018/06/move-all-objects-to-another-tablespace.html