How To Partition Existing Table Using DBMS_REDEFINITION

on
How To Partition Existing Table Using DBMS_REDEFINITION


GOAL

The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

SOLUTION

1) Create un-partitioned table called: unpar_table:
SQL> CREATE TABLE unpar_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
);

2) Apply some constraints to the table:
SQL> ALTER TABLE unpar_table ADD (
CONSTRAINT unpar_table_pk PRIMARY KEY (id)
);

SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);

3) Gather statistics on the table:
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);
4) Create a Partitioned Interim Table:
SQL> CREATE TABLE par_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));

5) Start the Redefinition Process:

a. Check the redefinition is possible using the following command:
SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');
b. If no errors are reported, start the redefintion using the following command:
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

Note: This operation can take quite some time to complete.

c. Optionally synchronize new table with interim name before index creation:
SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
d. Create Constraints and Indexes:
SQL> ALTER TABLE par_table ADD (
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id)
);

SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);
e. Gather statistics on the new table:
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);
f. Complete the Redefinition Process:
SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the name dictionary.

g. Remove original table which now has the name of the interim table:
SQL> DROP TABLE par_table; 
h. Rename all the constraints and indexes to match the original names:
ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;
i. Check whether partitioning is successful or not:
SQL> SELECT partitioned
FROM user_tables
WHERE table_name = 'unpar_table';

PAR
---
YES

1 row selected.

SQL> SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'unpar_table';

PARTITION_NAME
------------------------------
unpar_table_2005
unpar_table_2006
unpar_table_2007

3 rows selected.

Restrictions for Online Redefinition of Tables in 9i

  • There must be enough space to hold two copies of the table.
  • Primary key columns cannot be modified.
  • Tables must have primary keys.
  •  Redefinition must be done within the same schema.
  •  New columns added cannot be made NOT NULL until after the redefinition operation.
  •  Tables cannot contain LONGs, BFILEs or User Defined Types.
  •  Clustered tables cannot be redefined.
  •  Tables in the SYS or SYSTEM schema cannot be redefined.
  •  Tables with materialized view logs or materialized views defined on them cannot be redefined.
  •  Horizontal sub setting of data cannot be performed during the redefinition.

Restrictions for Online Redefinition of Tables in 11.2


  • If the table is to be redefined using primary key or pseudo-primary keys (unique keys or constraints with all component columns having not null constraints), then the post-redefinition table must have the same primary key or pseudo-primary key columns. If the table is to be redefined using rowids, then the table must not be an index-organized table.
  • After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.
  • Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
  • The overflow table of an index-organized table cannot be redefined online independently.
  • Tables with fine-grained access control (row-level security) cannot be redefined online.
  • Tables for which Flashback Data Archive is enabled cannot be redefined online. You cannot enable Flashback Data Archive for the interim table.
  • Tables with BFILE columns cannot be redefined online.
  • Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted toBLOBS. Tables with LOB columns are acceptable.
  • On a system with sufficient resources for parallel execution, and in the case where the interim table is not partitioned, redefinition of a LONG column to a LOB column can be executed in parallel, provided that:
    • The segment used to store the LOB column in the interim table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled.
    • There is a simple mapping from one LONG column to one LOB column, and the interim table has only oneLOBcolumn.
    In the case where the interim table is partitioned, the normal methods for parallel execution for partitioning apply.
  • Tables in the SYS and SYSTEM schema cannot be redefined online.
  • Temporary tables cannot be redefined.
  • A subset of rows in the table cannot be redefined.
  • Only simple deterministic expressions, sequences, and SYSDATEcan be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
  • If new columns are being added as part of the redefinition and there are no column mappings for these columns, then they must not be declared NOTNULL until the redefinition is complete.
  • There cannot be any referential constraints between the table being redefined and the interim table.
  • Table redefinition cannot be done NOLOGGING.
  • For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string isNULL.
  • You can convert a VARRAY to a nested table with the CAST operator in the column mapping. However, you cannot convert a nested table to a VARRAY.

========================================================================

Oracle Database - Enterprise Edition - Version 11.2.0.2.0 and later
Information in this document applies to any platform.

PURPOSE

This Note has been written to provide some volume details and timings for the process
to convert a normal table to a partition table using the DBMS_REDEFINITION package.

The note will also include details of objects used by the process (Fast Refresh) and how
these become populated during the procedure.

The procedure is broken down into the following sections:

1.  Create the Partition Table structure required, known as the Interim table.
2.  Execute DBMS_REDEFINITION.can_redef_table...
3.  Execute DBMS_REDEFINITION.start_redef_table...
4.  Execute DBMS_REDEFINITION.sync_interim_table...
5.  Execute DBMS_REDEFINITION.finish_redef_table...
6.  Drop the interim table.

DETAILS

Worked example under 11.2.0.3

--   Initial setup of table to be partitioned.
CREATE TABLE unpar_table (
  a NUMBER, y number,
  name VARCHAR2(100), date_used date);

alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));

--  load table with 1,000,000 rows

begin
        for i in 1 .. 1000
        loop
            for j in 1 .. 1000
            loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
            end loop;
        end loop;
end;
   /
commit;

PL/SQL procedure successfully completed.
Elapsed: 00:01:56.90

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);

SELECT   num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';
  NUM_ROWS
----------
   1000000

Elapsed: 00:00:00.01

SQL> CREATE TABLE par_table (
    a NUMBER, y number,
    name VARCHAR2(100),date_used DATE)
   PARTITION BY RANGE (date_used)
    (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
     PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

--  This procedure (DBMS_REDEFINITION.start_redef_table) creates a materialized view based on a CTAS, as we can see below with
--  the PREBUILT container table.

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.69

select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME                     CONTAINER_NAME                 BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE                      PAR_TABLE                      PREBUILT

Elapsed: 00:00:00.13

--  Insert 1000 rows into the master table while the DBMS_REDEF is active.
--  This will use the mview log created by the DBMS_REDEFINITION.start_redef_table.
--  Check the MLOG$_ table to confirm these online updates have been recorded.
SQL> begin
        for i in 1001 .. 1010
        loop
            for j in 1001 .. 1100
            loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
            end loop;
        end loop;
    end;
   /
commit;

Elapsed: 00:00:00.24

SQL> select count(*) from MLOG$_UNPAR_TABLE;

  COUNT(*)
----------
      1000

--  Run the dbms_redefinition.sync_interim_table to populate the new table structure (implements a MVIEW FAST REFRESH)
--  with the online updates.  This will purge the mview log of each record applied.
--  This can be run many times and should be, before we do the Finish_REDEF_TABLE.
SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.01

ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y));


EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

--  Finish_redef_table swaps the table names so the interim table becomes the original table name.
--  After completing this step, the original table is redefined with the attributes and data of the interim table.
--  The original table is locked briefly during this procedure.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

--  Note, both tables will now be synchronised.
select count(*) from par_table ;
  COUNT(*)
----------
   1001000

select count(*) from unpar_table ;
  COUNT(*)
----------
   1001000

--  Dictionary views to confirm the change of strructure of our original table "UNPAR_TABLE".
SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE';

PAR
---
YES

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE';


PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_12                     980000
UNPAR_TABLE_15                       5000
UNPAR_TABLE_MX                      16000

--  At this point the Interim table can be dropped.
drop TABLE par_table  cascade constraints;

Note for previous versions.

For tests done under 11.1.0.7 the timings for the sync_interim_table where the interim table is large (1,000,000 in our case) the timings for this are considerably longer.  This was not reviewed in depth, but initial investigation looks like the mview refresh for DBMS_REDEFINITION has been updated during the MERGE cycle.

example:

11.1.0.7
SYNC of 1000 rows --         Elapsed: 00:01:36.30     (1.5 minutes)

=====================================================================


Oracle dbms_redefinition



The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.
- See here for a full example of dbms_redefinition
The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the "Create table as select" command:


Here is a simple execution of an online table reorganization:
Here is a simple execution of an online table reorganization:

-- Check table can be redefined
EXEC Dbms_Redefinition.Can_Redef_Table('SCOTT', 'EMPLOYEES');
-- Create new table with CTAS
CREATE TABLE scott.employees2
TABLESPACE tools AS
SELECT empno, first_name, salary as sal
FROM employees WHERE 1=2;
-- Start Redefinition
EXEC Dbms_Redefinition.Start_Redef_Table( -
  'SCOTT', -
  'EMPLOYEES', -
  'EMPLOYEES2', -
  'EMPNO EMPNO, FIRST_NAME FIRST_NAME, SALARY*1.10 SAL);
-- Optionally synchronize new table with interim data
EXEC dbms_redefinition.sync_interim_table( -
  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');
-- Add new keys, FKs and triggers
ALTER TABLE employees2 ADD
(CONSTRAINT emp_pk2 PRIMARY KEY (empno)
USING INDEX
TABLESPACE indx);
-- Complete redefinition
EXEC Dbms_Redefinition.Finish_Redef_Table( -
  'SCOTT', 'EMPLOYEES', 'EMPLOYEES2');
-- Remove original table which now has the name of the new table
DROP TABLE employees2;

Using Oracle dbms_redefinitionTo solve the problem of doing table reorgs while the database accepts updates, Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.

The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the
table, enqueue changes during the redefinition, and then re-synchronize the restructured table
with the changes that have accumulated during reorganization.exec dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');

alter table titles add constraint pk_titles primary key (title_id);

exec dbms_redefinition.can_redef_table('PUBS','TITLES');

create table titles2

as

select * from titles;

exec dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id title_id,title
title,type type,pub_id pub_id,price price,advance advance,royalty*1.1 royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');

exec dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');

exec dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');
If your reorganization fails, you must take special steps to make it re-start. Because the
redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to
release the snapshot to re-start you procedure.

The ‘dbms_redefinition.abort_redef_table’ procedure which accepts 3 parameters (schema, original table name, holding table name), and which “pops the stack” and allows you to start over.


No database is 100% self-reliant or self-maintaining, which is a good thing for DBA job security. However, the last few major versions of Oracle have greatly increased its self-diagnostic and self-monitoring capabilities. Only database structural reorganization remains one of those tasks best left to the DBA to decide when it is appropriate to perform and when to schedule its execution. That is because data is the life blood of any modern organization, and while doing various database reorganizations, the following possibilities exist:

  • The process could blow-up mid-stream, so data may be left offline
  • The process is resource-intensive and takes significant time to execute
  • Data could be momentarily inconsistent between key steps
  • Probably advisable to consider doing a backup operation just prior to
The key point is that structural reorganizations are generally important events in any database’s life cycle. Even when a reorganization activity can theoretically be performed entirely online with little or no downtime, it is often a safer bet to perform any such activities in a controlled environment. Because the one time something that can not go wrong does, the DBA will be in a better situation to resume or recover if there are not frantic customers breathing down his neck. So schedule any reorganization event with extreme caution and over- compensation.


Now  with all that said, Oracle provides a robust and reliable package for performing many common online table level reorganizations – dbms_redefinition. Much like the dbms_metadata package, dbms_redefinition provides an almost limitless set of use cases or scenarios that it can address. Many people will probably just use the OEM graphical interface, but  here is a very common example that should fulfill as key need as well as serve as a foundation for one’s own modifications. The following are the key basic steps:
1.      Verify that the table is a candidate for online redefinition
2.      Create an interim table
3.      Enable parallel DML operations
4.      Start the redefinition process (and do not stop until step 9 is done)
5.      Copy dependent objects
6.      Check for any errors
7.      Synchronize the interim table (optional)
8.      Complete the redefinition
9.      Drop the interim table
A common question is what is happening behind the scenes here? In other words, how and what is Oracle doing? Essentially, the redefinition package is merely an API to an intelligent materialized view with a materialized view log. So a local replication of the object shows while the reorganization occurs. Then it refreshes to get up-to-date for any transaction that occurred during reorganization.

====================================================================

Comments

Post a Comment

Oracle DBA Information