Oracle 11g: Re-organizing Partitioned and Subpartitioned tables using MOVE command

Recently, I happen to delete 20% of 50 million rows from a table. The queries were performing bad. As you guessed it is the because of the fragmentation. We can re-org the table in different ways in Oracle. Here I describe about how to reorg using the simple ALTER TABLE ... MOVE command.

1) In the case of non-partitioned whole table:

Alter table VAICHE.FULL_TABL move;

2) When the table is partitioned table:

Alter table VAICHE.PART_TABL move partition ;

You can get partition name from all_tab_partitions.

3) When the table is sub partitioned:

Alter table VAICHE.SUBPART_TBL move subpartition ;

You can get names of subpartition from all_tab_subpartitions.

When a table is subpartitioned, you cannot do (1) and (2). You will get error like the below:

alter table VAICHE.PART_TABL move partition xyz;;
ERROR at line 1:
ORA-14257: cannot move partition other than a Range, List, System, or Hash
partition

For a subpartition table reorg, you have to move only the subpartitions. Other steps that need to be performed with reorg are
1) Rebuild Indexes
2) Gather Statistics.