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

ALTER TABLE TABLE1 MOVE NOLOGGING PARALLEL;
ALTER TABLE TABLE1 MOVE TABLESPACE TS1 NOLOGGING PARALLEL;
ALTER TABLE TABLE1 MOVE TABLESPACE TS2 NOLOGGING PARALLEL;

Thanks in advance..

Marco Baldelli · Accepted Answer · 2014-12-19T10:20:41.710

8

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.

edited Dec 19 '14 at 10:20

answered Dec 17 '14 at 13:03

Marco Baldelli

3,638
1
22
29

Marco.. First one is just move .. Second is move to the same table-space which the table exists.. Third is to move to other table-space.. What is the difference between 1 and 2? Am moved to a new project.. The first one is used in it.. It greatly reduces the space of the table-space after execution.. But want to know how it is different from 2? What is the advantage? – GKN Dec 18 '14 at 14:38
@GKN I have expanded my answer – Marco Baldelli Dec 19 '14 at 10:21
Thanks Marco.. If you have any link on this ..please share.. It will help me very much – GKN Dec 19 '14 at 13:41

score 0 · Answer 2 · edited Jul 07 '18 at 15:35

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

Alter Table Move command - ORACLE

2 Answers2