How to Partition a Non-partitioned Table [ID 1070693.6]

PURPOSE

You have a table that is not partitioned that you would like to make into a partitioned table.  This article describes four possible methods for partitioning a non-partitioned table.

 

These steps can also be used to change other partitioning characteristics such as adding subpartitioning to a partitioned table.

 

SCOPE & APPLICATION

Users needing to partition a non-partitioned table.

 

RELATED DOCUMENTS

Note:72332.1  DIAGNOSING ORA-14097 ON ALTER TABLE EXCHANGE PARTITION

Note:105317.1 Section "VII.7 Partition exchanges

Note:472449.1       How To Partition Existing Table Using DBMS_Redefinition

 

You can partition a non-partitioned table in one of four ways:

 

A)  Export/import method

 

B)  Insert with a subquery method

 

C)  Partition exchange method

 

D)  DBMS_REDEFINITION

 

Either of these four methods will create a partitioned table from an existing non-partitioned table.

 

A. Export/import method

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

 

1)  Export your table:

 

    exp usr/pswd tables=numbers file=exp.dmp

 

2)  Drop the table:

 

    drop table numbers;

 

3)  Recreate the table with partitions:

 

    create table numbers (qty number(3), name varchar2(15))

    partition by range (qty)

    (partition p1 values less than (501),

     partition p2 values less than (maxvalue));

 

4)  Import the table with ignore=y:

 

    imp usr/pswd file=exp.dmp ignore=y

 

    The ignore=y causes the import to skip the table creation and

    continues to load all rows.

 

 

B. Insert with a subquery method

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

 

1)  Create a partitioned table:

 

    create table partbl (qty number(3), name varchar2(15))

    partition by range (qty)

    (partition p1 values less than (501),

     partition p2 values less than (maxvalue));

 

2)  Insert into the partitioned table with a subquery from the

    non-partitioned table:

 

    insert into partbl (qty, name)

       select * from origtbl;

 

3)  If you want the partitioned table to have the same name as the

    original table, then drop the original table and rename the

    new table:

 

    drop table origtbl;

    alter table partbl rename to origtbl;

 

C. Partition Exchange method

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

 

ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or

subpartition) into a non-partitioned table and a non-partitioned table into a

partition (or subpartition) of a partitioned table by exchanging their data

and index segments.

 

1) Create table dummy_t as select with the required partitions

 

2) Alter table EXCHANGE partition partition_name

       with table non-partition_table;

 

 

Example

-------

 

SQL> CREATE TABLE p_emp

   2     (sal NUMBER(7,2))

   3      PARTITION BY RANGE(sal)

   4      (partition emp_p1 VALUES LESS THAN (2000),

   5       partition emp_p2 VALUES LESS THAN (4000));

 Table created.

 

 

 SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB             MGR HIREDATE        SAL

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

      7369 SMITH      CLERK          7902 17-DEC-80       800

      7499 ALLEN      SALESMAN       7698 20-FEB-81      1600

      7521 WARD       SALESMAN       7698 22-FEB-81      1250

      7566 JONES      MANAGER        7839 02-APR-81      2975

      7654 MARTIN     SALESMAN       7698 28-SEP-81      1250

      7698 BLAKE      MANAGER        7839 01-MAY-81      2850

      7782 CLARK      MANAGER        7839 09-JUN-81      2450

      7788 SCOTT      ANALYST        7566 19-APR-87      3000

      7839 KING       PRESIDENT           17-NOV-81      5000

      7844 TURNER     SALESMAN       7698 08-SEP-81      1500

      7876 ADAMS      CLERK          7788 23-MAY-87      1100

      7900 JAMES      CLERK          7698 03-DEC-81       950

      7902 FORD       ANALYST        7566 03-DEC-81      3000

      7934 MILLER     CLERK          7782 23-JAN-82      1300

 14 rows selected.

 

SQL> CREATE TABLE dummy_y as SELECT sal

 FROM emp WHERE  sal<2000;

 Table created.

 

SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal

 BETWEEN 2000 AND 3999;

 Table created.

 

SQL> alter table p_emp exchange partition emp_p1

 with table dummy_y;

 Table altered.

 

SQL> alter table p_emp exchange partition emp_p2

 with table dummy_z;

 Table altered.

 

 

D. DBMS_REDEFINITION

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

 

See Note 472449.1 "How To Partition Existing Table Using DBMS_Redefinition" for detailed instructions.