 |
|
Oracle
Redefine a Partition Online
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
The package DBMS_REDEFINITION is known to be used as
a tool to change the definition of the objects while keeping them
accessible (online). In previous versions, if you use it to move a
partitioned table to another tablespace, it will move the entire
table. This results in massive amount of undo and redo generation.
In Oracle 10g, you can use the package to move a
single partition (instead of the entire table). The following code
illustrates the steps you follow.
1. Confirm that you can redefine the table online.
Having no output after running the following code means the
online redefinition is possible:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
UNAME => 'HR',
TNAME => 'customers',
OPTIONS_FLAG =>
DBMS_REDEFINITION.CONS_USE_ROWID,
PART_NAME => 'p1');
END;
2. Create a temporary (interim) table to hold the
data for that partition:
CREATE TABLE
hr.customers_int
TABLESPACE custdata
AS
SELECT * FROM hr.customers
WHERE 1=2;
Note: If the table customers had some local
indexes, you should create those indexes (as non-partitioned, of
course) on the table customers_int.
3. Start the redefinition process:
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'HR',
ORIG_TABLE => 'customers',
INT_TABLE => 'customers_int',
PART_NAME => 'p1' ); -- partition to move
END;
4. If there were DML operations against the table
during the move process, you should synchronize the interim
table with the original table:
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE (
UNAME => 'HR',
ORIG_TABLE => 'customers',
INT_TABLE => 'customers_int',
COL_MAPPING => NULL,
OPTIONS_FLAG =>
DBMS_REDEFINITION.CONS_USE_ROWID,
PART_NAME => 'p1' );
END;
5. Finish the redefinition process:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE (
UNAME => 'HR',
ORIG_TABLE => 'customers',
INT_TABLE => 'customers_int',
PART_NAME => 'p1');
END;
To confirm the partition P1 was moved to the new
tablespace:
SELECT
PARTITION_NAME, TABLESPACE_NAME,
NUM_ROWS
FROM USER_TAB_PARTITIONS
WHERE PARTITION_NAME='P1'
Note: If there is any global index on the table,
they will be marked as UNUSABLE and must be rebuilt.
Note: You cannot change the structure of the
table during the definition process.
Note: statistics of object moved with this tool
are automatically generated by end of the process.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
