程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 更多編程語言 >> 編程綜合問答 >> dictionary-ORA-00604:遞歸SQL級別2出現錯誤,ORA-00942表或視圖不存在???

dictionary-ORA-00604:遞歸SQL級別2出現錯誤,ORA-00942表或視圖不存在???

編輯:編程綜合問答
ORA-00604:遞歸SQL級別2出現錯誤,ORA-00942表或視圖不存在???

安裝的是10goracle數據庫,安裝完成後倒入一個a.dmp文件(文件是從11g處得來的),剛開始導入錯誤,後來修改了a.dmp的頭部。導入成功,現在使用設置的管理員不能刪除用戶,不能刪除表,使用sys和system也不行。
圖片說明

查看網上前輩的經驗,查看對應的文件,也不知道什麼錯誤,求大神幫幫忙:
對應的文件:E:\oracle\product\10.2.0\db_1\admin\sstf\udump\sstf_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:\oracle\product\10.2.0\db_1\admin\sstf\bdump\alert_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

文件太長,只截取部分。

最佳回答:


解決方法:刪除數據庫database,再重新建立數據庫。默認分配兩個用戶sys和system,設置密碼為sys/sys,system/sys,使用命令行創建立表空間
tablespace,創建用戶並賦予權限。具體命令如下:

--創建表空間
create tablespace 表空間名 datafile 'E:\oracle\product\10.2.0\oradata\cebhk.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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved