end if;
else
:object_name := p1;
:sub_object := p2;
end if;
return;
end if;
begin
if :part2 is null and :part3 is null then
select 'USER', null, :part1
into :object_type, :object_owner, :object_name
from sys.all_users u
where u.username = :part1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
begin
if :part2 is null and :part3 is null and :deep != 0 then
select 'ROLE', null, :part1
into :object_type, :object_owner, :object_name
from sys.session_roles r
where r.role = :part1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
if :deep != 0 then
begin
if :part2 is null then
select constraint_type, owner, constraint_name
into :object_type, :object_owner, :object_name
from sys.all_constraints c
where c.constraint_name = :part1
and c.owner = :cur_schema
and rownum = 1;
else
select constraint_type, owner, constraint_name, :part3
into :object_type, :object_owner, :object_name, :sub_object
from sys.all_constraints c
where c.constraint_name = :part2
and c.owner = :part1
and rownum = 1;
end if;
if :object_type = 'P' then
:object_type := 'PRIMARY KEY';
end if;
if :object_type = 'U' then
:object_type := 'UNIQUE KEY';
end if;
if :object_type = 'R' then
:object_type := 'FOREIGN KEY';
end if;
if :object_type = 'C' then
:object_type := 'CHECK CONSTRAINT';
end if;
return;
exception
when no_data_found then
null;
end;
end if;
end;
再看
----- PL/SQL Call Stack -----
object line object
handle number name
0x15d17ad68 116 package body SYS.DBMS_UTILITY
......
......
SO: 0x13667b708, type: 54, owner: 0x149fa2a30, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=0x13667b708 handle=0x15e465678 mode=S lock=11a0570c8
user=15a46c3d0 session=15a46c3d0 count=1 mask=0001 savepoint=0x7675 flags=[00]
LIBRARY OBJECT HANDLE: handle=15e465678 mtx=0x15e4657a8(0) lct=1 pct=0 cdp=0
name=EXMXSQUERY.VW_EBAY_MAIL_CLCT@EBAYTRACK
hash=f0cd4f621dedeac376c5bc759c015f5e timestamp=12-22-2011 17:03:45
namespace=TABL flags=REM/KGHP/TIM/XLR/[00020020]
kkkk-dddd-llll=0000-0001-0001 lock=S pin=S latch#=14 hpc=0002 hlc=0002
lwt=0x15e465720[0x15e465720,0x15e465720] ltm=0x15e465730[0x15e465730,0x15e465730]
pwt=0x15e4656e8[0x15e4656e8,0x15e4656e8] ptm=0x15e4656f8[0x15e4656f8,0x15e4656f8]
ref=0x15e465750[0x15e465750,0x15e465750] lnd=0x15e465768[0x15e465768,0x15e465768]
LOCK INSTANCE LOCK: id=LBf0cd4f621dedeac3
PIN INSTANCE LOCK: id=NBf0cd4f621dedeac3 mode=S release=F flags=[00]
LIBRARY OBJECT: bject=1432efc78
type=SYNM flags=EXS/LOC[0005] pflags=[0000]status=INVLload=0
DATA BLOCKS:
看到这个地方，VW_EBAY_MAIL_CLCT是EBAYTRACK库上EXMXSQUERY用户下的一个同义词，应该是这个同义词失效了，一般同义词失效是其同义的对象删掉重建了，或者select权限收回了。为了证实这个认识，打电话询问开发的工程师当时有没有改动那个视图，回答时肯定的，说但是那个时间点正在create or replace，这样同义词就自动失效了。通过如下sql查询：
SQL> SELECT object_name,object_type,owner,status
2 FROM dba_objects
3 WHERE object_name='VW_EBAY_MAIL_C