Question: How
do I reorganize a partitioned table? I want to
re-claim the space. Can I reorganize a single
partition?Answer:
First, it is not always a good idea to reorganize a
partitioned table because the partitioning has made a
conscious decision to "spread out" the data. The
idea behind partitioning is to sacrifice efficient disk
utilization (disk is cheap) in return for a more efficient
data organization where like-minded data are grouped
together. See my notes on
Oracle partitioning tables.
Hence, I rarely recommend reorganizing a partitioned table.
The dbms_redefinition utility can be used for
converting a non-partitioned table to a partitioned table
and vice versa, and it can also be used to reorganize a
partitioned table.
See my notes on
dbms_redefinition which use CTAS (create table as
select) to copy all of the rows of the table into a new
table definition, residing a a tablespace with different
partitioning defined.
Another approach: alter table move partition
If you intend to change the partitioning of a table, you
can use the use alter table ... move partition
tablespace xxx syntax, and then
rename the tablespace.
Note that his
ONLY WORKS when the tablespace matches the partitions
one-for-one:
alter table mytab
move partition "2013-12-31" tablespace
"new-2013-12-31";
rename tablespace
"2013-12-31" to "old-2013-12-31";
rename tablespace "new-2013-12-31" to
"2013-12-31";
drop tablespace "old-2013-12-31";
You can also use the NOLOGGING clause wiht ehe alter table
xxx move partition::
alter table
mytab move partition part1 tablespace ts_new NOLOGGING;
Beware of the error "ORA-14257: cannot move partition
other than a Range or Hash partition".
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
