Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › drop user cascade报错

0 积分	1 好友	4 主题

发消息

[升级] drop user cascade报错

1^#

发表于 2013-4-12 08:35:16 | 查看: 6962| 回复: 9

ML问题描述如下：

数据库是9i的，在执行imp导入数据时，不知为何导入被中断了，为了确保导入数据的完整与非重复行，客户执行了drop user cascade，执行结果如下：
SQL> drop user SZGS cascade;
drop user SZGS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SZGS用户删除不掉，为了查明原因，用sql_trace跟踪，设置10064事件，结果如下：

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 4';

Session altered.

SQL> drop user SZGS cascade;
drop user SZGS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SQL> alter session set sql_trace=false;

Session altered.

通过追踪到的trace文件，如何判定drop user cascade报错的原因？

drop_user_errors.txt

22.04 KB, 下载次数: 1124

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

10^#

发表于 2013-4-12 12:56:53

desc sys.dbms_isnapshot

@?/rdbms/admin/prvtsnap.plb ==>运行下

回复只看该作者道具举报

Jialin.Lee

9^#

发表于 2013-4-12 11:55:34

cargoo 发表于 2013-4-12 10:34
是否从10g导入9i，而且是在导入MATERIALIZED VIEW SZGS.DM_SWJG_RS时出错

不是，我是同版本之间导入导出的！

回复只看该作者道具举报

cargoo

8^#

发表于 2013-4-12 10:34:02

是否从10g导入9i，而且是在导入MATERIALIZED VIEW SZGS.DM_SWJG_RS时出错

回复只看该作者道具举报

Jialin.Lee

7^#

发表于 2013-4-12 10:25:24

xin_chy 发表于 2013-4-12 09:51
/**drop user ... cascade**/
Oracle Database does not drop materialized views in other schemas that a ...

请看我做的errorstack信息：
/oracle/app/admin/orazhyy/udump/orazhyy_ora_6554176.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/product/9.2.1
System name: AIX
Node name: lpar2
Release: 3
Version: 5
Machine: 00C8121D4C00
Instance name: orazhyy
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 6554176, image: oracle@lpar2 (TNS V1-V3)

*** 2013-04-12 10:35:16.484
*** SESSION ID:(33.17202) 2013-04-12 10:35:16.472
Warning: Exception during DBMS_CDC_UTILITY.DROP_USER SZGS
Warning: Exception during DROP MATERIALIZED VIEW SZGS.CTAIS_SB_ZSXX_ZS1
*** 2013-04-12 10:35:16.526
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.RES_14_ZZXL
*** 2013-04-12 10:35:16.541
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.DM_SWJG_RS
*** 2013-04-12 10:35:16.542
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.RES_14_HZ
*** 2013-04-12 10:35:16.543
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.DM_HYML
*** 2013-04-12 10:35:16.544
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

回复只看该作者道具举报

Jialin.Lee

6^#

发表于 2013-4-12 10:24:46

cargoo 发表于 2013-4-12 09:37
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1 ...

根据errorstack的信息，可以看到貌似是因为drop这个用户时相应的物化视图报错了。。。

回复只看该作者道具举报

Jialin.Lee

5^#

发表于 2013-4-12 10:22:28

cargoo 发表于 2013-4-12 09:37
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1 ...

确实我查看了没有个这个包。。。然后我有做了errorstack信息如下：
/oracle/app/admin/orazhyy/udump/orazhyy_ora_6554176.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/product/9.2.1
System name: AIX
Node name: lpar2
Release: 3
Version: 5
Machine: 00C8121D4C00
Instance name: orazhyy
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 6554176, image: oracle@lpar2 (TNS V1-V3)

*** 2013-04-12 10:35:16.484
*** SESSION ID:(33.17202) 2013-04-12 10:35:16.472
Warning: Exception during DBMS_CDC_UTILITY.DROP_USER SZGS
Warning: Exception during DROP MATERIALIZED VIEW SZGS.CTAIS_SB_ZSXX_ZS1
*** 2013-04-12 10:35:16.526
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.RES_14_ZZXL
*** 2013-04-12 10:35:16.541
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.DM_SWJG_RS
*** 2013-04-12 10:35:16.542
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.RES_14_HZ
*** 2013-04-12 10:35:16.543
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Warning: Exception during DROP MATERIALIZED VIEW SZGS.DM_HYML
*** 2013-04-12 10:35:16.544
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

回复只看该作者道具举报

xin_chy

4^#

发表于 2013-4-12 09:51:43

/**drop user ... cascade**/
Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
根据10064 trace event，你使用用户登录，先删除物化视图，然后再进行用户删除试试。

回复只看该作者道具举报

cargoo

3^#

发表于 2013-4-12 09:37:54

ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_ISNAPSHOT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

检查SYS.DBMS_ISNAPSHOT包体是否存在

回复只看该作者道具举报

sheep_yang678

2^#

发表于 2013-4-12 08:37:09

Warning: Exception during DROP MATERIALIZED VIEW SZGS.RES_14_ZZXL

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册