oracle表转为分区表,【学习笔记】Oracle将普通表转换为分区表的4种方法

天萃荷净

Oracle研究中心学习笔记：分享一篇关于Oracle分区表的文章，详细介绍了4种方法将普通数据表转换为分区表的步骤。

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

Modified 03-MAR-2010     Type BULLETIN     Status PUBLISHED

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.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle将普通表转换为分区表的4种方法