关于非分区表转换分区表

关于非分区表转换分区表

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

一般的方法

How to Partition a Non-partitioned / Regular / Normal Table (文档 ID 1070693.6)

Note 472449.1 "How To Partition Existing Table Using DBMS_Redefinition" 

Goal 

The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package. 

Solution 

1) Create un-partitioned table called: unpar_table: 

SQL> CREATE TABLE unpar_table ( 

id NUMBER(10), 

create_date DATE, 

name VARCHAR2(100) 

); 

2) Apply some constraints to the table: 

SQL> ALTER TABLE unpar_table ADD ( 

CONSTRAINT unpar_table_pk PRIMARY KEY (id) 

); 

SQL> CREATE INDEX create_date_ind ON unpar_table(create_date); 

3) Gather statistics on the table: 

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE); 

4) Create a Partitioned Interim Table: 

SQL> CREATE TABLE par_table ( 

id NUMBER(10), 

create_date DATE, 

name VARCHAR2(100) 

) 

PARTITION BY RANGE (create_date) 

(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')), 

PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')), 

PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE)); 

5) Start the Redefinition Process: 

a. Check the redefinition is possible using the following command: 

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table'); 

b. If no errors are reported, start the redefintion using the following command: 

SQL> BEGIN 

DBMS_REDEFINITION.start_redef_table( 

uname => USER, 

orig_table => 'unpar_table', 

int_table => 'par_table'); 

END; 

/ 

Note: This operation can take quite some time to complete. 

c. Optionally synchronize new table with interim name before index creation: 

SQL> BEGIN 

dbms_redefinition.sync_interim_table( 

uname => USER, 

orig_table => 'unpar_table', 

int_table => 'par_table'); 

END; 

/ 

d. Create Constraints and Indexes: 

SQL> ALTER TABLE par_table ADD ( 

CONSTRAINT unpar_table_pk2 PRIMARY KEY (id) 

); 

SQL> CREATE INDEX create_date_ind2 ON par_table(create_date); 

e. Gather statistics on the new table: 

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE); 

f. Complete the Redefinition Process: 

SQL> BEGIN 

dbms_redefinition.finish_redef_table( 

uname => USER, 

orig_table => 'unpar_table', 

int_table => 'par_table'); 

END; 

/ 

At this point the interim table has become the "real" table and their names have been switched in the name dictionary. 

g. Remove original table which now has the name of the interim table: 

SQL> DROP TABLE par_table; 

h. Rename all the constraints and indexes to match the original names: 

ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk; 

ALTER INDEX create_date_ind2 RENAME TO create_date_ind; 

i. Check whether partitioning is successful or not: 

SQL> SELECT partitioned 

FROM user_tables 

WHERE table_name = 'unpar_table'; 

PAR 

--- 

YES 

1 row selected. 

SQL> SELECT partition_name 

FROM user_tab_partitions 

WHERE table_name = 'unpar_table'; 

PARTITION_NAME 

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

unpar_table_2005 

unpar_table_2006 

unpar_table_2007 

3 rows selected.