DBA_TABLES DBA_OBJECTS 和DBA_SEGMENTS 不同点

Show why DBA_TABLES DBA_OBJECTS AND DBA_SEGMENTS differ when selected for TABLES?

SOLUTION

DBA_TABLES VS DBA_SEGMENTS

DBA_TABLES includes CLUSTERED TABLES, TEMPORARY, PARTITIONED, and INDEX ORGANIZED tables that are not included in
      DBA_SEGMENTS as they have no storage allocations

DBA_SEGMENTS contains TYPED (for internal use only) tables ( bitand(TAB$.property, 1) = 0 ) that are not included in DBA_TABLES

DBA_TABLES VS DBA_OBJECTS

DBA_TABLES includes the internal table _default_auditing_options_ that is not included in DBA_OBJECTS

DBA_OBJECTS include TYPED (for internal use only) tables ( bitand(TAB$.property, 1) = 0 ) that are not included in DBA_TABLES

DBA_SEGMENTS VS DBA_OBJECTS

DBA_SEGMENTS includes the internal table _default_auditing_options_ that is not included in DBA_OBJECTS

DBA_OBJECTS include CLUSTERED TABLES, TEMPORARY, PARTITIONED, and INDEX ORGANIZED tables that are not included in
      DBA_SEGMENTS as they have no storage allocations

RESEARCH

DBA_TABLES VS DBA_SEGMENTS

SELECT OWNER,TABLE_NAME FROM DBA_TABLES
    WHERE CLUSTER_NAME IS NULL
    AND TEMPORARY <> 'Y'
    AND IOT_TYPE IS NULL
    AND PARTITIONED <>'YES'
MINUS
SELECT OWNER,SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE';

0 ROWS

SELECT OWNER,SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE'
MINUS
SELECT OWNER,TABLE_NAME FROM DBA_TABLES
    WHERE CLUSTER_NAME IS NULL
    AND TEMPORARY <> 'Y'
    AND PARTITIONED <>'YES';

OWNER SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SYS KOTAD$
SYS KOTADX$
SYS KOTMD$
SYS KOTTB$
SYS KOTTBX$
SYS KOTTD$

CREATE OR REPLACE VIEW DBA_TABLES_2
(OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,
PCT_FREE, PCT_USED,
INI_TRANS, MAX_TRANS,
INITIAL_EXTENT, NEXT_EXTENT,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
FREELISTS, FREELIST_GROUPS, LOGGING,
BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
DEGREE, INSTANCES, CACHE, TABLE_LOCK,
SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
IOT_TYPE, TEMPORARY, SECONDARY, NESTED,
BUFFER_POOL, ROW_MOVEMENT,
GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING,
CLUSTER_OWNER, DEPENDENCIES, COMPRESSION)
as
select u.name, o.name, decode(bitand(t.property, 4194400), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),0, null, co.name),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32), 32, null,
decode(bitand(t.flags, 32), 0, 'YES', 'NO')),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED'))
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o, sys.obj$ cx,
sys.user$ cu
where o.owner# = u.user#
and o.obj# = t.obj#
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+);

<< removed the bitand(TAB$.property, 1) = 0 >>


SELECT OWNER,SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE'
MINUS
SELECT OWNER,TABLE_NAME FROM DBA_TABLES_2
    WHERE CLUSTER_NAME IS NULL
    AND TEMPORARY <> 'Y'
    AND PARTITIONED <>'YES';

0 ROWS



DBA_TABLES VS DBA_OBJECTS

SELECT OWNER,TABLE_NAME FROM DBA_TABLES
    WHERE TABLE_NAME<>'_default_auditing_options_'
MINUS
SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS 
    WHERE OBJECT_TYPE = 'TABLE';

0 ROWS

create or replace view DBA_OBJECTS_2
(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY)
as
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, '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, sys.user$ u
where o.owner# = u.user#
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'
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'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

<< removed the _default_auditing_options_ exclusion >>

SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS_2
    WHERE OBJECT_TYPE = 'TABLE'
MINUS
SELECT OWNER,TABLE_NAME FROM DBA_TABLES_2;

0 ROWS

DBA_OBJECTS VS DBA_SEGMENTS


SELECT OWNER,SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE'
    AND SEGMENT_NAME<>'_default_auditing_options_'
MINUS
SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS
    WHERE OBJECT_TYPE = 'TABLE';

0 ROWS

SELECT O.OWNER, OBJECT_NAME FROM DBA_OBJECTS O, DBA_TABLES T
    WHERE OBJECT_TYPE = 'TABLE'
    AND OBJECT_NAME = TABLE_NAME
    AND O.OWNER = T.OWNER
    AND CLUSTER_NAME IS NULL
    AND T.TEMPORARY <> 'Y'
    AND IOT_TYPE IS NULL
    AND PARTITIONED <>'YES'
MINUS
SELECT OWNER,SEGMENT_NAME FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE'
    AND SEGMENT_NAME<>'_default_auditing_options_';

0 ROWS