浅析dba_tables,dba_object_tables,dba_all_tables视图的关系

什么是对象表？什么是关联表？

1.A relational table, which is the basic structure to hold user data.

2.An object table, which is a table that uses an object type for a column definition. An object table is explicitly defined to hold object instances of a particular type.

DBA_ALL_TABLES

describes all object tables and relational tables in the database.所有对象表和关系表

SQL> set long 4000;

SQL> select dbms_metadata.get_ddl('VIEW','DBA_ALL_TABLES','SYS') from dual;

DBMS_METADATA.GET_DDL('VIEW','DBA_ALL_TABLES','SYS')

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_ALL_TABLES" ("OWNER", "TABLE_NAME", "T

ABLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS", "PCT_FREE", "PCT_USED", "

INI_TRANS", "MAX_TRANS", "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX_EX

TENTS", "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", "OBJ

ECT_ID_TYPE", "TABLE_TYPE_OWNER", "TABLE_TYPE", "TEMPORARY", "SECONDARY", "NESTE

D", "BUFFER_POOL", "FLASH_CACHE", "CELL_FLASH_CACHE", "ROW_MOVEMENT", "GLOBAL_ST

ATS", "USER_STATS", "DURATION", "SKIP_CORRUPT", "MONITORING", "CLUSTER_OWNER", "

DBMS_METADATA.GET_DDL('VIEW','DBA_ALL_TABLES','SYS')

--------------------------------------------------------------------------------

DEPENDENCIES", "COMPRESSION", "COMPRESS_FOR", "DROPPED", "SEGMENT_CREATED") 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,

DBMS_METADATA.GET_DDL('VIEW','DBA_ALL_TABLES','SYS')

--------------------------------------------------------------------------------

     SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,

     IOT_TYPE, NULL, NULL, NULL, TEMPORARY, SECONDARY, NESTED,

     BUFFER_POOL, FLASH_CACHE,

     CELL_FLASH_CACHE, ROW_MOVEMENT,

     GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING,

     CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED,

     SEGMENT_CREATED

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","M

DBMS_METADATA.GET_DDL('VIEW','DBA_ALL_TABLES','SYS')

--------------------------------------------------------------------------------

IN_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","CA

CHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","OBJECT

_ID_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","SECONDARY","NESTED","BUFF

ER_POOL","FLASH_CACHE","CELL_FLASH_CACHE","ROW_MOVEMENT","GLOBAL_STATS","USER_ST

ATS","DURATION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMP

RESSION","COMPRESS_FOR","DROPPED","SEGMENT_CREATED" from dba_object_tables

可以确定DBA_TABLES是DBA_ALL_TABLES的子集

DBA_OBJECT_TABLES

describes all object tables in the database.描述了所有对象表

再次查询了DBA_OBJECT_TABLES, 定义就很复杂了，不过可以确定的是这个视图是从底层表查数据的。

其实看到这里可以发现，DBA_TABLES其实可以看成DBA_RELATIONAL_TABLES,这样更好理解DBA_TABLES,DBA_OBJECT_TABLES,DBA_ALL_TABLES的关系。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2141104/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2141104/