Database Reference
In-Depth Information
BIG1
and
BIG2
are both 1200 MB in size and each have about 200M of free space. We'll try to rebuild the first table,
BIG_TABLE1
:
EODA@ORA12CR1> alter table big_table1 move;
alter table big_table1 move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace BIG1
This fails—we need sufficient free space in tablespace
BIG1
to hold an entire copy of
BIG_TABLE1
at the same time
as the old copy is there—in short, we need about two times the storage for a short period (maybe more, maybe less—it
depends on the resulting size of the rebuilt table). We now attempt the same operation on
BIG_TABLE2
:
EODA@ORA12CR1> alter table big_table2 move;
alter table big_table2 move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
This is Oracle telling us we can't do the
MOVE
operation on the
table
; we must perform the operation on each
partition
of the table instead. We can move (hence rebuild and reorganize) each partition one by one:
EODA@ORA12CR1> alter table big_table2 move partition part_1;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_2;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_3;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_4;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_5;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_6;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_7;
Table altered.
EODA@ORA12CR1> alter table big_table2 move partition part_8;
Table altered.
Each individual move only needs sufficient free space to hold a copy of one-eighth of the data! Therefore, these
commands succeed given the same amount of free space as we had before. We need significantly less temporary
resources and, further, if the system fails (e.g., due to a power outage) after we move
PART_4
but before
PART_5
finished
moving, we won't lose all of the work performed: The first four partitions would still be moved when the system
recovers, and we may resume processing at partition
PART_5
.
Some may look at that and say, “Wow, eight statements—that is a lot of typing,” and it's true that this sort of thing
would be unreasonable if you had hundreds of partitions (or more). Fortunately, it is very easy to script a solution, and
the previous would become simply:
EODA@ORA12CR1> begin
2 for x in ( select partition_name
3 from user_tab_partitions
4 where table_name = 'BIG_TABLE2' )
