Oracle對於過程會重用OBJECT_ID。這裡的過程是指過程、函式、包和物件的總稱。

這裡透過TRACE來研究一下，Oracle內部是如何實現的。

OBJECT_ID的重用：http://yangtingkun.itpub.net/post/468/457962

前一篇文章透過一些測試的結果推測了Oracle關於OBJECT_ID重用的實現，這裡透過TRACE功能來簡單分析一下Oracle到底是如何工作的。

重啟資料庫後，首先刪除P_TEST，然後對建立過程的操作進行TRACE：

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

會話已更改。

SQL> CREATE PROCEDURE P_TEST AS
2 BEGIN
3 NULL;
4 END;
5 /

過程已建立。

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT OFF';

會話已更改。

然後再次刪除P_TEST過程，對建立P_TEST過程的操作進行TRACE：

SQL> DROP PROCEDURE P_TEST;

過程已刪除。

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

會話已更改。

SQL> CREATE PROCEDURE P_TEST AS
2 BEGIN
3 NULL;
4 END;
5 /

過程已建立。

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT OFF';

會話已更改。

最後跟蹤刪除過程的操作：

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

會話已更改。

SQL> DROP PROCEDURE P_TEST;

過程已刪除。

SQL> ALTER SESSION SET EVENTS = '10046 TRACE NAME CONTEXT OFF';

會話已更改。

為了描述方便，將這三個TRACE操作對應的TRACE檔案分別命名為TRACE1、TRACE2和TRACE3。TRACE1對應的建立過程由於是在剛重啟資料庫後，因此沒有重用OBJECT_ID，重新分配了一個OBJECT_ID。TRACE2對應的建立過程，重用了以前存在的OBJECT_ID。而TRACE3記錄了刪除過程中，Oracle如何進行操作。

由於TRACE中的內容眾多，而且絕大部分與當前的主題無關，這裡只列出部分與OBJ$表有關的操作。

首先看TRACE1中如何處理：

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=07edcf7c bln=22 avl=02 flg=05
value=61
Bind#1
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=07edcf50 bln=32 avl=06 flg=05
value="P_TEST"
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=07edcf2c bln=24 avl=02 flg=05
value=1
=====================
PARSING IN CURSOR #3 len=198 dep=1 uid=0 ct=3 lid=0 tim=9453664822 hv=4125641360 ad='336db760'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #3:c=0,e=166,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9453664816
FETCH #3:c=0,e=31,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=9453664902
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=07edcf7c bln=22 avl=02 flg=05
value=1
Bind#1
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=07edcf50 bln=32 avl=06 flg=05
value="P_TEST"
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=07edcf2c bln=24 avl=02 flg=05
value=1
=====================
PARSING IN CURSOR #4 len=198 dep=1 uid=0 ct=3 lid=0 tim=9453665238 hv=4125641360 ad='336db760'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #4:c=0,e=169,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9453665233
FETCH #4:c=0,e=28,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=9453665318

Oracle分別在當前使用者下和PUBLIC使用者下查詢同名的物件，都沒有找到。

PARSING IN CURSOR #6 len=205 dep=1 uid=0 ct=2 lid=0 tim=9454380157 hv=3861105107 ad='27dc35dc'
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
END OF STMT
PARSE #6:c=0,e=753,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=9454380151
BINDS #6:
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=07ecfbdc bln=22 avl=02 flg=05
value=61
Bind#1
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=27dd1b36 bln=32 avl=06 flg=09
value="P_TEST"
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=07ecfbb8 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=07ecfb94 bln=24 avl=04 flg=05
value=55506
Bind#4
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=07ecfb70 bln=24 avl=02 flg=05
value=7
Bind#5
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=27dd1c29 bln=08 avl=07 flg=09
value="3/23/2008 11:21:45"
Bind#6
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=27dd1c30 bln=08 avl=07 flg=09
value="3/23/2008 11:21:45"
Bind#7
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=27dd1c37 bln=08 avl=07 flg=09
value="3/23/2008 11:21:45"
Bind#8
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=07ecfb4c bln=24 avl=02 flg=05
value=1
Bind#9
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#10
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#11
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#12
acdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=07ecfb28 bln=24 avl=00 flg=05
Bind#13
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=07ecfb04 bln=24 avl=01 flg=05
value=0
Bind#14
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#15
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=07ecfad0 bln=24 avl=02 flg=05
value=6
Bind#16
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=07ecfaac bln=24 avl=04 flg=05
value=65535
EXEC #6:c=0,e=1338,p=0,cr=1,cu=5,mis=1,r=1,dep=1,og=4,tim=9454381597
EXEC #5:c=203125,e=905455,p=36,cr=708,cu=75,mis=0,r=0,dep=0,og=1,tim=9454381744
WAIT #5: nam='log file sync' ela= 12370 buffer#=2753 p2=0 p3=0 obj#=3 tim=9454394158
WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=9454394241
WAIT #5: nam='SQL*Net message from client' ela= 1771 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=9454396054
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=9454396129
WAIT #0: nam='SQL*Net message from client' ela= 6537942 driver id=1413697536 #bytes=1 p3=0 obj#=3 tim=9460934099

最後Oracle在OBJ$表中插入新記錄。

看看TRACE2的情況：

PARSING IN CURSOR #4 len=95 dep=1 uid=0 ct=3 lid=0 tim=9683841885 hv=3482111077 ad='27e06d24'
select o.obj#,o.type#,o.status from dependency$ d, obj$ o where d.p_obj#=:1 and d.d_obj#=o.obj#
END OF STMT
PARSE #4:c=0,e=472,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=9683841879
BINDS #4:
kkscoacd
Bind#0
acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
acflg=00 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=07c00cf8 bln=22 avl=04 flg=05
value=55506
EXEC #4:c=0,e=1766,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=9683844176
FETCH #4:c=0,e=80,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,tim=9683844377
STAT #4 id=1 cnt=0 pid=0 pos=1 bj=0 p='NESTED LOOPS (cr=4 pr=0 pw=0 time=110 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 bj=92 p='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=96 us)'
STAT #4 id=3 cnt=0 pid=2 pos=1 bj=123 p='INDEX RANGE SCAN I_DEPENDENCY2 (cr=4 pr=0 pw=0 time=48 us)'
STAT #4 id=4 cnt=0 pid=1 pos=2 bj=18 p='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=5 cnt=0 pid=4 pos=1 bj=36 p='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'

Oracle一上來第一個查詢就使用OBJECT_ID：55506。這說明這個對應的關係應該已經在記憶體中晚上了，不是透過SQL方式實現的。

最後，Oracle透過UPDATE操作來更新OBJ$表：

PARSING IN CURSOR #4 len=331 dep=1 uid=0 ct=6 lid=0 tim=9683870303 hv=2997034431 ad='33512f2c'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #4:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9683870297
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=07c03bc8 bln=24 avl=04 flg=05
value=55506
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=07c03ba4 bln=24 avl=02 flg=05
value=7
Bind#2
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=27dd1c29 bln=08 avl=07 flg=09
value="3/23/2008 11:25:35"
Bind#3
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=27dd1c30 bln=08 avl=07 flg=09
value="3/23/2008 11:25:35"
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=27dd1c37 bln=08 avl=07 flg=09
value="3/23/2008 11:25:35"
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=07c03b80 bln=24 avl=02 flg=05
value=1
Bind#6
acdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=07c03b5c bln=24 avl=00 flg=05
Bind#7
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=07c03b38 bln=24 avl=01 flg=05
value=0
Bind#8
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#9
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=07c03b14 bln=24 avl=02 flg=05
value=6
Bind#10
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=07c03af0 bln=24 avl=04 flg=05
value=65535
Bind#11
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=07c03c10 bln=22 avl=02 flg=05
value=61
Bind#12
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=27dd1b36 bln=32 avl=06 flg=09
value="P_TEST"
Bind#13
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=07c03bec bln=24 avl=02 flg=05
value=1
Bind#14
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#15
No oacdef for this bind.
Bind#16
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#17
No oacdef for this bind.
Bind#18
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#19
No oacdef for this bind.
EXEC #4:c=0,e=779,p=0,cr=2,cu=1,mis=0,r=1,dep=1,og=4,tim=9683871174
STAT #4 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE OBJ$ (cr=2 pr=0 pw=0 time=112 us)'
STAT #4 id=2 cnt=1 pid=1 pos=1 bj=37 p='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=36 us)'
EXEC #7:c=31250,e=30138,p=0,cr=26,cu=68,mis=0,r=0,dep=0,og=1,tim=9683871327
WAIT #7: nam='log file sync' ela= 569 buffer#=3774 p2=0 p3=0 obj#=-1 tim=9683871936
WAIT #7: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9683872010
WAIT #7: nam='SQL*Net message from client' ela= 29351 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9683901399
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9683901486
*** 2008-03-23 11:25:40.625
WAIT #0: nam='SQL*Net message from client' ela= 4696512 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9688598032

最後看看執行刪除是Oracle做了什麼就一目瞭然了：

PARSING IN CURSOR #15 len=331 dep=1 uid=0 ct=6 lid=0 tim=10682519187 hv=2997034431 ad='33512f2c'
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
END OF STMT
PARSE #15:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=10682519180
BINDS #15:
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=080e1ec4 bln=24 avl=04 flg=05
value=55506
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=080e1ea0 bln=24 avl=02 flg=05
value=10
Bind#2
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=27dd1c29 bln=08 avl=07 flg=09
value="3/23/2008 11:25:35"
Bind#3
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=27dd1c30 bln=08 avl=07 flg=09
value="3/23/2008 11:42:14"
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=27dd1c37 bln=08 avl=07 flg=09
value="12/31/4712 23:59:59"
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=080e1e7c bln=24 avl=02 flg=05
value=1
Bind#6
acdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
kxsbbbfp=080e1e58 bln=24 avl=00 flg=05
Bind#7
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=080e1e34 bln=24 avl=01 flg=05
value=0
Bind#8
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#9
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=080e1e10 bln=24 avl=02 flg=05
value=6
Bind#10
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=080e1dec bln=24 avl=04 flg=05
value=65535
Bind#11
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=080e1f0c bln=22 avl=02 flg=05
value=61
Bind#12
acdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=27dd1b36 bln=32 avl=06 flg=09
value="P_TEST"
Bind#13
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=080e1ee8 bln=24 avl=02 flg=05
value=1
Bind#14
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#15
No oacdef for this bind.
Bind#16
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#17
No oacdef for this bind.
Bind#18
acdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#19
No oacdef for this bind.
EXEC #15:c=0,e=827,p=0,cr=2,cu=3,mis=0,r=1,dep=1,og=4,tim=10682520115
STAT #15 id=1 cnt=0 pid=0 pos=1 bj=0 p='UPDATE OBJ$ (cr=2 pr=0 pw=0 time=145 us)'
STAT #15 id=2 cnt=1 pid=1 pos=1 bj=37 p='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=36 us)'
EXEC #17:c=62500,e=63127,p=0,cr=201,cu=70,mis=0,r=0,dep=0,og=1,tim=10682520408
WAIT #17: nam='log file sync' ela= 448 buffer#=2307 p2=0 p3=0 obj#=-1 tim=10682520894
WAIT #17: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10682520971
WAIT #17: nam='SQL*Net message from client' ela= 943 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10682521978
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10682522052
*** 2008-03-23 11:42:20.031
WAIT #0: nam='SQL*Net message from client' ela= 5491197 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10688013280

可以看到，Oracle在刪除過程的時候，並不是想我們想象中的從OBJ$中將物件DELETE掉，而是透過UPDATE語句，將物件的狀態置於10。

從DBA_OBJECTS的定義可以看到，TYPE#為10被認為是不存在的物件：

SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_OBJECTS';

TEXT
------------------------------------------------------------------------------
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

因此這個物件被刪除後仍然存在於OBJ$，而透過DBA_OBJECTS檢視是無法查詢到的：

SQL> SELECT OBJ#, NAME, TYPE# FROM SYS.OBJ$ WHERE OBJ# = 55506;

OBJ# NAME TYPE#
---------- ------------------------------ ----------
55506 P_TEST 10

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 55506;

未選定行

Oracle就是透過這種方式來實現OBJECT_ID的重用的。

OBJECT_ID的重用（二）

相關文章