5 Different Methods to Partition a non partitioned Oracle table, PARTITION EXCHANGE, SPLIT, Redefinition

Method#1: Partition Exchange

What is Partition Exchange?

ALTER TABLE EXCHANGE PARTITION can convert a partition into a non-partitioned table and vice versa
a.     Create duplicate table of non-partitioned table with required partitions
b.     Alter table EXCHANGE partition <partition_name>
with <non_partition_table_name>;



Scenario: The table example to be partitioned. We need to create interim tables to complete this activity. Please go through the example to understand this method.

1.  The structure of EXAMPLE table is as below.

set pagesize 200
set long 999999
set linesize 150
select dbms_metadata.get_ddl('TABLE','EXAMPLE_PART') from dual;

  CREATE TABLE "EXAMPLE"
   (    "ID" number(10) NOT NULL ENABLE,
        "UID" VARCHAR2(40),
        "PIX" VARCHAR2(40),
        "FNAME" VARCHAR2(100),
        "MNAME" VARCHAR2(100),
        "LNAME" VARCHAR2(100),
        "SFIX" VARCHAR2(40),
        "JTITLE" VARCHAR2(100),
        "COMNAME" VARCHAR2(40),
        "ADD1" VARCHAR2(50),
        "ADD2" VARCHAR2(50),
        "ADD3" VARCHAR2(50),
        "CTY" VARCHAR2(30),
        "STAT" VARCHAR2(20),
        "POSTCODE" VARCHAR2(10),
        "COUNTY" VARCHAR2(40),
        "COUNTRY" VARCHAR2(40),
        "PH_NUM" VARCHAR2(20),
        "FX_NUM" VARCHAR2(15)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

2.       A unique is added to the table example.

alter table example add constraint unique_example unique(id);

Table altered.

3.       Create a table with same structure of the table EXAMPLE with required partitions. Here I created 2 partitions.

  CREATE TABLE "EXAMPLE_PARTITION"
   (    "ID" number(10) NOT NULL ENABLE,
        "UID" VARCHAR2(40),
        "PIX" VARCHAR2(40),
        "FNAME" VARCHAR2(100),
        "MNAME" VARCHAR2(100),
        "LNAME" VARCHAR2(100),
        "SFIX" VARCHAR2(40),
        "JTITLE" VARCHAR2(100),
        "COMNAME" VARCHAR2(40),
        "ADD1" VARCHAR2(50),
        "ADD2" VARCHAR2(50),
        "ADD3" VARCHAR2(50),
        "CTY" VARCHAR2(30),
        "STAT" VARCHAR2(20),
        "POSTCODE" VARCHAR2(10),
        "COUNTY" VARCHAR2(40),
        "COUNTRY" VARCHAR2(40),
        "PH_NUM" VARCHAR2(20),
        "FX_NUM" VARCHAR2(15)
   ) PARTITION BY RANGE(ID)
(PARTITION EXAMPLE_P1 VALUES LESS THAN (50000),
PARTITION EXAMPLE_P2 VALUES LESS THAN (100000))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

4.       Verify the example_partition table partition details from user_tab_partitions

SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name ='EXAMPLE_PARTITION'
SQL> /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ------------EXAMPLE_PARTITION              EXAMPLE_P1                     50000
EXAMPLE_PARTITION              EXAMPLE_P2                     100000

5.       Create temporary tables to filter the data for the partitions. Here I have 2 partitions and I created two interim tables (Part1 & Part2) for partition exchange. You need to create interim tables as many partitions which you required.

SQL> Create table part1 as select * from example where id<50000;

Table created.

SQL> Create table part2 as select * from example where id between 50001 and 99999;

Table created.

6.  Exchange the interim tables with table partitions(part1 to example_p1 and part2 to example_p2)

SQL> Alter table example_partition exchange partition example_p1 with table part1;

Table altered.


SQL> Alter table example_partition exchange partition example_p2 with table part2;

Table altered.

7.       Next Drop the example table
8.  Rename the example_partition to example.

9.       Now constraints needs to be added as in the table example.
SQL>  alter table example_partition add constraint unique_example unique(id);

Table altered.

Method#2: Split Partition with Partition Exchange

Scenario: Partition the table EXAMPLE and the table definition is as below. For this example I created this table and inserted the sample data with following scripts.

1.       Create table EXAMPLE

SQL> CREATE TABLE "EXAMPLE"
  2     (    "ID" number(10) NOT NULL ENABLE,
  3          "UID" VARCHAR2(40),
  4          "PIX" VARCHAR2(40),
  5          "FNAME" VARCHAR2(100),
  6          "MNAME" VARCHAR2(100),
  7          "LNAME" VARCHAR2(100),
  8          "SFIX" VARCHAR2(40),
  9          "JTITLE" VARCHAR2(100),
 10          "COMNAME" VARCHAR2(40),
 11          "ADD1" VARCHAR2(50),
 12          "ADD2" VARCHAR2(50),
 13          "ADD3" VARCHAR2(50),
 14          "CTY" VARCHAR2(30),
 15          "STAT" VARCHAR2(20),
 16          "POSTCODE" VARCHAR2(10),
 17          "COUNTY" VARCHAR2(40),
 18          "COUNTRY" VARCHAR2(40),
 19          "PH_NUM" VARCHAR2(20),
 20          "FX_NUM" VARCHAR2(15)
 21     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 22    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 23    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 24    TABLESPACE "USERS"
 25  ;

Table created.

alter table example add constraint unique_example unique(id);

Table altered.

SQL>  insert into example (id) select rownum from dba_objects where rownum<100000;

95114 rows created.

SQL> commit;

Commit complete.

2.       Create interim table EXAMPLE_PARTITION with required one partition using clause PARTITION max_value values less than (maxvalue)


SQL>  CREATE TABLE "EXAMPLE_PARTITION"
   (    "ID" number(10) NOT NULL ENABLE,
        "UID" VARCHAR2(40),
        "PIX" VARCHAR2(40),
        "FNAME" VARCHAR2(100),
        "MNAME" VARCHAR2(100),
        "LNAME" VARCHAR2(100),
        "SFIX" VARCHAR2(40),
        "JTITLE" VARCHAR2(100),
        "COMNAME" VARCHAR2(40),
        "ADD1" VARCHAR2(50),
        "ADD2" VARCHAR2(50),
        "ADD3" VARCHAR2(50),
        "CTY" VARCHAR2(30),
        "STAT" VARCHAR2(20),
        "POSTCODE" VARCHAR2(10),
        "COUNTY" VARCHAR2(40),
        "COUNTRY" VARCHAR2(40),
        "PH_NUM" VARCHAR2(20),
        "FX_NUM" VARCHAR2(15)
   ) PARTITION BY RANGE(ID)
(PARTITION max_value values less than (maxvalue))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS";


Table created.


3.       Verify the interim table with following commands

SQL> select table_name, partition_name, high_value from user_tab_partitions
where table_name ='EXAMPLE_PARTITION'
SQL> /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ----------------
EXAMPLE_PARTITION              MAX_VALUE                      MAXVALUE

4.       Do a partition exchange with partition(max_value) and the table EXAMPLE. After the partition exchange the main table (EXAMPLE) will be empty. See the commands below


SQL> Alter table example_partition exchange partition max_value with table example;

Table altered.

SQL> select count(*) from example;

  COUNT(*)
----------
         0

SQL> select count(*) from example_partition;

  COUNT(*)
----------
     95114

5.       Create as many partitions needed using Split_partition cluase as like below

SQL>Alter table example_partition
Split partition max_value at (50000) into (partition example_p1, partition max_value);

Table altered.

SQL> Alter table example_partition
Split partition max_value at (100000) into (partition example_p2, partition max_value);
Table altered.

SQL>
  

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name ='EXAMPLE_PARTITION'
 

TABLE_NAME                     PARTITION_NAME                HIGH_VALUE
------------------------------ ------------------------------ ----------
EXAMPLE_PARTITION              EXAMPLE_P1                     50000
EXAMPLE_PARTITION              EXAMPLE_P2                     100000
EXAMPLE_PARTITION              MAX_VALUE                      MAXVALUE 

Now drop table example
Rename table example_partition to example
Add constraints to example

alter table example add constraint unique_example unique(id);

Table altered.


Method#3: Table redefinition using dbms_redefinition package

Scenario: Partition the table EXAMPLE and the table definition is as below.

SQL>  CREATE TABLE "EXAMPLE"
  2     (    "ID" number(10) NOT NULL ENABLE,
  3          "UID" VARCHAR2(40),
  4          "PIX" VARCHAR2(40),
  5          "FNAME" VARCHAR2(100),
  6          "MNAME" VARCHAR2(100),
  7          "LNAME" VARCHAR2(100),
  8          "SFIX" VARCHAR2(40),
  9          "JTITLE" VARCHAR2(100),
 10          "COMNAME" VARCHAR2(40),
 11          "ADD1" VARCHAR2(50),
 12          "ADD2" VARCHAR2(50),
 13          "ADD3" VARCHAR2(50),
 14          "CTY" VARCHAR2(30),
 15          "STAT" VARCHAR2(20),
 16          "POSTCODE" VARCHAR2(10),
 17          "COUNTY" VARCHAR2(40),
 18          "COUNTRY" VARCHAR2(40),
 19          "PH_NUM" VARCHAR2(20),
 20          "FX_NUM" VARCHAR2(15)
 21     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 22    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 23    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 24    TABLESPACE "USERS"
 25  ;

Table created.

SQL> insert into example (id) select rownum from dba_objects where rownum<100000;


95120 rows created.

SQL> COMMIT;

Commit complete.


1.       Check whether the table can be redefined online. Table must need a primary key for this method.otherwise you will get following error message. Below mentioned package will check whether redefinition is possible or not.

SQL> exec dbms_redefinition.can_redef_table('scott', 'example');
BEGIN dbms_redefinition.can_redef_table('scott', 'example'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."EXAMPLE" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1770
ORA-06512: at line 1

SQL> alter table example add primary key (id);

Table altered.

SQL> exec dbms_redefinition.can_redef_table('scott', 'example');

PL/SQL procedure successfully completed.

2.       Create a table with required partitions and this table would be the partitioned structure. The EXAMPLE table would be redefined with this structure.

  CREATE TABLE "EXAMPLE_PARTITION"
   (    "ID" number(10) NOT NULL ENABLE,
        "UID" VARCHAR2(40),
        "PIX" VARCHAR2(40),
        "FNAME" VARCHAR2(100),
        "MNAME" VARCHAR2(100),
        "LNAME" VARCHAR2(100),
        "SFIX" VARCHAR2(40),
        "JTITLE" VARCHAR2(100),
        "COMNAME" VARCHAR2(40),
        "ADD1" VARCHAR2(50),
        "ADD2" VARCHAR2(50),
        "ADD3" VARCHAR2(50),
        "CTY" VARCHAR2(30),
        "STAT" VARCHAR2(20),
        "POSTCODE" VARCHAR2(10),
        "COUNTY" VARCHAR2(40),
        "COUNTRY" VARCHAR2(40),
        "PH_NUM" VARCHAR2(20),
        "FX_NUM" VARCHAR2(15)
   ) PARTITION BY RANGE(ID)
(PARTITION EXAMPLE_P1 VALUES LESS THAN (50000),
PARTITION EXAMPLE_P2 VALUES LESS THAN (100000))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS";

Table created.
3.       Start the redefinition process using start_redef_table procedure.


SQL> exec dbms_redefinition.start_redef_table('scott', 'EXAMPLE', 'EXAMPLE_PARTITION');

PL/SQL procedure successfully completed.

declare
  error_count pls_integer := 0;
  BEGIN
  dbms_redefinition.copy_table_dependents('SCOTT', 'EXAMPLE', 'EXAMPLE_PARTITION',1, true, true, true, false,error_count);
  dbms_output.put_line('errors := ' || to_char(error_count));
  END;
  /

PL/SQL procedure successfully completed.

4.       Finish the redefinition process using finish_redef_table procedure. Now EXAMPLE table is ready with partitions.
SQL> exec dbms_redefinition.finish_redef_table('SCOTT', 'EXAMPLE', 'EXAMPLE_PARTITION');

PL/SQL procedure successfully completed.

5.  Check the partition details using below mentioned sql.
SQL> select table_name, partition_name, high_value from user_tab_partitions
  2  where table_name ='EXAMPLE';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ------------
EXAMPLE                        EXAMPLE_P1                     50000
EXAMPLE                        EXAMPLE_P2                     100000


Method#4: Using Exp/imp utility
1.      Take export of non partitioned table
2.      Drop the non partitioned table
3.      Recreate the table with partitions
4.      Import the table with ignore=y. ignore=y option skips the table creation error and continue with import
Method#5: Duplicate table

1.      Create a duplicate of non-partitioned(with different name and same structure) table with partitions
2.      Insert the data into partitioned table from non-partitioned table
3.      Drop non-partitioned table
4.      Rename the duplicate table to original table name

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google