PL/SQL: ORA-00942: table or view does not exist
有兩個使用者jytest,jytest1都擁有dba許可權在jytest1使用者建立了一個表test
create user jytest identified by "jytest"
default tablespace users temporary tablespace temp;
grant dba to jytest;
grant resource to jytest;
grant connect to jytest;
create user jytest1 identified by "jytest1"
default tablespace users temporary tablespace temp;
grant dba to jytest1;
grant resource to jytest1;
grant connect to jytest1;
create table jytest1.test(userid number(20));
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52603;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST1 TEST TABLE
在使用者jytest下可以直接查詢jytest1.test表但是在jytest下建立一個過程，在過程中引用jytest1.test表編譯不透過.在過程中引用別的使用者物件不能透過role許可權來訪問,要透過物件授權才能在過程中訪問
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> select * from jytest1.test;
USERID---------------------
SQL> alter session set events '10046 trace name context off';
Session altered
它的跟蹤檔案中顯示的記錄部分資訊如下:select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#END OF STMTPARSE #2:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1355777810000085BINDS #2:kkscoacd Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7f47498 bln=22 avl=04 flg=05
value=52603上面的操作顯示了使用者jytest在執行select * from jytest1.test查詢時會查詢jytest使用者對jytest1.test表是否有select許可權
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1END OF STMTPARSE #4:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1355777810001339BINDS #4:kkscoacd Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ecffb8 bln=22 avl=04 flg=05
value=52603
上面的查詢是查詢jytest1.test表中的列的定義
但是在使用者jytest建立mytest過程時編譯時會報錯
SQL> create or replace procedure mytest
2 as
3 hj number;
4 begin
5 select count(*) into hj from jytest1.test;
6
7 end ;
8 /
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> alter procedure mytest compile;
Warning: Procedure altered with compilation errors
SQL> alter session set events '10046 trace name context off';
Session altered
Warning: Procedure created with compilation errors 而這個錯誤的真實資訊是Error: PL/SQL: ORA-00942: table or view does not exist
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52604;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST MYTEST PROCEDURE
SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=52603;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ----------------------------------- -------------------
JYTEST1 TEST TABLE
下面是alter procedure mytest compile操作的跟蹤檔案的部分資訊
在jytest.mytest過程進行編譯時,oracle會查詢與mytest過程所引用
的對物件.下面向dependency$表中插入了mytest和test兩個物件
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
END OF STMT
PARSE #5:c=999,e=367,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355777875297551
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 18:50:54"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=01 flg=05
value=0
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=03 flg=05
value=887
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29aca975 bln=08 avl=07 flg=09
value="4/18/2003 0:0:0"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b1c bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
WAIT #5: nam='db file sequential read' ela= 28 file#=1 block#=689 blocks=1 obj#=-1 tim=1355777875298765
EXEC #5:c=1000,e=1358,p=1,cr=1,cu=7,mis=1,r=1,dep=1,og=4,tim=1355777875299060
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 18:50:54"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=02 flg=05
value=1
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=04 flg=05
value=52603
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a522d1 bln=08 avl=07 flg=09
value="12/29/2013 18:44:2"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b1c bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #5:c=1000,e=741,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355777875299853
=====================
PARSING IN CURSOR #5 len=69 dep=1 uid=0 ct=2 lid=0 tim=1355777875301888 hv=544200346 ad='29a537d8'
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
END OF STMT
PARSE #5:c=1000,e=1363,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355777875301883
BINDS #5:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b88 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b64 bln=24 avl=02 flg=05
value=1
Bind#2
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7dd9b40 bln=24 avl=02 flg=05
value=9
WAIT #5: nam='db file sequential read' ela= 72 file#=1 block#=697 blocks=1 obj#=-1 tim=1355777875303656
EXEC #5:c=2999,e=2589,p=1,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1355777875304577
BINDS #3:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ddf88c bln=22 avl=02 flg=05
value=18
而當我將jytest1.test的select許可權授予給jytest
grant select on jytest1.test to jytest;
再進行編譯時就成功了
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> alter procedure mytest compile;
Procedure altered
SQL> alter session set events '10046 trace name context off';
Session altered
跟蹤檔案的部分資訊如下
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
END OF STMT
PARSE #4:c=0,e=537,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355782171491739
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=01 flg=05
value=0
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=03 flg=05
value=887
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29aca975 bln=08 avl=07 flg=09
value="4/18/2003 0:0:0"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=2000,e=1910,p=0,cr=1,cu=7,mis=1,r=1,dep=1,og=4,tim=1355782171493656
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=02 flg=05
value=1
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=04 flg=05
value=52603
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a522d1 bln=08 avl=07 flg=09
value="12/29/2013 18:44:2"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=2000,e=1717,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355782171495379
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c44 bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=29a5cae5 bln=08 avl=07 flg=09
value="12/29/2013 20:22:13"
Bind#2
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5c20 bln=24 avl=02 flg=05
value=2
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bfc bln=24 avl=03 flg=05
value=3984
Bind#4
acdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=8 ff=0
kxsbbbfp=298cc271 bln=08 avl=07 flg=09
value="6/30/2005 19:12:15"
Bind#5
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ee5bd8 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
EXEC #4:c=1000,e=984,p=0,cr=0,cu=7,mis=0,r=1,dep=1,og=4,tim=1355782171496437
=====================
多插入了一條d_obj#=52604 p_obj#=3984的記錄
SQL> select * from dependency$ a where a.d_obj#=52604;
D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY D_ATTRS D_REASON
---------- ----------- ---------- ---------- ----------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
52604 2013-12-29 0 887 2003-4-18 1
52604 2013-12-29 1 52603 2013-12-29 1
52604 2013-12-29 2 3984 2005-6-30 1 1
PARSING IN CURSOR #4 len=69 dep=1 uid=0 ct=2 lid=0 tim=1355782171496998 hv=544200346 ad='298e30f4'
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
END OF STMT
PARSE #4:c=1000,e=486,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1355782171496991
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15ec bln=22 avl=04 flg=05
value=52604
Bind#1
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15c8 bln=24 avl=02 flg=05
value=1
Bind#2
acdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=00 csi=00 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#3
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=b7ef15a4 bln=24 avl=02 flg=05
value=9
EXEC #4:c=3000,e=2183,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1355782171499507
=====================
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-751883/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-00942: table or view does not existView
- imp 時出現在ORA-00942: table or view does not existView
- job呼叫過程報錯 ORA-00942: table or view does not existView
- from v * ERROR at line 1: ORA-00942: table or view does not existErrorView
- GoldenGate 執行ddl_setup.sql出錯 ORA-00942: table or view does not existGoSQLView
- 啟動OEM時關於ora-00942：table or view does not exist的處理View
- SNMP TABLE ERROR : Requested table is empty or does not existError
- asmcmd does not exist in directoryASM
- yii2接入pgSQL(查詢不到表The table does not exist: {{%user}})SQL
- javax.media does not existJava
- PL/SQL表---table()函式用法SQL函式
- MIRO Error:Table T169V: entry G180 does not existError
- MIGO Error:Check table 169P: entry G180 does not existGoError
- Property [title] does not exist on this collection instance
- Waring: /dev/centos/swap does not existdevCentOS
- PatchObject constructor:Input file does not existObjectStruct
- PL/SQL中ORA-00942錯的進一步研究薦SQL
- PSQLexception: ERROR ： type "signed" does not existSQLExceptionError
- Laravel Class env does not exist 問題排查Laravel
- FAQ:Field DATABASE does not exist; see long textDatabase
- Check table 169P: entry XXXX does not exist的解決
- [BUG反饋]子查詢報錯，Base table or view not found: 1146 Table 'onethink.(' doesn't existView
- relation with OID 637165 does not exist
- ORA-04043: object DBA_DATA_FILES does not existObject
- ORA-04042 procedure, function, package, or package body does not existFunctionPackage
- ORA-24756: transaction does not exist問題解決
- 【Oracle】-【許可權-ORA-04043】- object does not existOracleObject
- ORA-12545 TNS: Host or Object Does not ExistObject
- PL/SQL表(oracle記憶體表)---table()函式用法SQLOracle記憶體函式
- SQL not exist out joinSQL
- Property 'context' does not exist on type 'NodeRequire'.ts(2339)ContextUI
- The user specified as a definer ('wx_root'@'%') does not exist 解決方案
- view the favorites table.View
- ABAP 資料結構啟用時的錯誤訊息 - combination reference table field does not exist資料結構
- 'mysql.column_stats' doesn't exist and Table 'mysql.index_stats' doesn't existMySqlIndex
- MySQL 5.7啟動資料庫報錯'does not exist or is not executable'MySql資料庫
- mysql 1449 ： The user specified as a definer ('root'@'%') does not exist 解決方法MySql
- org.apache.maven.plugins:maven-archetype-plugin does not existApacheMavenPlugin