General
=============================
Example of partitioning existing table using DBMS_REDEFINITION package.
DBMS_REDEFINITION package was introduced in Oracle 9i.
This would be the easiest was to partition an existing table.
The implementation is using Materialized Views and MV Log tables.
The inetrim table is manually created. This table would be dropped at the end of the process.
Until the transition is completed Oracle is tracking ongoing DML operation with MV Logs.
Oracle DBMS_REDEFINITION Reference.
Orafaq DBMS_REDEFINITION Reference
=============================
General steps to partition existing table with DBMS_REDEFINITION
=============================
A. Check is the redefinition is possible
DBMS_REDEFINITION.can_redef_table
B. Manually create a new partitioned table, same as original table.
This would be a temporary table, that would be eventually dropped.
C. Start the redefinition
DBMS_REDEFINITION.start_redef_table
D. Copy dependent objects
DBMS_REDEFINITION.copy_table_dependents
An alternative to this step is to manually create Constraints and Indexes on the new table.
E. Check for errors.
Query DBA_REDEFINITION_ERRORS
F. Synchronize data between old and new tables
DBMS_REDEFINITION.sync_interim_table
G. Complete the Redefinition Process
DBMS_REDEFINITION.finish_redef_table
At this point the partitioned table has become the "real" table and their names have been switched in the data dictionary.
H. Drop the interim table.
DROP TABLE ... PURGE;
EXEC DBMS_STATS.gather_table_stats
=============================
General example of using DBMS_REDEFINITION to partition existing table.
=============================
A. Check if the redefinition is possible
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE ('MY_USER', 'MONTHLY_CALLS_AGG', DBMS_REDEFINITION.CONS_USE_PK);
END;
/
PL/SQL procedure successfully completed
If the table has no PK, following error is raised:
BEGIN
DBMS_REDEFINITION.can_redef_table ('MANAGER', 'STATEMENTS_AUDIT', DBMS_REDEFINITION.CONS_USE_PK);
END;
/
ORA-12089: cannot online redefine table "MANAGER"."STATEMENTS_AUDIT" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 115
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1136
ORA-06512: at line 3
Note:
The redefinition process can be based on a Primary Key or ROWID.
Two constants are defined for this purpose:
- DBMS_REDEFINITION.CONS_USE_PK (the default) - DBMS_REDEFINITION.CONS_USE_ROWID.
This is required so Oracle will know how to create the mview log: WITH ROWID, or WITH PRIMARY KEY.
B. Manually create a new partitioned table, same as original table.
CREATE TABLE MY_USER.MONTHLY_CALLS_AGG_INTERIM
partition by RANGE(year)
(PARTITION P_1012 VALUES LESS THAN(2013),
PARTITION P_1013 VALUES LESS THAN(2014),
PARTITION P_1014 VALUES LESS THAN(2015),
PARTITION P_1015 VALUES LESS THAN(2016)
)
AS SELECT * FROM MY_USER.MONTHLY_CALLS_AGG
WHERE 1=2;
Table created
C. Start the redefinition
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('MY_USER', 'MONTHLY_CALLS_AGG', 'MONTHLY_CALLS_AGG_INTERIM');
END;
/
PL/SQL procedure successfully completed
D. Copy dependent objects
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents ('MY_USER', 'MONTHLY_CALLS_AGG', 'MONTHLY_CALLS_AGG_INTERIM', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
PL/SQL procedure successfully completed
E. Check for errors.
SELECT object_name, base_table_name, ddl_txt
FROM DBA_REDEFINITION_ERRORS;
F. Synchronize data between old and new tables
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('MY_USER', 'MONTHLY_CALLS_AGG', 'MONTHLY_CALLS_AGG_INTERIM');
END;
/
PL/SQL procedure successfully completed
G. Complete the Redefintion Process
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('MY_USER', 'MONTHLY_CALLS_AGG', 'MONTHLY_CALLS_AGG_INTERIM');
END;
/
PL/SQL procedure successfully completed
H. Drop the interim table.
DROP TABLE MY_USER.MONTHLY_CALLS_AGG_INTERIM CASCADE CONSTRAINTS PURGE;
CASCADE CONSTRAINTS - Would drop all referential integrity constraints that refer to
primary and unique keys in the dropped table.
PURGE - Using PURGE is equivalent to first dropping the table and then purging it from
the recycle bin.
You cannot recover the table if you have dropped it with the PURGE clause.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_USER', tabname => 'MONTHLY_CALLS_AGG');
END;
/
PL/SQL procedure successfully completed
=============================
Now check the table, after it was partitioned.
=============================
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'MONTHLY_CALLS_AGG'
========================
Changing Tablespace
========================
1. Table
Notice that the new table is on user default tablespace, because when creating the new MONTHLY_CALLS_AGG_INTERIM table, tablespace clause was omitted.
It was moved from MONTHLY_CALLS_AGG_TB to DEFAULT_TABLESPACE
2. Index
The Primary Key unique index in not Local, as might be expected.
The Primary Key unique index tablespace was not changed during
the DBMS_REDEFINITION.copy_table_dependents command.
It is still on MY_USER_MONTHLY_AGG_IX tablespace.
========================
Changing Table Tablespace
========================
Lets move the table MONTHLY_CALLS_AGG from DEFAULT_TABLESPACE back to it original tablespace MONTHLY_CALLS_AGG_TB.
Option A.
ALTER TABLE XXX MOVE TABLESPACE
ALTER TABLE MONTHLY_CALLS_AGG MOVE TABLESPACE MONTHLY_CALLS_AGG_TB
ORA-14511: cannot perform operation on a partitioned object
Option B.
ALTER TABLE XXX MOVE PARTITION
Move partition by partition, from tablespace A to tablespace B.
Need to generate scripts, to move tablespace per partition.
set heading off
set linesize 200
SELECT 'ALTER TABLE '||TABLE_OWNER ||'.'||TABLE_NAME||' MOVE PARTITION '||partition_name||' TABLESPACE MONTHLY_AGG_TB NOLOGGING;'
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'MY_USER'
AND table_name = 'MONTHLY_CALLS_AGG';
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1012 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1013 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1014 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1015 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
Now execute the generated commands.
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1012 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1013 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1014 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG MOVE PARTITION P_1015 TABLESPACE MONTHLY_AGG_TB NOLOGGING;
Table altered
Check current situation
SELECT table_owner, table_name, partition_name, tablespace_name, num_rows
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'MY_USER'
AND table_name = 'MONTHLY_CALLS_AGG';
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
----------- ------------------- --------------- -------------------- ----------
DWNG MONTHLY_CALLS_AGG P_1012 DWNG_MONTHLY_AGG_TB 1542881
DWNG MONTHLY_CALLS_AGG P_1013 DWNG_MONTHLY_AGG_TB 782200
DWNG MONTHLY_CALLS_AGG P_1014 DWNG_MONTHLY_AGG_TB 744540
DWNG MONTHLY_CALLS_AGG P_1015 DWNG_MONTHLY_AGG_TB 58696
Looks like first partition need to be split into 2012 and 2011.
========================
Split Existing Partition
========================
First try - Add a new partition with values less than 2012.
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG
ADD PARTITION P_1011 VALUES LESS THAN ( 2012 )
TABLESPACE DWNG_MONTHLY_AGG_TB;
ORA-14074: partition bound must collate higher than that of the last partition
Second try - Split existing partition into two.
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG SPLIT PARTITION P_1012 AT (2012) INTO (PARTITION P_1011, PARTITION P_1012A);
Table altered
ALTER TABLE MY_USER.MONTHLY_CALLS_AGG RENAME PARTITION P_1012A TO P_1012
Table altered
SELECT table_owner, table_name, partition_name, tablespace_name, num_rows
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'MY_USER'
AND table_name = 'MONTHLY_CALLS_AGG';
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
----------- ------------------- --------------- -------------------- ----------
DWNG MONTHLY_CALLS_AGG P_1011 DWNG_MONTHLY_AGG_TB
DWNG MONTHLY_CALLS_AGG P_1012 DWNG_MONTHLY_AGG_TB
DWNG MONTHLY_CALLS_AGG P_1013 DWNG_MONTHLY_AGG_TB 782200
DWNG MONTHLY_CALLS_AGG P_1014 DWNG_MONTHLY_AGG_TB 744540
DWNG MONTHLY_CALLS_AGG P_1015 DWNG_MONTHLY_AGG_TB 58696
Need to gather statistics to get the num_rows value.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_USER', tabname => 'MONTHLY_CALLS_AGG');
END;
ORA-20000: index "MY_USER"."MONTHLY_CALLS_AGG_PK" or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 12639
ORA-06512: at "SYS.DBMS_STATS", line 12658
ORA-06512: at line 3
ALTER INDEX MY_USER.MONTHLY_CALLS_AGG_PK REBUILD PARTITION P_1011
ORA-14075: partition maintenance operations may only be performed on partitioned indices
ALTER INDEX MY_USER.MONTHLY_CALLS_AGG_PK REBUILD GLOBAL INDEX
ORA-14075: partition maintenance operations may only be performed on partitioned indices
From the error, it looks like the index on the Partitioned table, is not partitioned, neither Local or Global.
ALTER INDEX DWNG.MONTHLY_CALLS_AGG_PK REBUILD ONLINE;
Index altered
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_USER', tabname => 'MONTHLY_CALLS_AGG');
END;
PL/SQL procedure successfully completed
SELECT table_owner, table_name, partition_name, tablespace_name, num_rows
FROM DBA_TAB_PARTITIONS
WHERE table_owner = 'MY_USER'
AND table_name = 'MONTHLY_CALLS_AGG';
TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
----------- ------------------- --------------- -------------------- ----------
DWNG MONTHLY_CALLS_AGG P_1011 DWNG_MONTHLY_AGG_TB 756885
DWNG MONTHLY_CALLS_AGG P_1012 DWNG_MONTHLY_AGG_TB 777009
DWNG MONTHLY_CALLS_AGG P_1013 DWNG_MONTHLY_AGG_TB 785961
DWNG MONTHLY_CALLS_AGG P_1014 DWNG_MONTHLY_AGG_TB 754797
DWNG MONTHLY_CALLS_AGG P_1015 DWNG_MONTHLY_AGG_TB 58347
Look at the numbers under num_rows.
A. Now there are values for the P_2011 and P_2012 partitions.
B. The numbers for other partitions slightly differ, comparing to previous values, DBMS_STATS.GATHER_TABLE_STATS was run only few minutes ago, with no DML activity on the table.
For example, for P_2013, 785961 vs 782200
========================
Transferring Index to a partitioned state.
========================
There is no way to transfer existing index from a non partitioned state to a partitioned state.
The only option is to drop and re-create the index with LOCAL clause.
How to check if index is Global or Local?
All indexes are listed in DBA_INDEXES
Partitioned indexes appear also in DBA_PART_INDEXES.
Query DBA_TABLES, DBA_PART_TABLES , DBA_INDEXES, DBA_PART_INDEXES, USER_SEGMENTS.
SELECT owner, table_name, tablespace_name, partitioned
FROM DBA_TABLES
WHERE table_name = 'MONTHLY_CALLS_AGG';
OWNER TABLE_NAME TABLESPACE_NAME PARTITIONED
------------- -------------------- -------------------- -----------
DWNG MONTHLY_CALLS_AGG YES
SELECT owner, table_name, partitioning_type
FROM DBA_PART_TABLES
WHERE table_name = 'MONTHLY_CALLS_AGG';
OWNER TABLE_NAME PARTITIONING_TYPE
------------- -------------------- ----------------------------
DWNG MONTHLY_CALLS_AGG RANGE
SELECT segment_name, partition_name, segment_type, tablespace_name
FROM USER_SEGMENTS
WHERE segment_name = 'MONTHLY_CALLS_AGG_PK';
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- --------------- ---------------- --------------------
MONTHLY_CALLS_AGG_PK INDEX DWNG_MONTHLY_AGG_IX
SELECT index_name, table_owner, table_name, partitioned
FROM USER_INDEXES
WHERE table_name = 'MONTHLY_CALLS_AGG'
INDEX_NAME TABLE_OWNE TABLE_NAME PARTITIONED
------------------------------ ---------- -------------------- -----------
MONTHLY_CALLS_AGG_PK DWNG MONTHLY_CALLS_AGG NO
DROP INDEX MONTHLY_CALLS_AGG_PK
Index dropped
CREATE UNIQUE INDEX MY_USER.MONTHLY_CALLS_AGG_IND1 on MY_USER.MONTHLY_CALLS_AGG (year, month, customer_id, service_name, system_service) LOCAL;
Index created
SELECT index_name, table_owner, table_name, partitioned
FROM USER_INDEXES
WHERE table_name = 'MONTHLY_CALLS_AGG'
FROM USER_INDEXES
WHERE table_name = 'MONTHLY_CALLS_AGG'
INDEX_NAME TABLE_OWNE TABLE_NAME PARTITIONED
------------------------------ ---------- -------------------- -----------
MONTHLY_CALLS_AGG_IND1 DWNG MONTHLY_CALLS_AGG YES
=============================
General - Indexes on a Partitioned table
=============================
Option A - Local Indexes
Option B - Global Indexes
General - Indexes on a Partitioned table
=============================
Option A - Local Indexes
Option B - Global Indexes
Local Indexes
Each partition of a local index is associated with exactly one partition of the table.
Oracle automatically keeps the index partitions in sync with the table partitions.
New partitions are added to local indexes only when you add a partition to the underlying table, and are dropped when a partition is dropped from the underlying table.
It is impossible to manage index partitions manually.
Global Indexes
Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.
When a one of the below commands is executed on the underlying table, the index becomes unusable. To overcome this, use UPDATE GLOBAL INDEXES option.
For example:
ALTER TABLE my_table DROP PARTITION part_1 UPDATE GLOBAL INDEXES
Operations on the table that make global index unusable:
ADD (HASH), COALESCE (HASH), DROP, EXCHANGE, MERGE, MOVE, SPLIT, TRUNCATE.
=============================
Zombie index after table Redefinition
=============================
Consider following scenario:
DBMS_REDEFINITION.FINISH_REDEF_TABLE was run successfully.
Now, checking objects referencing old table, an index still exist.
The index, resides on TOOLS tablespace, which is the default user tablespace.
What is going on?
When Oracle is performing the Redefinition Process, it is using Read Only Snapshots.
Per Oracle documentation:
Oracle creates several internal objects in the schema of the snapshot. Do not alter, change data in, or delete these objects manually. At the snapshot site, Oracle creates a base table, named SNAP$_snapshotname, to store the rows retrieved by the snapshot's defining query. For simple snapshots, Oracle also creates an index on the ROWID column of the base table, named I_SNAP$_snapshotname.
Indeed, the weird index name is I_SNAP$_<TABLE_NAME>. But the underlying object is not there. Seems like a bug. Oracle version was 10.1.0.5.0 The solution is to drop the index. Here are the investigation steps, and resolution.SELECT index_name, table_name, tablespace_name,status, partitionedFROM USER_INDEXES WHERE table_name LIKE '%STATEMENTS_AUDIT%'INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS PARTITIONED -------------------------- ---------------- --------------- -------- ----------- I_SNAP$_STATEMENTS_AUDIT_P STATEMENTS_AUDIT TOOLS UNUSABLE NO STATEMENTS_AUDIT_IX01 STATEMENTS_AUDIT N/A YESSELECT index_name, table_name, column_name, column_position FROM USER_IND_COLUMNS WHERE table_name LIKE '%STATEMENTS_AUDIT%';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION --------------------------- ------------------- -------------------- --------------- I_SNAP$_STATEMENTS_AUDIT_P STATEMENTS_AUDIT M_ROW$$ 1 STATEMENTS_AUDIT_IX01 STATEMENTS_AUDIT OBJECT_SCHEMA 1 STATEMENTS_AUDIT_IX01 STATEMENTS_AUDIT OBJECT_NAME 2
SELECT table_name, partitioned,tablespace_name
FROM USER_TABLES
WHERE table_name LIKE '%STATEMENTS_AUDIT%';
TABLE_NAME PARTITIONED TABLESPACE_NAME
------------------------------ ----------- ------------------------------
STATEMENTS_AUDIT YES
SELECT object_name, object_type, status
FROM USER_OBJECTS
WHERE object_name LIKE '%SNAP%';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------- ----------
I_SNAP$_STATEMENTS_AUDIT_P INDEX VALID
SELECT * FROM USER_MVIEWS SELECT * FROM USER_REFRESH SELECT * FROM USER_SNAPSHOT_LOGS SELECT * FROM USER_SNAPSHOTS All return No Data Found Seems that there is a zombie Index, on ROWID of Snapshot on STATEMENTS_AUDIT table (The table that was Redefined). Once Redefinition process has finished, the Snapshot was dropped but the Index was not dropped. Maybe this is bug in Oracle 10.1.0.5 The solution was simply to drop the zombie index.SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod PL/SQL Release 10.1.0.5.0 - Production CORE 10.1.0.5.0 Production TNS for Linux: Version 10.1.0.5.0 - Production NLSRTL Version 10.1.0.5.0 - Production
DROP INDEX I_SNAP$_<TABLE_NAME>; Index dropped========================
Reference
========================
http://oracle-base.com Reference for DBMS_REDEFINITION
http://www.orafaq.com Reference for DBMS_REDEFINITION
No comments:
Post a Comment