Alter Table Move command - ORACLE

oracle11g alter-table tablespace
GKN picture GKN · Dec 17, 2014 · Viewed 24.3k times · Source

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

Marco Baldelli picture Marco Baldelli · Dec 17, 2014

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.

Related questions

Alter table after keyword in Oracle

ALTER TABLE testTable ADD column1 NUMBER(1) DEFAULT 0 NOT NULL AFTER column2; Why can't I use mySql syntax in Oracle too? The above command works in MySql. Can you give me an equivalent that works? Error report: SQL Error: ORA-01735: invalid …

Read More
Changing Oracle table name

I'm trying to change a table name in oracle. I first run this script to declare the table CREATE TABLE CUSTOMER ( C_ID NUMBER(6,0), C_LAST VARCHAR2(25), C_FIRST varchar2(25), C_MI char(1), C_DOB DATE, C_ADDRESS varchar2(100), C_…

Read More
Rename Oracle table

What is the difference between ALTER TABLE RENAME statement and RENAME TABLE statement. ie, between Alter table old_table_name rename to new_table_name and rename table old_table_name to new_table_name.

Read More