柚子快報(bào)邀請(qǐng)碼778899分享：oracle DBA

http://yzkb.51969.com/

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_TABLESDBA_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_TABLESDBA_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 ROWSDBA_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

柚子快報(bào)邀請(qǐng)碼778899分享：oracle DBA

http://yzkb.51969.com/

好文推薦