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

How to Partition a Non-partitioned Table [ID 1070693.6]
	Modified03-MAR-2010TypeBULLETINStatusPUBLISHED

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.1DIAGNOSING ORA-14097 ON ALTER TABLE EXCHANGE PARTITION

Note:105317.1Section "VII.7 Partition exchanges

Note:472449.1How 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))

3PARTITION BY RANGE(sal)

4(partition emp_p1 VALUES LESS THAN (2000),

5partition emp_p2 VALUES LESS THAN (4000));

Table created.

SQL> SELECT * FROM emp;

EMPNO ENAMEJOBMGR HIREDATESAL

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

7369 SMITHCLERK7902 17-DEC-80800

7499 ALLENSALESMAN7698 20-FEB-811600

7521 WARDSALESMAN7698 22-FEB-811250

7566 JONESMANAGER7839 02-APR-812975

7654 MARTINSALESMAN7698 28-SEP-811250

7698 BLAKEMANAGER7839 01-MAY-812850

7782 CLARKMANAGER7839 09-JUN-812450

7788 SCOTTANALYST7566 19-APR-873000

7839 KINGPRESIDENT17-NOV-815000

7844 TURNERSALESMAN7698 08-SEP-811500

7876 ADAMSCLERK7788 23-MAY-871100

7900 JAMESCLERK7698 03-DEC-81950

7902 FORDANALYST7566 03-DEC-813000

7934 MILLERCLERK7782 23-JAN-821300

14 rows selected.

SQL> CREATE TABLE dummy_y as SELECT sal

FROM emp WHEREsal<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.

在线重定义参考：

http://blog.csdn.net/xujinyang/article/details/6831289

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