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 exitSQL>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=942SELECT /* 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_sourceCLOSE #140395202553720:c=1,e=1,dep=1,type=0,tim=60072744459334WAIT #140395204915568: nam='SQL*Net break/reset to client' ela= 6 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=60072744459416WAIT #140395204915568: nam='SQL*Net break/reset to client' ela= 79 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=60072744459502WAIT #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.535WAIT #140395204915568: nam='SQL*Net message from client' ela= 6474857 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=60072750934385CLOSE #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_sourceSQL>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_sourceERROR 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-