近段在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/