开发者社区> 问答> 正文

ORA-00604:递归SQL级别2出现错误,ORA-00942表或视图不存在

安装的是10goracle数据库,安装完成后倒入一个a.dmp文件(文件是从11g处得来的),刚开始导入错误,后来修改了a.dmp的头部。导入成功,现在使用设置的管理员不能删除用户,不能删除表,使用sys和system也不行。
screenshot
查看网上前辈的经验,查看对应的文件,也不知道什么错误,求大神帮帮忙:
对应的文件:E:oracleproduct10.2.0db_1adminsstfudumpsstf_ora_4004.trc

Dump file e:\oracle\product\10.2.0\db_1\admin\sstf\udump\sstf_ora_4004.trc
PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=21488643641 hv=1569151342 ad='19f127c8'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=656,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=21488643634
*** 2015-11-16 16:43:34.143
XCTEND rlbk=0, rd_only=1

PARSING IN CURSOR #1 len=20 dep=0 uid=0 oct=53 lid=0 tim=21507012388 hv=0 ad='4bdfc04'
drop user ff cascade
END OF STMT
PARSE #1:c=0,e=1214,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=21507012383

PARSING IN CURSOR #2 len=652 dep=1 uid=38 oct=47 lid=38 tim=21507014102 hv=3055982128 ad='19da805c'
BEGIN
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
BEGIN
IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN
xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
END OF STMT
PARSE #2:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21507014097

PARSING IN CURSOR #3 len=51 dep=2 uid=38 oct=3 lid=38 tim=21507020063 hv=397653107 ad='19da3fdc'
SELECT USER_ID FROM ALL_USERS WHERE USERNAME = :B1
END OF STMT
PARSE #3:c=0,e=473,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=21507020058

PARSING IN CURSOR #4 len=37 dep=3 uid=0 oct=3 lid=0 tim=21507022119 hv=1398610540 ad='20eb5b20'
select text from view$ where rowid=:1
END OF STMT
PARSE #4:c=0,e=687,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=21507022114
EXEC #4:c=0,e=1199,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=4,tim=21507024564
FETCH #4:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=1,dep=3,og=4,tim=21507024949
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=63 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=31 us)'
EXEC #3:c=15600,e=7142,p=0,cr=2,cu=0,mis=1,r=0,dep=2,og=1,tim=21507028408
FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=21507028827
EXEC #3:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=21507029383
FETCH #3:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=21507029691
EXEC #2:c=15600,e=10901,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=21507030062

PARSING IN CURSOR #4 len=479 dep=1 uid=46 oct=47 lid=46 tim=21507030543 hv=384289956 ad='19d97b08'
declare
stmt varchar2(200);
rdf_exception EXCEPTION; pragma exception_init(rdf_exception, -20000);
BEGIN
if dictionary_obj_type = 'USER' THEN
BEGIN
EXECUTE IMMEDIATE
'begin ' ||
'mdsys.rdf_apis_internal.' ||
'notify_drop_user(''' || dictionary_obj_name || '''); ' ||
'end;';
EXCEPTION
WHEN rdf_exception THEN RAISE;
WHEN OTHERS THEN NULL;
END;
end if;
end;
END OF STMT
PARSE #4:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21507030538

PARSING IN CURSOR #2 len=58 dep=2 uid=46 oct=47 lid=46 tim=21507035288 hv=3441858174 ad='20e30644'
begin mdsys.rdf_apis_internal.notify_drop_user('FF'); end;
END OF STMT

对应的文件E:oracleproduct10.2.0db_1adminsstfbdumpalert_sstf.log

Mon Nov 16 17:07:08 2015
ORA-24010 encountered when generating server alert SMG-4120
Mon Nov 16 17:17:11 2015
ORA-24010 encountered when generating server alert SMG-4120
Mon Nov 16 17:27:14 2015
ORA-24010 encountered when generating server alert SMG-4120
Mon Nov 16 17:37:17 2015
ORA-24010 encountered when generating server alert SMG-4120
Mon Nov 16 17:47:26 2015
ORA-24010 encountered when generating server alert SMG-4120
Mon Nov 16 17:57:30 2015
ORA-24010 encountered when generating server alert SMG-4120

文件太长,只截取部分。

展开
收起
吴孟桥 2016-06-15 14:20:19 6481 0
1 条回答
写回答
取消 提交回答
  • 解决方法:删除数据库database,再重新建立数据库。默认分配两个用户sys和system,设置密码为sys/sys,system/sys,使用命令行创建立表空间
    tablespace,创建用户并赋予权限。具体命令如下:

    --创建表空间
    create tablespace 表空间名 datafile 'E:oracleproduct10.2.0oradatacebhk.dbf' size 2048m autoextend on next 2176m maxsize 8192m
    logging online permanent blocksize 8192 extent management local autoallocate default nocompress segment space management auto;

    --创建用户并赋予权限
    create user 用户名 identified by 密码 default tablespace 表空间名;
    grant connect,resource,dba,exp_full_database,imp_full_database to pfm;

    2019-07-17 19:39:09
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载