Oracle 12c中查詢dba_soruce ORA-00604處理

在使用PLSQL Developer工具檢視業務使用者下所有儲存過程報ORA-00604，ORA-00942，確認使用者許可權和表是存在，無失效物件。

 

透過10046進行追蹤

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

SQL>select * from dba_source;

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1 

ORA-00942：table or view does not exit

SQL>alter session set events '10046 trace name context off'

 

Bind#1

  oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00

  oacflg=10 fl2=0001 frm=01 csi=852 siz=0 off=24

  kxsbbbfp=7fb04e2d2fc8  bln=32  avl=10  flg=01

  value="DBA_SOURCE"

CLOSE #140395202554824:c=19,e=18,dep=1,type=1,tim=60072744458961

=====================

PARSE ERROR #140395202553720:len=475 dep=1 uid=0 oct=3 lid=0 tim=60072744459232 err=942

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "MODSEP")  */ 1 AS C1 FROM " MODSEP " SAMPLE BLOCK(0.0134053, 8) SEED(1)  " MODSEP " WHERE ("MODSEP "."ENDTIME" IS NULL) AND ("MODSEP "."EPID"=257975690) AND ("MODSEP "."STEPSTATE"='1' OR " MODSEP P"."STEPSTATE" IS NULL)) innerQuery

=====================

PARSE ERROR #140395204915568:len=25 dep=0 uid=145 oct=3 lid=145 tim=60072744459291 err=604

 select * from dba_source

CLOSE #140395202553720:c=1,e=1,dep=1,type=0,tim=60072744459334

WAIT #140395204915568: nam='SQL*Net break/reset to client' ela= 6 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=60072744459416

WAIT #140395204915568: nam='SQL*Net break/reset to client' ela= 79 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=60072744459502

WAIT #140395204915568: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=60072744459509

 

*** 2022-02-09 19:07:51.535

WAIT #140395204915568: nam='SQL*Net message from client' ela= 6474857 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=60072750934385

CLOSE #140395204915568:c=6,e=5,dep=0,type=0,tim=60072750934457

=====================

PARSING IN CURSOR #140395204915568 len=56 dep=0 uid=145 oct=42 lid=145 tim=60072750934603 hv=1729844458 ad='0' sqlid='5dt9w7dmjqp7a'

 A

 

從trace檔案可以看到MODSEP表，我們檢查了該表，表的狀態和資料都是正常的。

檢查一下這張表是不是在儲存過程中

SQL>select * from dba_source where upper(text) like '%MODSEP%' order by owner,name,type,line;

No rows selected

儲存過程不涉及該表。

確認dba_views中是否儲存dba_source

SQL>select text_vc c100 from dba_views where view_name='INT$DBA_SOURCE';

 

INT$DBA_SOURCE檢視的定義：

 

SELECT u.name  

,      o.name  

,      DECODE(o.type#  

             , 7, 'PROCEDURE'  

             , 8, 'FUNCTION'  

             , 9, 'PACKAGE'  

             ,11, 'PACKAGE BODY'  

             ,12, 'TRIGGER'  

             ,13, 'TYPE'  

             ,14, 'TYPE BODY'  

             ,22, 'LIBRARY'  

             ,87, 'ASSEMBLY'  

             ,'UNDEFINED'  

             )  

,      o.type#  

,      s.line  

,      s.source  

,      DECODE(bitand(o.flags, 196608)  

             ,65536 , 1  

             ,131072, 1  

             ,0  

             )  

,      TO_NUMBER(sys_context('USERENV', 'CON_ID'))  

FROM   sys."_CURRENT_EDITION_OBJ"     o  

,      sys.source$                    s  

,      sys.user$                      u  

WHERE  o.obj#               = s.obj#  

  AND  o.owner#             = u.user#  

  AND  (   o.type#         IN (7, 8, 9, 11, 12, 14, 22)  

        OR (    o.type#     = 13  

            AND o.subname  IS NULL  

           )  

       )  

UNION ALL  

SELECT u.name  

,      o.name  

,      'JAVA SOURCE'  

,      o.type#  

,      s.joxftlno  

,      s.joxftsrc  

,      DECODE(bitand(o.flags, 196608)  

             ,65536 , 1  

             ,131072, 1  

             ,0  

             )  

,      TO_NUMBER(sys_context('USERENV', 'CON_ID'))  

FROM   sys."_CURRENT_EDITION_OBJ"     o  

,      x$joxscd                       s  

,      sys.user$                      u  

WHERE  o.obj#       = s.joxftobn  

  AND  o.owner#     = u.user#  

  AND  o.type#      = 28  

;  

 

pdb中執行

SQL>select * from int$dba_source

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1 

ORA-00942：table or view does not exit

 

PDB直接執行INT$DBA_SOURCE'檢視中定義的SQL可以返回結果，初步判斷是share pool中與記憶體指向不正確，建議的方案是1、重啟CDB和PDB；2、flush 共享池。

 

避開業務高峰在PDB中執行

SQL> alter system flush shared_pool;

還是不能查詢dba_source

在CDB中執行

SQL> alter system flush shared_pool;

 

可以正常查詢dba_source了，PL SQL developer功能也可以正常檢視業務使用者下的儲存過程。

-the end-