Hash Partitioning an Existing Table using DBMS_REDEFINITION in Oracle 11gR2 RAC Environment

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables). This is typically used where ranges aren't appropriate, i.e. invoiceNumber, productID, OrderID etc.

Implementation of HASH partition in FXXX_ACCOUNT table. Below are details step:

Step-1: Check Redefinition possible in FXXX_ACCOUNT for Partition.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('Nxxx24', 'Fxxx_ACCOUNT');

PL/SQL procedure successfully completed.

Step-2: Check Index, constraint & data in Fxxx_ACCOUNT

SQL> select count(1) from Fxxx_ACCOUNT;

  COUNT(1)

----------

   2049406

SQL> select table_name, index_name from user_indexes where table_name='Fxxx_ACCOUNT';

TABLE_NAME                     INDEX_NAME

------------------------------ ------------------------------

Fxxx_ACCOUNT                   SYS_IL0001003322C00003$$

Fxxx_ACCOUNT                   Fxxx_ACCOUNT_PK

SQL> select  constraint_name, constraint_type,table_name from user_constraints where table_name='Fxxx_ACCOUNT';

CONSTRAINT_NAME                C TABLE_NAME

------------------------------ - ------------------------------

SYS_C00404236                  C Fxxx_ACCOUNT

Fxxx_ACCOUNT_PK                P Fxxx_ACCOUNT

Step-3: Check Partition is already exists on FXXX_ACCOUNT table

SQL> SELECT partitioned FROM user_tables WHERE  table_name = 'Fxxx_ACCOUNT';

PAR

---

NO

Step-4: create a partition interim table Fxxx_ACCOUNT_PAR

SQL> CREATE TABLE Fxxx_ACCOUNT_PAR(

RECID VARCHAR2(255) NOT NULL,

XMLRECORD XMLTYPE

)

PARTITION BY HASH(RECID) (

  partition p1  tablespace NBxxxDATA,

  partition p2  tablespace NBxxxDATA,

  partition p3  tablespace NBxxxDATA,

  partition p4  tablespace NBxxxDATA,

  partition p5  tablespace NBxxxDATA, 

  partition p6  tablespace NBxxxDATA,

  partition p7  tablespace NBxxxDATA,

  partition p8  tablespace NBxxxDATA,

  partition p9  tablespace NBxxxDATA,

  partition p10 tablespace NBxxxDATA,

  partition p11  tablespace NBxxxDATA,

  partition p12  tablespace NBxxxDATA,

  partition p13  tablespace NBxxxDATA,

  partition p14  tablespace NBxxxDATA,

  partition p15  tablespace NBxxxDATA

 );

/

Step-5: Start the Redefinition Process

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

Session altered.

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

Session altered.

SQL> BEGIN

  2    DBMS_REDEFINITION.start_redef_table(

  3      uname      => ' Nxxx24',

  4      orig_table => 'Fxxx_ACCOUNT',

  5      int_table  => 'Fxxx_ACCOUNT_PAR');

  6  END;

  7  /

PL/SQL procedure successfully completed.

Note: this process take quite some time.

Step-6: Synchronize new table with interim data before index creation

SQL> BEGIN

  2    DBMS_REDEFINITION.sync_interim_table(

  3      uname      => ' Nxxx24',

  4      orig_table => 'Fxxx_ACCOUNT',

  5      int_table  => 'Fxxx_ACCOUNT_PAR');

  6  END;

  7  /

PL/SQL procedure successfully completed.

Step-7: The dependent objects will need to be created against the new table. This is done using the COPY_TABLE_DEPENDENTS procedure. We can decide which dependencies should be copied.

SET SERVEROUTPUT ON

DECLARE

l_errors  NUMBER;

BEGIN

DBMS_REDEFINITION.copy_table_dependents(

uname            => 'NBxxx24',

orig_table       => 'Fxxx_ACCOUNT',

int_table        => 'Fxxx_ACCOUNT_PAR',

copy_indexes     => DBMS_REDEFINITION.cons_orig_params,

copy_triggers    => TRUE,

copy_constraints => TRUE,

copy_privileges  => TRUE,

ignore_errors    => FALSE,

num_errors       => l_errors,

copy_statistics  => FALSE,

copy_mvlog       => FALSE);

DBMS_OUTPUT.put_line('Errors=' || l_errors);

END;

/

Note: we can do that manually instead of COPY_TABLE_DEPENDENTS 

Step-8: Finish the Redefinition Process.  At this process interim table has become the "real" table and their names have been switched in the data dictionary.

BEGIN

  DBMS_REDEFINITION.finish_redef_table(

    uname      => 'Nxxx24',       

    orig_table => 'Fxxx_ACCOUNT',

    int_table  => 'Fxxx_ACCOUNT_PAR');

END;

Step-9: Check whether partition is create in Fxxx_ACCOUNT

> Partition create check IN Fxxx_ACCOUNT

SQL> SELECT partitioned FROM user_tables WHERE  table_name = 'FXXX_ACCOUNT';

PAR

---

YES

> Partition wise total row INTO in Fxxx_ACCOUNT

SQL> SELECT 'p1', count(1) FROM FBNK_ACCOUNT partition (p1) UNION ALL
  2  SELECT 'p2', count(1) FROM FBNK_ACCOUNT partition (p2) UNION ALL
  3  SELECT 'p3', count(1) FROM FBNK_ACCOUNT partition (p3) UNION ALL
  4  SELECT 'p4', count(1) FROM FBNK_ACCOUNT partition (p4) UNION ALL
  5  SELECT 'p5', count(1) FROM FBNK_ACCOUNT partition (p5) UNION ALL
  6  SELECT 'p6', count(1) FROM FBNK_ACCOUNT partition (p6) UNION ALL
  7  SELECT 'p7', count(1) FROM FBNK_ACCOUNT partition (p7) UNION ALL
  8  SELECT 'p8', count(1) FROM FBNK_ACCOUNT partition (p8) UNION ALL
  9  SELECT 'p9', count(1) FROM FBNK_ACCOUNT partition (p9) UNION ALL
 10  SELECT 'p10', count(1) FROM FBNK_ACCOUNT partition (p10) UNION ALL
 11  SELECT 'p11', count(1) FROM FBNK_ACCOUNT partition (p11) UNION ALL
 12  SELECT 'p12', count(1) FROM FBNK_ACCOUNT partition (p12) UNION ALL
 13  SELECT 'p13', count(1) FROM FBNK_ACCOUNT partition (p13) UNION ALL
 14  SELECT 'p14', count(1) FROM FBNK_ACCOUNT partition (p14) UNION ALL

 15  SELECT 'p15', count(1) FROM FBNK_ACCOUNT partition (p15);

> After Partition total row in FXXX_ACCOUNT

SQL> select count(1) from fbnk_account;

  COUNT(1)

----------

   2049406