[20180702]物件名重用.txt
[20180702]物件名重用.txt
--//連結:重複測試:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.測試:
CREATE OR REPLACE PROCEDURE testobj AS
BEGIN
NULL;
END;
/
DROP PROCEDURE testobj;
BEGIN DBMS_SCHEDULER.create_job
( job_name => 'TESTOBJ',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => systimestamp,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/
--//出現如下錯誤:
BEGIN DBMS_SCHEDULER.create_job
*
ERROR at line 1:
ORA-27477: "SCOTT"."TESTOBJ" already exists
ORA-06512: at "SYS.DBMS_ISCHED", line 135
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 1
--//明明已經刪除了TESTOBJ儲存過程,但是報錯存在.
SCOTT@test01p> select * from dba_objects where object_name='TESTOBJ';
no rows selected
--//查詢檢視dba_objects沒有記錄.
SCOTT@test01p> select * from sys.obj$ where name='TESTOBJ';
Record View
As of: 2018/7/2 20:52:37
OBJ#: 107713
DATAOBJ#:
OWNER#: 109
NAME: TESTOBJ
NAMESPACE: 1
SUBNAME:
TYPE#: 10
CTIME: 2018/7/2 20:48:44
MTIME: 2018/7/2 20:48:49
STIME: 4712/12/31 23:59:59
STATUS: 1
REMOTEOWNER:
LINKNAME:
FLAGS: 1048576
OID$:
SPARE1: 6
SPARE2: 65535
SPARE3: 109
SPARE4:
SPARE5:
SPARE6:
SIGNATURE: 71E6348AFEB8750819E90407270A8E39
SPARE7: 0
SPARE8: 0
SPARE9: 0
--//可以發現型別TYPE#: 10.
--//檢視檢視DBA_OBJECTS定義:
SCOTT@test01p> select text_vc c100,view_name,owner from dba_views where view_name='DBA_OBJECTS';
C100 VIEW_NAME OWNER
---------------------------------------------------------------------------------------------------- -------------------- --------------------
select u.name, o.name, o.subname, o.obj#, o.dataobj#, DBA_OBJECTS SYS
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 '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, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
114, 'SQL TRANSLATION PROFILE',
115, 'UNIFIED AUDIT POLICY',
'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'),
o.namespace,
o.defining_edition,
decode(bitand(o.flags, 196608),
65536, 'METADATA LINK', 131072, 'OBJECT LINK', 'NONE'),
case when o.type# in (4,5,7,8,9,11,12,13,14,22,87,114) then
decode(bitand(o.flags, 1048576), 0, 'Y', 1048576, 'N', 'Y')
else null end,
decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and o.type# != 10 /* NON-EXISTENT */
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
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', NULL, NULL,
'NONE', NULL, 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
--//注意看條件 and o.type# != 10 /* NON-EXISTENT */,表示不存在的情況.
--//也就是當刪除一些物件時,oracle會把obj$修改=10.等待其它物件可以重用.
--//再次重複測試:
CREATE OR REPLACE PROCEDURE testobj AS
BEGIN
NULL;
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 7
--//儲存過程的型別為7.
SCOTT@test01p> DROP PROCEDURE testobj;
Procedure dropped.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 10
--//type#=10,驗證自己的判斷.至於為什麼建立job時報錯,按照作者的觀點是bug.如果建立表就存在問題.
SCOTT@test01p> create table testobj(a number);
Table created.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 2
--//表的type#=2.
SCOTT@test01p> drop table testobj purge ;
Table dropped.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
no rows selected
--//drop table後物件消失.再次建立job看看.
BEGIN DBMS_SCHEDULER.create_job
( job_name => 'TESTOBJ',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => systimestamp,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107714 109 TESTOBJ 1 66
--//job type#=66.
BEGIN
DBMS_SCHEDULER.DROP_JOB
(job_name => 'SCOTT.TESTOBJ',force => FALSE);
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
no rows selected
--//視乎只有建立過程或者函式之類的保留物件名字,型別type#=10.保留重用.
--//可以檢視rdbms目錄下dcore.bsq關於obj$的建立指令碼:
create table obj$ /* object table */
( obj# number not null, /* object number */
dataobj# number, /* data layer object number */
owner# number not null, /* owner user number */
name varchar2("M_IDEN") not null, /* object name */
namespace number not null, /* namespace of object (see KQD.H): */
/* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
/* 8 = LOB, 9 = DIRECTORY, */
/* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
/* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
/* 58 = (Data Mining) MODEL */
subname varchar2("M_IDEN"), /* subordinate to the name */
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
ctime date not null, /* object creation time */
mtime date not null, /* DDL modification time */
stime date not null, /* specification timestamp (version) */
status number not null, /* status of object (see KQD.H): */
/* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
/* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
/* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
/* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */
remoteowner varchar2("M_IDEN"), /* remote owner name (remote object) */
linkname varchar2("M_XDBI"), /* link name (remote object) */
flags number, /* 0x01 = extent map checking required */
/* 0x02 = temporary object */
/* 0x04 = system generated object */
/* 0x08 = unbound (invoker's rights) */
/* 0x10 = secondary object */
/* 0x20 = in-memory temp table */
/* 0x80 = dropped table (RecycleBin) */
/* 0x100 = synonym VPD policies */
/* 0x200 = synonym VPD groups */
/* 0x400 = synonym VPD context */
/* 0x4000 = nested table partition */
oid$ raw(16), /* OID for typed table, typed view, and type */
spare1 number, /* sql version flag: see kpul.h */
spare2 number, /* object version number */
spare3 number, /* base user# */
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date,
signature raw(16), /* object signature hash value */
spare7 number, /* future use */
spare8 number,
spare9 number
)
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
--//關於type#註解:
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
--//不過裡面並沒有job type定義.不過dba_objects定義有說明.
--//摘要連結:
Research
So now it's time for some research. I checked DBA_OBJECTS but as expected I couldn't find the dropped procedure. I knew
that the object is there somewhere, I just needed to find it. It wasn't in the recycle bin either (duh, procedures don't
go to the recycle bin). The next thing was to check the low level table OBJ$, and voila, it's there.
Since it wasn't in DBA_OBJECTS but it was in OBJ$, I needed to understand what happened to it. I opened the DBA_OBJECTS
view to check the code (it queries the view _CURRENT_EDITION_OBJ which then queries OBJ$, but that's not really
relevant for this issue). In the source SQL I saw all the different object type numbers (OBJ$.TYPE# represents the
object type, so 2 is table, 4 is view, 7 is procedure and so on). Then I checked the TYPE# of my dropped object and it
was 10. The strange thing is that 10 is not in the DBA_OBJECTS view. Now it was time for some google search. Took me
some time and some digging but I managed to find Jonathan Lewis's post about non-existent objects. In this post he
explains that dropped objects are changed to type# 10 instead of being dropped for optimization purposes. Oracle should
be able to reuse the name if we create a new object with the same name before restarting the database.
Back to our case. When we drop the procedure it can still be found in OBJ$ with type=10. If we create a table after the
procedure is removed, the table is created and it reuses the procedure name. But if we don't create a table, we have the
dropped procedure with type 10, and it seems that DBMS_SCHEDULER cannot reuse the object name. For me, this seems like a
bug specific to DBMS_SCHEDULER mechanism. So far I couldn't find a known bug about it, will update here if I find
something.
Update
I wanted to thanks Paul Sammy that found the bug for me (Bug 25979086). The bug states that it happens in 12.2.0.1 and
above (which we know is not true) and is fixed in 12.2.0.2 (which doesn't exists, but I hope it is fixed in 18c).
--//連結:重複測試:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
2.測試:
CREATE OR REPLACE PROCEDURE testobj AS
BEGIN
NULL;
END;
/
DROP PROCEDURE testobj;
BEGIN DBMS_SCHEDULER.create_job
( job_name => 'TESTOBJ',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => systimestamp,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/
--//出現如下錯誤:
BEGIN DBMS_SCHEDULER.create_job
*
ERROR at line 1:
ORA-27477: "SCOTT"."TESTOBJ" already exists
ORA-06512: at "SYS.DBMS_ISCHED", line 135
ORA-06512: at "SYS.DBMS_SCHEDULER", line 288
ORA-06512: at line 1
--//明明已經刪除了TESTOBJ儲存過程,但是報錯存在.
SCOTT@test01p> select * from dba_objects where object_name='TESTOBJ';
no rows selected
--//查詢檢視dba_objects沒有記錄.
SCOTT@test01p> select * from sys.obj$ where name='TESTOBJ';
Record View
As of: 2018/7/2 20:52:37
OBJ#: 107713
DATAOBJ#:
OWNER#: 109
NAME: TESTOBJ
NAMESPACE: 1
SUBNAME:
TYPE#: 10
CTIME: 2018/7/2 20:48:44
MTIME: 2018/7/2 20:48:49
STIME: 4712/12/31 23:59:59
STATUS: 1
REMOTEOWNER:
LINKNAME:
FLAGS: 1048576
OID$:
SPARE1: 6
SPARE2: 65535
SPARE3: 109
SPARE4:
SPARE5:
SPARE6:
SIGNATURE: 71E6348AFEB8750819E90407270A8E39
SPARE7: 0
SPARE8: 0
SPARE9: 0
--//可以發現型別TYPE#: 10.
--//檢視檢視DBA_OBJECTS定義:
SCOTT@test01p> select text_vc c100,view_name,owner from dba_views where view_name='DBA_OBJECTS';
C100 VIEW_NAME OWNER
---------------------------------------------------------------------------------------------------- -------------------- --------------------
select u.name, o.name, o.subname, o.obj#, o.dataobj#, DBA_OBJECTS SYS
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 '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, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
114, 'SQL TRANSLATION PROFILE',
115, 'UNIFIED AUDIT POLICY',
'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'),
o.namespace,
o.defining_edition,
decode(bitand(o.flags, 196608),
65536, 'METADATA LINK', 131072, 'OBJECT LINK', 'NONE'),
case when o.type# in (4,5,7,8,9,11,12,13,14,22,87,114) then
decode(bitand(o.flags, 1048576), 0, 'Y', 1048576, 'N', 'Y')
else null end,
decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and o.type# != 10 /* NON-EXISTENT */
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
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', NULL, NULL,
'NONE', NULL, 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
--//注意看條件 and o.type# != 10 /* NON-EXISTENT */,表示不存在的情況.
--//也就是當刪除一些物件時,oracle會把obj$修改=10.等待其它物件可以重用.
--//再次重複測試:
CREATE OR REPLACE PROCEDURE testobj AS
BEGIN
NULL;
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 7
--//儲存過程的型別為7.
SCOTT@test01p> DROP PROCEDURE testobj;
Procedure dropped.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 10
--//type#=10,驗證自己的判斷.至於為什麼建立job時報錯,按照作者的觀點是bug.如果建立表就存在問題.
SCOTT@test01p> create table testobj(a number);
Table created.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107713 109 TESTOBJ 1 2
--//表的type#=2.
SCOTT@test01p> drop table testobj purge ;
Table dropped.
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
no rows selected
--//drop table後物件消失.再次建立job看看.
BEGIN DBMS_SCHEDULER.create_job
( job_name => 'TESTOBJ',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => systimestamp,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
OBJ# OWNER# NAME NAMESPACE TYPE#
---------- ---------- -------------------- --------- -----
107714 109 TESTOBJ 1 66
--//job type#=66.
BEGIN
DBMS_SCHEDULER.DROP_JOB
(job_name => 'SCOTT.TESTOBJ',force => FALSE);
END;
/
SCOTT@test01p> select obj#,owner#,name,namespace,type# from sys.obj$ where name='TESTOBJ';
no rows selected
--//視乎只有建立過程或者函式之類的保留物件名字,型別type#=10.保留重用.
--//可以檢視rdbms目錄下dcore.bsq關於obj$的建立指令碼:
create table obj$ /* object table */
( obj# number not null, /* object number */
dataobj# number, /* data layer object number */
owner# number not null, /* owner user number */
name varchar2("M_IDEN") not null, /* object name */
namespace number not null, /* namespace of object (see KQD.H): */
/* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
/* 8 = LOB, 9 = DIRECTORY, */
/* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
/* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
/* 58 = (Data Mining) MODEL */
subname varchar2("M_IDEN"), /* subordinate to the name */
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
ctime date not null, /* object creation time */
mtime date not null, /* DDL modification time */
stime date not null, /* specification timestamp (version) */
status number not null, /* status of object (see KQD.H): */
/* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
/* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
/* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
/* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */
remoteowner varchar2("M_IDEN"), /* remote owner name (remote object) */
linkname varchar2("M_XDBI"), /* link name (remote object) */
flags number, /* 0x01 = extent map checking required */
/* 0x02 = temporary object */
/* 0x04 = system generated object */
/* 0x08 = unbound (invoker's rights) */
/* 0x10 = secondary object */
/* 0x20 = in-memory temp table */
/* 0x80 = dropped table (RecycleBin) */
/* 0x100 = synonym VPD policies */
/* 0x200 = synonym VPD groups */
/* 0x400 = synonym VPD context */
/* 0x4000 = nested table partition */
oid$ raw(16), /* OID for typed table, typed view, and type */
spare1 number, /* sql version flag: see kpul.h */
spare2 number, /* object version number */
spare3 number, /* base user# */
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date,
signature raw(16), /* object signature hash value */
spare7 number, /* future use */
spare8 number,
spare9 number
)
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
--//關於type#註解:
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
--//不過裡面並沒有job type定義.不過dba_objects定義有說明.
--//摘要連結:
Research
So now it's time for some research. I checked DBA_OBJECTS but as expected I couldn't find the dropped procedure. I knew
that the object is there somewhere, I just needed to find it. It wasn't in the recycle bin either (duh, procedures don't
go to the recycle bin). The next thing was to check the low level table OBJ$, and voila, it's there.
Since it wasn't in DBA_OBJECTS but it was in OBJ$, I needed to understand what happened to it. I opened the DBA_OBJECTS
view to check the code (it queries the view _CURRENT_EDITION_OBJ which then queries OBJ$, but that's not really
relevant for this issue). In the source SQL I saw all the different object type numbers (OBJ$.TYPE# represents the
object type, so 2 is table, 4 is view, 7 is procedure and so on). Then I checked the TYPE# of my dropped object and it
was 10. The strange thing is that 10 is not in the DBA_OBJECTS view. Now it was time for some google search. Took me
some time and some digging but I managed to find Jonathan Lewis's post about non-existent objects. In this post he
explains that dropped objects are changed to type# 10 instead of being dropped for optimization purposes. Oracle should
be able to reuse the name if we create a new object with the same name before restarting the database.
Back to our case. When we drop the procedure it can still be found in OBJ$ with type=10. If we create a table after the
procedure is removed, the table is created and it reuses the procedure name. But if we don't create a table, we have the
dropped procedure with type 10, and it seems that DBMS_SCHEDULER cannot reuse the object name. For me, this seems like a
bug specific to DBMS_SCHEDULER mechanism. So far I couldn't find a known bug about it, will update here if I find
something.
Update
I wanted to thanks Paul Sammy that found the bug for me (Bug 25979086). The bug states that it happens in 12.2.0.1 and
above (which we know is not true) and is fixed in 12.2.0.2 (which doesn't exists, but I hope it is fixed in 18c).
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2157230/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- C#中透過ObjectPool重用物件提高程式效能C#Object物件
- Java物件重用如何降低延遲並提高效能 - MinborgJava物件
- [20200417]xdate別名.txt
- Windows修改新建.txt檔名Windows
- [20200214]xargs與別名.txt
- [20191128]date命令別名.txt
- [20220329]批量修改檔名.txt
- [20211213]完善date命令別名.txt
- 重用其他程式庫
- [20211020]XXXX_DGB服務名.txt
- 從重複到重用
- golang slice 和 string 重用Golang
- [20200109]主機名如何定位IP.txt
- [20191219]索引名裡帶回車符.txt索引
- JavaScript物件導向名詞詳解JavaScript物件
- [20210902]library_cache物件級別轉儲.txt物件
- [20180328]不要在sys建立使用者物件.txt物件
- PHP 物件導向 （三）名稱空間PHP物件
- 通過類名動態生成物件物件
- 「Js」物件按照鍵名來進行排序JS物件排序
- iOS開發-UITableView的重用機制iOSUIView
- TypeScript 中限制物件鍵名的取值範圍TypeScript物件
- 吃人的那些 Java 名詞：物件、引用、堆、棧Java物件
- JavaScript 節點物件的型別與名稱JavaScript物件型別
- [20200212]使用DBMS_SHARED_POOL.MARKHOT標識熱物件.txt物件
- 物件導向 -- 類的載入順序 類和物件的名稱空間物件
- 可重用性是一個謬論 - UWE FRIEDRICHSEN
- 如何重用瀏覽器tab開啟頁面瀏覽器
- 教你如何解決SQL server中提示物件名無效SQLServer物件
- [20200213]使用DBMS_SHARED_POOL.MARKHOT標識熱物件2.txt物件
- [20181122]瞭解資料庫快取被那些物件佔用.txt資料庫快取物件
- ThreadLocal執行緒重用時帶來的問題thread執行緒
- OpenHarmony定義元件重用樣式：@Styles裝飾器元件
- SAP ABAP Gateway Client 的 ABAP 實現，重用 HTTP ConnectionGatewayclientHTTP
- 詳解 Node.Js 中實現埠重用原理Node.js
- Android MVP架構改造~如何重用頂層業務AndroidMVP架構
- [20240930]關於共享池-表物件在庫快取探究2.txt物件快取
- [2020528]寫sql語句不要忘記給欄位加上表別名.txtSQL