sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

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

时间:2017-01-02 22:28   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 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种方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1442.html

Oracle研究中心

关键词:

Oracle分区表

oracle普通表转换分区表的详细过程

oracle使用imp/exp将普通表转换为分区表

oracle普通数据表数据放入分区表的方法步骤