ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DROP TABLE FAILED WITH ORA-12083 ORA-12083

DROP TABLE FAILED WITH ORA-12083 ORA-12083

原创 Linux操作系统 作者:ciwei_no1 时间:2011-05-26 15:35:12 0 删除 编辑
文章来源:http://haochunpeng.itpub.net/post/385/189303
用drop materialized view 删除物化视图后,与视图同名的表没有自动删除。于是发出drop table 命令,报错ORA-12083:must user DROP MATERIALIZED VIEW ... 很是奇怪。于是放SR:oracle给出的答复是:


ISSUE CLARIFICATION
====================
After dropping the materialized view,while trying to drop the underlying table,
it failed with below error

When I try to drop the table it gives "ORA-12083: must use DROP MATERIALIZED
VIEW ..." .

.
ISSUE VERIFICATION
===================
Verified with error description above.

.
CAUSE DETERMINATION
====================
This is because of the Bug 4287115.
This is a known issue in 9i , which is fixed in 10g.

CAUSE JUSTIFICATION
====================
the bug description clearly matches the issue here.

.
POTENTIAL SOLUTION(S)
======================
To workaround the problem , connected as sys run the following update statement.
The following update should update only one row. In case if it updates more
than one row, then rollback the transaction, which means that this workaround
does not apply to your case.

connect sys/ as sysdba
update tab$ set property = property - 100663296 where
obj# in (select obj# from obj$ where name='OT_CR_ITEM_X' and
owner# in (select USER_ID from all_users where USERNAME=''));
commit;

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

connect /
Now try dropping the table & it should go through fine.


POTENTIAL SOLUTION JUSTIFICATION(S)
====================================
This is the recommended solution.

.
SOLUTION / ACTION PLAN
=======================
Hi Chunpeng,

This problem is not fixed in 9i,but you can try the workaround given above.
this is fixed in 10g.

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

上一篇: resumable session
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    24
  • 访问量
    55588