ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 联机重组出现的问题集锦

联机重组出现的问题集锦

原创 Linux操作系统 作者:anycall2010 时间:2009-03-31 13:15:29 0 删除 编辑

近段在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-24

  • 博文量
    182
  • 访问量
    562997