分享程序员开发的那些事...
更新时间：2022-09-13 11:11:36
[20150604]关于同义词的问题3.txt
--前一阵子对服务器的同义词做了一些整理，参考链接：
http://blog.itpub.net/267265/viewspace-1621290/
http://blog.itpub.net/267265/viewspace-1612866/
--但是我在检查sys.obj$时发现我好像并没有删除这些同义词。我通过一些例子来说明：
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS@test> select * from dba_objects where object_name='SYN_PUBLIC_TT1';
no rows selected
SYS@test> select * from sys.obj$ where name='SYN_PUBLIC_TT1';
no rows selected
SCOTT@test> create table tt as select * from dept ;
Table created.
SCOTT@test> create synonym syn_public_tt1 for tt;
Synonym created.
SYS@test> select * from dba_objects where object_name='SYN_PUBLIC_TT1';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT SYN_PUBLIC_TT1 305137 SYNONYM 2015-06-04 11:03:23 2015-06-04 11:03:23 2015-06-04:11:03:23 VALID N N N 1
SYS@test> select obj#,DATAOBJ#,OWNER#,name from sys.obj$ where name='SYN_PUBLIC_TT1';
OBJ# DATAOBJ# OWNER# NAME
---------- ---------- ---------- --------------------
305137 84 SYN_PUBLIC_TT1
--可以发现现在已经建立了同义词。
2.如果现在删除呢？
SCOTT@test> drop synonym syn_public_tt1;
Synonym dropped.
SYS@test> select obj#,DATAOBJ#,OWNER#,name from sys.obj$ where name='SYN_PUBLIC_TT1';
OBJ# DATAOBJ# OWNER# NAME
---------- ---------- ---------- --------------------
305137 84 SYN_PUBLIC_TT1
SYS@test> select * from dba_objects where object_name='SYN_PUBLIC_TT1';
no rows selected
--XXXX!居然发现在sys.obj$依旧存在，如果你看dba_objects的定义可以发现条件存在如下：
SYS@test> select text from dba_views where owner='SYS' and 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 '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',
'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
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
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
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
and o.name='SYN_PUBLIC_TT1'
--有一个条件 o.type# != 10 /* NON-EXISTENT */.
SYS@test> select obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
OBJ# DATAOBJ# OWNER# NAME TYPE#
---------- ---------- ---------- -------------------- ----------
305137 84 SYN_PUBLIC_TT1 10
--这样视图就过滤掉了，而sys.obj$依旧存在。注意看后面的注解：/* NON-EXISTENT */。
SCOTT@test> create synonym syn_public_tt1 for tt;
Synonym created.
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ as of timestamp sysdate-1/1440 where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305137 84 SYN_PUBLIC_TT1 10
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305137 84 SYN_PUBLIC_TT1 5
--对比可以发现，drop synonym时仅仅修改TYPE#=10,并没有从sys.obj$删除。而且建立时如果存在会重用这个对象，注意看OBJ#也没有变化,rowid也没变。
3.什么时候会清理呢？
--看了一些文档，提到下次重启数据库时有smon清除 NON-EXISTENT。
SCOTT@test> drop synonym syn_public_tt1;
Synonym dropped.
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305137 84 SYN_PUBLIC_TT1 10
--重启服务器看看：
SYS@test> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
--等1会。等的时间还很长,...................
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305137 84 SYN_PUBLIC_TT1 10
--至少等了4分钟。。。。
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
no rows selected
--我扫描共享池发现执行如下：
SYS@test> select sql_id,sql_text,executions from v$sql where sql_id='59vjj34vugaav';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
59vjj34vugaav delete from obj$ where obj# = :1 1
4.继续测试：
SCOTT@test> create synonym syn_public_tt1 for tt;
Synonym created.
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAT 305141 84 SYN_PUBLIC_TT1 5
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAT 305141 84 SYN_PUBLIC_TT1 10
--等1段时间，下午来看看是否会删除。。。。
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SYN_PUBLIC_TT1';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAT 305141 84 SYN_PUBLIC_TT1 10
--依旧没有删除。我检查我的测试环境：
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type#,namespace from sys.obj$ where type#=10;
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE# NAMESPACE
------------------ ---------- ---------- ---------- -------------------- ---------- ----------
AAAAASAABAAAU2EAAo 74435 45 XDB_CONFIGURATION 10 1
AAAAASAABAAAU2EAAw 74443 45 XDB_NAMESPACES 10 1
AAAAASAABAAAU2EAAy 74445 45 XDB_DOM_HELPER 10 1
AAAAASAABAAAU2EAA1 74448 45 XDB_UTILITIES 10 1
AAAAASAABAAAU2EAA4 74451 45 XDB_TOOLS 10 1
AAAAASAABAAAU3JAAT 305141 84 SYN_PUBLIC_TT1 10 1
6 rows selected.
--视乎一些系统用户的同义次不会清除。
--引申出一个问题，除了同义词删除会出现这种情况，其它对象呢？至少表，索引不会。看看视图呢？
5.看看视图的情况:
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='TT_VIEW';
no rows selected
SCOTT@test> create view tt_view as select * from tt;
View created.
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='TT_VIEW';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305142 84 TT_VIEW 4
SCOTT@test> drop view tt_view ;
View dropped.
SYS@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='TT_VIEW';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAS 305142 84 TT_VIEW 10
--可以发现视图也是一样。
SCOTT@test> create sequence seq3;
Sequence created.
SCOTT@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SEQ3';
ROWID OBJ# DATAOBJ# OWNER# NAME TYPE#
------------------ ---------- ---------- ---------- -------------------- ----------
AAAAASAABAAAU3JAAX 305144 84 SEQ3 6
SCOTT@test> drop sequence seq3;
Sequence dropped.
SCOTT@test> select rowid,obj#,DATAOBJ#,OWNER#,name,type# from sys.obj$ where name='SEQ3';
no rows selected
--sequence又没有这个问题。