[20180702]对象名重用.txt

[20180702]对象名重用.txt

--//链接:https://amitzil.wordpress.com/2018/06/28/non-existent-objects-and-possibly-a-bug/,重复测试:

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定义有说明.

--//摘要链接:https://amitzil.wordpress.com/2018/06/28/non-existent-objects-and-possibly-a-bug/

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).