dba_tables 和dba_all_tables的区别 具体点 100分

dba_tables 和dba_all_tables的区别  具体点 100分

解决方案 »

  1.   

     
    dba_all_tables = dba_tables union all DBA_OBJECT_TABLES
    create or replace dba_all_tales as 
    SELECT OWNER,
           TABLE_NAME,
           TABLESPACE_NAME,
           CLUSTER_NAME,
           IOT_NAME,
           STATUS,
           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,
           NULL,
           NULL,
           NULL,
           TEMPORARY,
           SECONDARY,
           NESTED,
           BUFFER_POOL,
           ROW_MOVEMENT,
           GLOBAL_STATS,
           USER_STATS,
           DURATION,
           SKIP_CORRUPT,
           MONITORING,
           CLUSTER_OWNER,
           DEPENDENCIES,
           COMPRESSION,
           DROPPED
      FROM DBA_TABLES
    UNION ALL
    SELECT "OWNER",
           "TABLE_NAME",
           "TABLESPACE_NAME",
           "CLUSTER_NAME",
           "IOT_NAME",
           "STATUS",
           "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",
           "OBJECT_ID_TYPE",
           "TABLE_TYPE_OWNER",
           "TABLE_TYPE",
           "TEMPORARY",
           "SECONDARY",
           "NESTED",
           "BUFFER_POOL",
           "ROW_MOVEMENT",
           "GLOBAL_STATS",
           "USER_STATS",
           "DURATION",
           "SKIP_CORRUPT",
           "MONITORING",
           "CLUSTER_OWNER",
           "DEPENDENCIES",
           "COMPRESSION",
           "DROPPED"
      FROM DBA_OBJECT_TABLES
      

  2.   

    关注ing~~,找了一下，itpub曾经问过同样的问题，没有理想的答案
    自己尝试了下，发现以下：-- 表结构有一点点不一样
    SQL>desc dba_tables;SQL>desc dba_all_tables;
    -- 表记录数前者多了几十个.
    SQL> select count(*) from dba_all_tables;  COUNT(*)
    ----------
          2052SQL> select count(*) from dba_tables;  COUNT(*)
    ----------
          2013
      

  3.   

    dba_all_tables = dba_tables union all DBA_OBJECT_TABLES
    精辟！
      

  4.   

    普通登录两张表数据一样的，以DBA权限登录all比dba_tables多一些。
      

  5.   


    SQL> select * from (select table_name a from dba_tables) t1,(select table_name b from dba_all_tables) t2 where t1.a not in(t2.b);
      

  6.   

    dba_all_tables = dba_tables union all DBA_OBJECT_TABLES
    精辟！