oracle 自由列表,13.蛤蟆的Oracle杂记——数据字典dba_tables

13.蛤蟆的Oracle杂记——数据字典dba_tables

视图dba_tables是数据库中所有数据表的描述。

该视图包含的列属性还是非常多个，需要慢慢品味。

查看视图如下：

sys@PDB1> desc dba_tables;

Name                                                           Null?  Type

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

OWNER                                                       NOT NULL VARCHAR2(128)

TABLE_NAME                                                     NOT NULL VARCHAR2(128)

TABLESPACE_NAME                                                     VARCHAR2(30)

CLUSTER_NAME                                                            VARCHAR2(128)

IOT_NAME                                                             VARCHAR2(128)

STATUS                                                                   VARCHAR2(8)

PCT_FREE                                                               NUMBER

PCT_USED                                                              NUMBER

INI_TRANS                                                             NUMBER

MAX_TRANS                                                          NUMBER

INITIAL_EXTENT                                                 NUMBER

NEXT_EXTENT                                                                NUMBER

MIN_EXTENTS                                                                NUMBER

MAX_EXTENTS                                                               NUMBER

PCT_INCREASE                                                              NUMBER

FREELISTS                                                               NUMBER

FREELIST_GROUPS                                                        NUMBER

LOGGING                                                               VARCHAR2(3)

BACKED_UP                                                          VARCHAR2(1)

NUM_ROWS                                                          NUMBER

BLOCKS                                                                  NUMBER

EMPTY_BLOCKS                                                             NUMBER

AVG_SPACE                                                            NUMBER

CHAIN_CNT                                                           NUMBER

AVG_ROW_LEN                                                             NUMBER

AVG_SPACE_FREELIST_BLOCKS                                          NUMBER

NUM_FREELIST_BLOCKS                                                      NUMBER

DEGREE                                                                 VARCHAR2(10)

INSTANCES                                                             VARCHAR2(10)

CACHE                                                                    VARCHAR2(5)

TABLE_LOCK                                                          VARCHAR2(8)

SAMPLE_SIZE                                                                 NUMBER

LAST_ANALYZED                                                            DATE

PARTITIONED                                                                 VARCHAR2(3)

IOT_TYPE                                                               VARCHAR2(12)

TEMPORARY                                                         VARCHAR2(1)

SECONDARY                                                          VARCHAR2(1)

NESTED                                                                 VARCHAR2(3)

BUFFER_POOL                                                                VARCHAR2(7)

FLASH_CACHE                                                                VARCHAR2(7)

CELL_FLASH_CACHE                                                    VARCHAR2(7)

ROW_MOVEMENT                                                                 VARCHAR2(8)

GLOBAL_STATS                                                               VARCHAR2(3)

USER_STATS                                                           VARCHAR2(3)

DURATION                                                             VARCHAR2(15)

SKIP_CORRUPT                                                              VARCHAR2(8)

MONITORING                                                                VARCHAR2(3)

CLUSTER_OWNER                                                         VARCHAR2(128)

DEPENDENCIES                                                             VARCHAR2(8)

COMPRESSION                                                               VARCHAR2(8)

COMPRESS_FOR                                                            VARCHAR2(30)

DROPPED                                                               VARCHAR2(3)

READ_ONLY                                                           VARCHAR2(3)

SEGMENT_CREATED                                                    VARCHAR2(3)

RESULT_CACHE                                                             VARCHAR2(7)

CLUSTERING                                                         VARCHAR2(3)

ACTIVITY_TRACKING                                                   VARCHAR2(23)

DML_TIMESTAMP                                                         VARCHAR2(25)

HAS_IDENTITY                                                               VARCHAR2(3)

CONTAINER_DATA                                                       VARCHAR2(3)

INMEMORY                                                           VARCHAR2(8)

INMEMORY_PRIORITY                                                VARCHAR2(8)

INMEMORY_DISTRIBUTE                                                    VARCHAR2(15)

INMEMORY_COMPRESSION                                                        VARCHAR2(17)

INMEMORY_DUPLICATE                                                      VARCHAR2(13)

然后我们来看下其中每个列的描述如下：

OWNER：表的拥有者

TABLE_NAME：表的名字

TABLESPACE_NAME：包含表的表空间(其中NULLfor partitioned, temporary, and index-organized tables)

CLUSTER_NAME：表如果属于簇表，簇表名字

IOT_NAME：索引组织表的名字，如果存在

STATUS：表的状态(UNUSABLE或者VALID)

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：表是否CACHE在BUFFER CACHE中

TABLE_LOCK  ：表锁是否使能或者禁止

SAMPLE_SIZE         ：分析这个表的采样大小

LAST_ANALYZED    ：最近一次分析的时间

PARTITIONED         ：表是否分区

IOT_TYPE：是否是索引组织表

TEMPORARY ：表是否是临时

SECONDARY：表是否是通过ODCIIndexCreate方法创建的辅助对象

NESTED ：表是否是nested 表

BUFFER_POOL：表的bufferpool ,                 NULL for partitioned tables:

■DEFAULT

■ KEEP

■ RECYCLE

■ NULL

FLASH_CACHE：Smart Flash Cache提示用于表块

CELL_FLASH_CACHE      ：Cell flash cache提示用于表块

ROW_MOVEMENT：分区行迁移是否使能

GLOBAL_STATS：表是否作为一个整体收集静态统计信息？

USER_STATS：静态统计信息是否直接被用户输入

DURATION：如果是临时表，则表的持续时间

SKIP_CORRUPT      ：在表和索引扫描时候是否无视标记为corrupt的块

MONITORING：表是否设置了MONITORING属性

CLUSTER_OWNER          ：簇的拥有者

DEPENDENCIES：行级依赖跟踪是否开启

COMPRESSION：表是否压缩

COMPRESS_FOR：默认压缩类型

DROPPED：表是否被DROP了，然在RECYCLE中

READ_ONLY            ：表是否只读

SEGMENT_CREATED：表的段是否创建了

RESULT_CACHE：结果高速缓存模式注解表

CLUSTERING ：表是否有簇属性

ACTIVITY_TRACKING：Heat Maptracking是否开启

DML_TIMESTAMP ：修改时间，创建时间

HAS_IDENTITY：表是否有特性列

CONTAINER_DATA        ：表是否包含特定container的数据

INMEMORY：表是否在内存列存储

INMEMORY_PRIORITY ：是否优先内存列存储

INMEMORY_DISTRIBUTE：IM列存储在RAC环境中如何分布

INMEMORY_COMPRESSION                   ：IM列存储的压缩等级

INMEMORY_DUPLICATE： IM列存储的重复设置