关于非分区表转换分区表
----------------------------------
一般的方法How to Partition a Non-partitioned / Regular / Normal Table (文档 ID1070693.6)
Note 472449.1 "How To Partition Existing Table UsingDBMS_Redefinition"
Goal
The purpose of this document is to provide step by stepinstructions on how to convert unpartitioned table to partitionedone 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 thetable: SQL> ALTER TABLE unpar_table ADD ( CONSTRAINT unpar_table_pk PRIMARY KEY(id) );
SQL> CREATE INDEX create_date_ind ONunpar_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 followingcommand: SQL> EXEC Dbms_Redefinition.can_redef_table(USER,'unpar_table');
b. If no errors are reported, start the redefintion using thefollowing 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 tocomplete.
c. Optionally synchronize new table with interim name beforeindex 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 ONpar_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" tableand their names have been switched in the namedictionary.
g. Remove original table which now has the name of the interimtable: SQL> DROP TABLE par_table;
h. Rename all the constraints and indexes to match theoriginal names: ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TOunpar_table_pk; ALTER INDEX create_date_ind2 RENAME TOcreate_date_ind;
i. Check whether partitioning is successful ornot: 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.
