ORACLE online redefinition-converting a common table into a partitioned table

Source: Internet
Author: User

10gR2
You need to convert a common table into monthly partitions to improve query efficiency.

The test is as follows:

1. Create a test table

SQL   T(ID   ID                                                    T  ROWNUM,CREATED   TIMING   () (     


Ii. test whether the table can be redefined online


SQL  DBMS_REDEFINITION.CAN_REDEF_TABLE(, DBMS_REDEFINITION.CAN_REDEF_TABLE(,, DBMS_REDEFINITION.CONS_USE_PK); : line ,  : identifier : line ,  SQL: Statement ignored


The error occurs because the execution permission of 'dbms _ REDEFINITION 'is not granted. The solution is:

Grant permissions to dba users

SQL    SYS.DBMS_REDEFINITION      ,   ,   , LOCK  ,     succeeded.

Return to the test user for further verification.

SQL  DBMS_REDEFINITION.CAN_REDEF_TABLE(,  DBMS_REDEFINITION.CAN_REDEF_TABLE(, , DBMS_REDEFINITION.CONS_USE_PK); : cannot online redefine  "test"."T"  no  : at "SYS.DBMS_REDEFINITION", line : at "SYS.DBMS_REDEFINITION", line : at line 

If no primary key is defined, the above error message is displayed.

Create a primary key:

SQL   t   pk_t   altered.


Verification Successful again

SQL  DBMS_REDEFINITION.CAN_REDEF_TABLE(,SQL  successfully completed.

3. Create intermediate tables and partitions

SQL  to_char((time),) (TIME),YYYYMMDD HH24:MI:SS ::   T_NEW (ID   , TIME DATE) PARTITION        (PARTITION T_2003  LESS THAN (TO_DATE(,        PARTITION T_2004  LESS THAN (TO_DATE(,        PARTITION T_2005  LESS THAN (TO_DATE(,        PARTITION T_2006  LESS THAN (TO_DATE(,        PARTITION T_2007  LESS THAN (TO_DATE(,       PARTITION T_2008  LESS THAN (TO_DATE(,       PARTITION T_2009  LESS THAN (TO_DATE(,       PARTITION T_2010  LESS THAN (TO_DATE(,       PARTITION T_2011  LESS THAN (TO_DATE(,       PARTITION T_2012  LESS THAN (TO_DATE(,       PARTITION T_2013  LESS THAN (TO_DATE(, )));

4. Online redefinition

SQL  dbms_redefinition.start_redef_table(,,SQL  successfully completed.

Materialized View during creation

SQL  OWNER                                       (  (  (  mview_name   DBMS_REDEFINITION.START_REDEF_TABLE(,,SQL    mview_name  

# After start, a materialized view is generated. If no abort exists during the middle of the process, the materialized view will exist once, and the following message will be reported during the next operation:

SQL  DBMS_REDEFINITION.START_REDEF_TABLE(, ,  DBMS_REDEFINITION.START_REDEF_TABLE(, , ); : cannot online redefine  "test"."T" : at "SYS.DBMS_REDEFINITION", line : at "SYS.DBMS_REDEFINITION", line : at line   materialized    T;

ORA-14400: inserted partition key does not map to any partition

This error indicates that when a partition table is created, the value is not included in the partition table. You can query the data to create the missing partition table.
Refer:

SQL  to_char((time),) (TIME),T_NEWYYYYMMDDT_NEW

V. Partition Data Synchronization After redefinition

SQL   dbms_redefinition.sync_interim_table(,,SQL  successfully completed.

6. Complete online redefinition

SQL   DBMS_REDEFINITION.FINISH_REDEF_TABLE(,,SQL  successfully completed.

 

If an error occurs during online redefinition

You can execute dbms_redefinition.start_redef_table before dbms_redefinition.finish_redef_table.
Run DBMS_REDEFINITION.abort_redef_table ('test', 't', 't_ new') to discard the online redefinition.

Http://www.cnblogs.com/cycsa By cycsa


References

Http://www.blogjava.net/willpower88/archive/2007/04/19/111987.html (typographical very positive! Thank you !)
Http://space.itpub.net/8334342/viewspace-582331

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.