[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).
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
百度快照被劫持跳转到博彩页面的解决办法
近期受世界杯的影响,我们Sinesafe接过很多中小企业网站频繁的被黑客入侵篡改了快照内容的网站安全问题导致打开网站被提示博彩页面,在搜索引擎中会被提示百度网址安全中心提醒您:该页面可能已被非法篡改! 主要客户网站问题基本都是反复性质的篡改,手动清理删除掉代码只能解决当前问题,没过几天就又被篡改了内容,而且经常是篡改首页顶部的代码. 1.网站快照被劫持问题分析与解决方案处理过程 下面我们分析下客户网站,客户是Linux系统的单独服务器,网站采用的是discuz论坛程序+uchome (PHP+mysql数据库架构)由于客户的网站在百度权重很高且权重为6,所以百度收录页面也是秒收的,网站关键词的排名也很靠前。网站被篡改跳转到博彩,以及收录一些博彩内容快照的攻击问题,困扰了客户整整三年,总是反反复复的被篡改跳转。而且这些网站篡改都是隐蔽性非常强的,从百度搜索引擎搜索过来的手机用户,会直接跳转到博彩网站,直接在输入网址则不会跳转。从我们这么多年网站安全维护的经验来看,这个黑客是故意做了浏览器的判断,来让网站跳转到博彩上去,让网站的管理员无从下手寻找被跳转的踪迹,通过我们sinesafe的安全...
- 下一篇
Elastic Search 新手入门笔记(一)
前言 因为最近在写一个功能点是与Elastic Search 相关的,所以最近在完成功能的基础上,还去查了很多有关于Elastic Search的文档。Elastic Search 的 client ,还是不少的,但是现在我只用了Java High Level REST Client。下面是进行的总结,希望也可以帮助像我一样的小白。 多说一点: 这是我的第一篇笔记,作为一个马上要毕业的大学生来说,多学,多听,多积累,是很有必要的。有可能语言上比较晦涩难懂,技术的阐述上也不是那么娴熟准确,但是我会好好努力的。 为什么用到Elastic Search? 这里的Elastic Search 泛指的是全文检索。在刚接触的时候,我想过这样一个问题,在关系型数据库mysql的like进行模糊查询的效果,与Elastic Search这样的全文检索,效果几乎就是一样的,那为什么还要用全文检索呢?如果是学了一些的现在的我,遇上了刚开始接触全文检索的我的话,一定会指着自己的鼻子说:“你真是无知啊。” 原因我觉得一共有两个: 第一个是查询的速度特别快!在关系型数据库中,数据是结构化的,我们当要进行模糊查询...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2初体验,简单认识spring boot2并且搭建基础工程
- CentOS8编译安装MySQL8.0.19
- CentOS7,CentOS8安装Elasticsearch6.8.6
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- CentOS关闭SELinux安全模块
- Linux系统CentOS6、CentOS7手动修改IP地址
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果