s****y
发帖数: 581 | 1 大家说说这两个output会有区别吗（在同一个schema下）？
如果有，区别是什么？
另外， 有了user＿objects，是不是CAT view其实是多余的？
（１）
select distinct table_name
from cat
（２)
select distinct object_name
from user_objects
where object_type in
(
select distinct table_type
from cat
)
) | B*****g
发帖数: 34098 | 2 oracle 9i, user_objects should cover cat
CREATE OR REPLACE FORCE VIEW sys.user_objects
(
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary
) AS
SELECT 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, '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, 'SECURITY PROFILE',
59, 'RULE',
62, 'EVALUATION CONTEXT',
'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')
FROM sys.obj$ o
WHERE o.owner# = USERENV ('SCHEMAID')
AND o.linkname IS NULL
AND (o.type# NOT IN (1 /* INDEX - handled below
*/
, 10 /* NON-EXISTENT
*/
)
OR (o.type# = 1
AND 1 = (SELECT 1
FROM sys.ind$ i
WHERE i.obj# = o.obj#
AND i.type# IN (1, 2, 3, 4, 6, 7, 9))))
AND o.name != '_NEXT_OBJECT'
AND o.name != '_default_auditing_options_'
UNION ALL
SELECT l.name,
NULL,
TO_NUMBER (NULL),
TO_NUMBER (NULL),
'DATABASE LINK',
l.ctime,
TO_DATE (NULL),
NULL,
'VALID',
'N',
'N',
'N'
FROM sys.link$ l
WHERE l.owner# = USERENV ('SCHEMAID');
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;
【在 s****y 的大作中提到】
: 大家说说这两个output会有区别吗（在同一个schema下）？
: 如果有，区别是什么？
: 另外， 有了user＿objects，是不是CAT view其实是多余的？
: （１）
: select distinct table_name
: from cat
: （２)
: select distinct object_name
: from user_objects
: where object_type in
|
|
