当进入到一个陌生的用户或日常检查特定用户的时候，大家可能会在SQL*Plus中使用“select * from cat;”快速查看一下这个用户中都存在哪些我们关心的数据库对象。
sec@ora10g> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
V_T VIEW
SYN_T SYNONYM
S_T SEQUENCE
DEMO TABLE
T TABLE
问题来了：
Question：您知道CAT都能查看到哪些数据库对象么？
Answer ：包含VIEW、SYNONYM、SEQUENCE和TABLE（表不包含iot - overflow and nested tables）这四种数据库对象
看到上面的结论了吧，也就是说CAT并不能得到所有的数据库对象信息（要得到用户全部对象信息可以使用user_objects视图），不过上面所列的四种对象基本上是数据库中常见的，因此如果您只是在应用层面做技术支持或开发的话基本满足需求，这也许就是CAT的存在的意义吧。
如果您知道CAT的“出身”，就能从根本上明白CAT为什么只能查询得到上面的那些数据库对象。
OK，“CAT的探索之旅”现在开始。
1.其实，有些朋友称CAT为“CAT视图”是不正确的，它其实是一个在PUBLIC下的一个同名，来使用dbms_metadata.get_ddl做次的探索尝试
sec@ora10g> select owner,object_name, object_type from dba_objects where object_name = 'CAT';
OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -------------------
PUBLIC CAT SYNONYM
sys@ora10g> select dbms_metadata.get_ddl('SYNONYM','CAT','PUBLIC') from dual;
DBMS_METADATA.GET_DDL('SYNONYM','CAT','PUBLIC')
-----------------------------------------------
CREATE OR REPLACE PUBLIC SYNONYM "CAT" FOR "SYS"."USER_CATALOG"
2.上面查询的结论是：CAT是"SYS"."USER_CATALOG"的一个同名。那USER_CATALOG又是什么数据库对象呢，通过下面的进一步探索发现，它既是PUBLIC下的一个同名也是SYS用户下的一个视图
sec@ora10g> col owner for a10
sec@ora10g> col OBJECT_NAME for a30
sec@ora10g> select owner,object_name, object_type from dba_objects where object_name = 'USER_CATALOG';
OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -------------------
SYS USER_CATALOG VIEW
PUBLIC USER_CATALOG SYNONYM
3.我们先来查看一下这个同名的出处，发现它就是"SYS"."USER_CATALOG"的同名，所以上面得到的两个数据库对象是一致的
sys@ora10g> select dbms_metadata.get_ddl('SYNONYM','USER_CATALOG','PUBLIC') from dual;
DBMS_METADATA.GET_DDL('SYNONYM','USER_CATALOG','PUBLIC')
--------------------------------------------------------
CREATE OR REPLACE PUBLIC SYNONYM "USER_CATALOG" FOR "SYS"."USER_CATALOG"
4.到至此，我们基本上确定了CAT的真正来源了，后我们揭开它的神秘面纱，查看一下sys用户下USER_CATALOG视图的创建语句
sys@ora10g> select dbms_metadata.get_ddl('VIEW','USER_CATALOG','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','USER_CATALOG','SYS')
--------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."USER_CATALOG" ("TABLE_NAME", "TABLE_TYPE")
AS
select o.name,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED')
from sys.obj$ o
where o.owner# = userenv('SCHEMAID')
and ((o.type# in (4, 5, 6))
or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
and o.linkname is null
5.从上面USER_CATALOG视图的的创建语句我们已经能够得到CAT查询的逻辑关系了——得到数据库类型是4、5、6和2的一部分信息
结论就是开篇提到的：
Question：您知道CAT视图都能查看到那些数据库对象么？
Answer ：包含VIEW、SYNONYM、SEQUENCE和TABLE（表不包含iot - overflow and nested tables）这四种数据库对象
6.上面是一个反向推导的过程，发掘到了CAT的出处。下面我们从正面看一下这个CAT同名的完整创建过程
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
<style. /* Style. Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style.
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
<style. /* Style. Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style.
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
<style. /* Style. Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style.
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
<style. /* Style. Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style.
DROP VIEW SYS.USER_CATALOG;
CREATE OR REPLACE FORCE VIEW SYS.USER_CATALOG (
TABLE_NAME,
TABLE_TYPE
)
AS
SELECT o.name,
DECODE (o.type#,
0, 'NEXT OBJECT',
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
'UNDEFINED')
FROM sys.obj$ o
WHERE o.owner# = USERENV ('SCHEMAID')
AND ( (o.type# IN (4, 5, 6))
OR (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
AND NOT EXISTS
(SELECT NULL
FROM sys.tab$ t
WHERE t.obj# = o.obj#
AND (BITAND (t.property, 512) = 512
OR BITAND (t.property, 8192) = 8192))))
AND o.linkname IS NULL;
COMMENT ON TABLE SYS.USER_CATALOG IS 'Tables, Views, Synonyms and Sequences owned by the user';
COMMENT ON COLUMN SYS.USER_CATALOG.TABLE_NAME IS 'Name of the object';
COMMENT ON COLUMN SYS.USER_CATALOG.TABLE_TYPE IS 'Type of the object';
DROP PUBLIC SYNONYM CAT;
CREATE PUBLIC SYNONYM CAT FOR SYS.USER_CATALOG;
DROP PUBLIC SYNONYM USER_CATALOG;
CREATE PUBLIC SYNONYM USER_CATALOG FOR SYS.USER_CATALOG;
GRANT SELECT ON SYS.USER_CATALOG TO PUBLIC WITH GRANT OPTION;
7.小结
Oracle中的每一个方便大家操作的数据库对象都值得我们去探索和发现，如果您有兴趣，也可以对其他您常用的视图使用这样的方法探索一番，其乐无穷也~~
这些给我们操作数据库带来便捷的数据库对象还有tab、obj、seq、dict等等，关于这些“小”视图，您可以参考我的另一篇文章《【视图】oracle 数据字典视图之 “小”视图 CAT, TAB, SEQ, SYN ...》http://space.itpub.net/519536/viewspace-571454。
GoodLuck.
-- The End --
【探索】【CAT】对CAT出处的探索之旅
分享好友
分享这个小栈给你的朋友们，一起进步吧。
订阅须知
• 所有用户可根据关注领域订阅专区或所有专区
• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询
• 专区发布评论属默认订阅所评论专区（除付费小栈外）