同义词失效导致无法访问表

开发在测试库查询一张表，但是却报以下错误
SQL> select count(*) from JUSTIN;
 
select count(*) from JUSTIN
 
ORA-04045: errors during recompilation/revalidation of JUSTIN.JUSTIN
ORA-20110: No DDL in any time, please contact DBA team If you turly need it.
ORA-06512: at line 12
 
查看后发现是对应的同义词失效了，目前系统中存在trigger禁止DDL操作，
SQL> select data_object_id,object_id,object_type,status from dba_objects where object_name ='JUSTIN';
 
DATA_OBJECT_ID  OBJECT_ID OBJECT_TYPE         STATUS
-------------- ---------- ------------------- -------
         45198      45198 TABLE               VALID
                    49016 SYNONYM             INVALID

先将trigger禁止掉，然后用10046跟踪一下
SQL> alter trigger disable_ddl disable;
 
Trigger altered
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum =1));

SPID
------------
7410

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

Session altered.

SQL> select count(*) from JUSTIN;

  COUNT(*)
----------
       418

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

Session altered.
进入到udump目录查看生成的跟踪文件，用tkprof格式化，果然在跟踪文件中发现如下信息
ALTER SYNONYM "JUSTIN"."JUSTIN" COMPILE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          1           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 69     (recursive depth: 1)
********************************************************************************

当通过同义词访问其他对象时，如果该同义词为invalid会自动编译，但是由于系统禁止了DDL操作，所以会访问不成功。
将该trigger disable后执行一遍select count(*) from JUSTIN;，该同义词状态已经变为valid

SQL> select data_object_id,object_id,object_type,status from dba_objects where object_name ='JUSTIN';
 
DATA_OBJECT_ID  OBJECT_ID OBJECT_TYPE         STATUS
-------------- ---------- ------------------- -------
         45198      45198 TABLE               VALID
                    49016 SYNONYM             VALID

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-709507/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-709507/