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