普通表在线重定义为分区表过程中报错，数值范围超过了分区限制大小，那么想要重新对表进行在线重定义需要经过哪些步骤呢？这个例子记录了处理过程：
SALES@ORCL>exec dbms_redefinition.start_redef_table(‘SALES‘, ‘SALES‘, ‘SALES_P‘);
BEGIN dbms_redefinition.start_redef_table(‘SALES‘, ‘SALES‘, ‘SALES_P‘); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
SALES@ORCL>DROP TABLE SALES_P;
DROP TABLE SALES_P
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SALES"."SALES_P"
SALES@ORCL>drop materialized view SALES_P;
Materialized view dropped.
SALES@ORCL>DROP TABLE SALES_P;
Table dropped.
SALES@ORCL>create table SALES_P
(略。。。。
)
PARTITION BY RANGE(time)
(PARTITION P1 VALUES LESS THAN(2500000),
PARTITION P2 VALUES LESS THAN(MAXVALUE));
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Table created.
SALES@ORCL>exec dbms_redefinition.start_redef_table(‘SALES‘, ‘SALES‘, ‘SALES_P‘);
BEGIN dbms_redefinition.start_redef_table(‘SALES‘, ‘SALES‘, ‘SALES_P‘); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "SALES"."SALES" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
SALES@ORCL>drop materialized view SALES;
drop materialized view SALES
*
ERROR at line 1:
ORA-12003: materialized view "SALES"."SALES" does not exist
SALES@ORCL>exec dbms_redefinition.can_redef_table(‘SALES‘,‘SALES‘);
BEGIN dbms_redefinition.can_redef_table(‘SALES‘,‘SALES‘); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "SALES"."SALES" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
SALES@ORCL>drop materialized view LOG ON SALES;
Materialized view log dropped.
SALES@ORCL>exec dbms_redefinition.can_redef_table(‘SALES‘,‘SALES‘);
PL/SQL procedure successfully completed.
重新开始在线重定义
SALES@ORCL>exec dbms_redefinition.start_redef_table(‘SALES‘, ‘SALES‘, ‘SALES_P‘);