联机重组出现的问题集锦

近段在ORACLE的联机重组的时候，出现如下报错以及解决办法：

1、“PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared”报错

SQL> @checkredef
sys.dbms_redefinition.cons_use_pk);
*
ERROR at line 3:
ORA-06550: line 3, column 1:
PLS-00201: identifier 'SYS.DBMS_REDEFINITION' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

出错原因是没有赋予‘DBMS_REDEFINITION' 的执行权限。解决的办法是：

SQL> conn sys/oracle as sysdba
Connected.
SQL> grant execute on dbms_redefinition to ws_app;

Grant succeeded.

2、关于“ORA-01435: user does not exist”的报错

SQL> @checkredef
BEGIN
*
ERROR at line 1:
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 2

说明自己测试的时候，用户名出错。

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sales','woodscrew_orders',
dbms_redefinition.cons_use_pk);
END;
/

目前我的用户是WS_APP，因此将“sales"改为”WS_APP“问题解决。

3、”ORA-12091: cannot online redefine table "WS_APP"."WOODSCREW_ORDERS" with
materialized views“错误

SQL> @checkredef
BEGIN
*
ERROR at line 1:
ORA-12091: cannot online redefine table "WS_APP"."WOODSCREW_ORDERS" with
materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 2

说明WOODSCREW_ORDERS里面有物化视图和物化视图日志。解决办法，删除相关的物化视图及日志。

SQL> drop materialized view log on WOODSCREW_ORDERS;

Materialized view log dropped.

SQL> drop materialized view log on woodscrew;

Materialized view log dropped.

SQL> drop materialized view ws_app.cust_ws_order_mv;

Materialized view dropped.

最终成功！

SQL> @checkredef

PL/SQL procedure successfully completed.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8334342/viewspace-582331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8334342/viewspace-582331/