dba_tables视图学习

发布时间：2020-08-10 18:11:45 作者：yhluffy
来源：ITPUB博客阅读：487

DBA_TABLES描述数据库中的所有关系表。它的列与ALL_TABLES中的列相同。要收集此视图的统计信息，请使用DBMS_STATS包。

ALL_TABLES描述当前用户可以访问的关系表。要收集此视图的统计信息，请使用DBMS_STATS包。

USER_TABLES描述当前用户拥有的关系表。此视图不显示所有者列。

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the table 表的拥有者
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the table 表名
TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized tables 指定表所属的表空间，但是通过查询可以发现有一部分 tablespace 为空的表，一般情况下要么是分区表，要么是临时表，要么是索引组织表（iot type）
CLUSTER_NAME	VARCHAR2(30)		Name of the cluster, if any, to which the table belongs Oracle 提供了多种数据表存储结构。我们最常见的就是三种，分别为堆表（HeapTable）、索引组织表（Index Organization Table，简称为 IOT）和聚簇表（ClusterTable）
IOT_NAME	VARCHAR2(30)		Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the IOT_TYPE column is not NULL, then this column contains the base table name. 溢出或映射表条目所属的索引组织表的名称(如果有的话)。如果IOT_TYPE列不为空，则此列包含基表名。
STATUS	VARCHAR2(8)		If a previous DROP TABLE operation failed, indicates whether the table is unusable ( UNUSABLE ) or valid ( VALID ) 如果先前的删除表操作失败，则指示该表是不可用的(不可用的)还是有效的(有效的)
PCT_FREE	NUMBER		Minimum percentage of free space in a block; NULL for partitioned tables 数据块中剩余百分比的最小值，分区表的话此列为空
PCT_USED	NUMBER		Minimum percentage of used space in a block; NULL for partitioned tables 数据块中使用百分比的最小值，分区表的话此列为空
INI_TRANS	NUMBER		Initial number of transactions; NULL for partitioned tables 事务的初始化值，分区表的话此列为
MAX_TRANS	NUMBER		Maximum number of transactions; NULL for partitioned tables 事务的最大值，分区表的话此列为空
INITIAL_EXTENT	NUMBER		Size of the initial extent (in bytes); NULL for partitioned tables 初始化 extent 大小（以字节为单位），分区表的话此列为空
NEXT_EXTENT	NUMBER		Size of secondary extents (in bytes); NULL for partitioned tables 下一个 extent 分配大小，分区表的话此列为空
MIN_EXTENTS	NUMBER		Minimum number of extents allowed in the segment; NULL for partitioned tables 段中分配的区中的最小值，分区表的话此列为空
MAX_EXTENTS	NUMBER		Maximum number of extents allowed in the segment; NULL for partitioned tables 段中分配的区中的最大值，分区表的话此列为空
PCT_INCREASE	NUMBER		Percentage increase in extent size; NULL for partitioned tables 在 extents 中，增长的比例，分区表的话此列为空
FREELISTS	NUMBER		Number of process freelists allocated to the segment; NULL for partitioned tables 分配到段中自由列表的数量，分区表的话此列为空
FREELIST_GROUPS	NUMBER		Number of freelist groups allocated to the segment; NULL for partitioned tables 分配到段中的自由列表组数量，分区表的话此列为空
LOGGING	VARCHAR2(3)		Indicates whether or not changes to the table are logged; NULL for partitioned tables: YES NO 是否记录日志，分区表的话此列为空
BACKED_UP	VARCHAR2(1)		Indicates whether the table has been backed up since the last modification (Y) or not (N) 在上一次修改过后是否备份
NUM_ROWS*	NUMBER		Number of rows in the table 表的行数
BLOCKS*	NUMBER		Number of used data blocks in the table 表使用过的数据块数
EMPTY_BLOCKS	NUMBER		Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the DBMS_STATS package. 表中的空块数，即没有使用的块只有在使用DBMS_STATS包收集表上的统计信息时，才会填充此列
AVG_SPACE*	NUMBER		Average amount of free space, in bytes, in a data block allocated to the table 分配给表的数据块中的平均可用空间量(以字节为单位)
CHAIN_CNT*	NUMBER		Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID 表中从一个数据块链接到另一个数据块的行数，或者已经迁移到新块的行数，需要一个链接来保存旧的ROWID 表中跨越多个块的行数量
AVG_ROW_LEN*	NUMBER		Average length of a row in the table (in bytes) 表中一行的平均长度(以字节为单位)
AVG_SPACE_FREELIST _BLOCKS	NUMBER		Average freespace of all blocks on a freelist 自由列表中所有块的平均自由空间
NUM_FREELIST_BLOCKS	NUMBER		Number of blocks on the freelist 自由列表上的块数
DEGREE	VARCHAR2(10)		Number of threads per instance for scanning the table, or DEFAULT 每个实例有多少线程可以同时扫描表或者表的默认并行为 1
INSTANCES	VARCHAR2(10)		Number of instances across which the table is to be scanned, or DEFAULT 多少实例可以同时扫描表，默认值为1
CACHE	VARCHAR2(5)		Indicates whether the table is to be cached in the buffer cache (Y) or not (N) 是否是要在缓冲区高速缓存 ( Y ) or ( N )
TABLE_LOCK	VARCHAR2(8)		Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) 是否锁表 ( ENABLED ) or ( DISABLED )
SAMPLE_SIZE	NUMBER		Sample size used in analyzing this table 分析这个表所使用的样本大小
LAST_ANALYZED	DATE		Date on which this table was most recently analyzed 最近一次分析表的时间
PARTITIONED	VARCHAR2(3)		Indicates whether the table is partitioned (YES) or not (NO) 是否是分区表
IOT_TYPE	VARCHAR2(12)		If the table is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If the table is not an index-organized table, then IOT_TYPE is NULL. 如果表是一个索引组织的表，那么IOT_TYPE是IOT、IOT_OVERFLOW或IOT_MAPPING。如果表不是索引组织的表，则IOT_TYPE为空。
TEMPORARY	VARCHAR2(1)		Indicates whether the table is temporary (Y) or not (N) 是否是临时表
SECONDARY	VARCHAR2(1)		Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) 是否是通过 ODCIIndexCreate 方法创建的辅助对象
NESTED	VARCHAR2(3)		Indicates whether the table is a nested table (YES) or not (NO) 是否是 nested 表 ( YES ) or ( NO
BUFFER_POOL	VARCHAR2(7)		Buffer pool for the table; NULL for partitioned tables: DEFAULT KEEP RECYCLE NULL 表对象的默认 buffer,如果没有被缓存到buffer cache，则显示为null；分区表显示为 NULL
FLASH_CACHE	VARCHAR2(7)		Database Smart Flash Cache hint to be used for table blocks:（11g才有） DEFAULT KEEP NONE Solaris and Oracle Linux functionality only. Smart Flash Cache 提示用于表块(仅限于 Solaris and Oracle Linux)
CELL_FLASH_CACHE	VARCHAR2(7)		Cell flash cache hint to be used for table blocks: DEFAULT KEEP NONE See Also: Oracle Exadata Storage Server Software documentation for more information Cell flash cache 提示用于表块
ROW_MOVEMENT	VARCHAR2(8)		Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED) 行迁移是否开启
GLOBAL_STATS	VARCHAR2(3)		For partitioned tables, indicates whether statistics for the table as a whole (global statistics) are accurate (YES) or whether they were not collected and have to be estimated from statistics on underlying partitions and subpartitions (NO) 对于分区表，指示整个表的统计信息(全局统计信息)是否准确(YES)，或者是否没有收集这些信息，并且必须根据底层分区和子分区的统计信息进行估计(NO) 作为一个整体（分区表）表的统计的是否准确表示是否被用户统计信息 ( YES ) or ( NO )
USER_STATS	VARCHAR2(3)		Indicates whether statistics were entered directly by the user (YES) or not (NO) 表示是否被用户统计信息 ( YES ) or ( NO )
DURATION	VARCHAR2(15)		Indicates the duration of a temporary table: SYS$SESSION - Rows are preserved for the duration of the session SYS$TRANSACTION - Rows are deleted after COMMIT Null - Permanent table 如果是临时表，则表的持续时间: • SYS$SESSION : the rows are preserved for the duration of the session • SYS$TRANSACTION : the rows are deleted after COMMIT 分区表显示为 NULL 空
SKIP_CORRUPT	VARCHAR2(8)		Indicates whether Oracle Database ignores blocks marked corrupt during table and index scans (ENABLED) or raises an error (DISABLED). To enable this feature, run the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure. 在表和索引扫描时候是否无视标记为 corrupt的块. 如果要起用，则执行 DBMS_REPAIR . SKIP_CORRUPT_BLOCKS
MONITORING	VARCHAR2(3)		Indicates whether the table has the MONITORING attribute set (YES) or not (NO) 表是否设置了 MONITORING 属性
CLUSTER_OWNER	VARCHAR2(30)		Owner of the cluster, if any, to which the table belongs 簇表的拥有者
DEPENDENCIES	VARCHAR2(8)		Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) 行级依赖跟踪是否开启( ENABLED ) or ( DISABLED )
COMPRESSION	VARCHAR2(8)		Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables 表是否压缩
COMPRESS_FOR	VARCHAR2(12)		Default compression for what kind of operations:（11g才有） BASIC OLTP QUERY LOW QUERY HIGH ARCHIVE LOW ARCHIVE HIGH NULL 表压缩的类型
DROPPED	VARCHAR2(3)		Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables 表是否被 DROP 到了回收站中
READ_ONLY	VARCHAR2(3)		Indicates whether the table IS READ-ONLY (YES) or not (NO) （11g才有）表是否是只读的
SEGMENT_CREATED	VARCHAR2(3)		Indicates whether the table segment is created (YES) or not (NO) （11g才有）表的段是否创建
RESULT_CACHE	VARCHAR2(7)		Result cache mode annotation for the table: （11g才有） DEFAULT - Table has not been annotated FORCE MANUAL 结果缓存中是否表注释

获取表的DDL的方法：

1）利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句

2）imp.indexfile 和 impdp.sqlfile

1）利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句

参考: How To Obtain Table DDL Without Using DBMS_METADATA.GET_DDL(文档 ID 1922301.1)

首先运行
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1
输入schema的名字
然后执行：
define CR=chr(10)
define TAB=chr(9)
col x noprint
col y noprint
SELECT TABLE_NAME Y
      ,0 X
      ,'CREATE TABLE ' || RTRIM(TABLE_NAME) || '('
FROM   DBA_TABLES
WHERE  OWNER = UPPER('&schema')
UNION
SELECT TC.TABLE_NAME Y
      ,COLUMN_ID X
      ,DECODE(COLUMN_ID, 1, ' ', ' ,') || RTRIM(COLUMN_NAME) || &TAB || &TAB ||
       RTRIM(DATA_TYPE) ||
       RTRIM(DECODE(DATA_TYPE, 'DATE', NULL, 'LONG', NULL, 'NUMBER',
                    DECODE(TO_CHAR(DATA_PRECISION), NULL, NULL, '('), '(')) ||
       RTRIM(DECODE(DATA_TYPE, 'DATE', NULL, 'CHAR', DATA_LENGTH,
                    'VARCHAR2', DATA_LENGTH, 'NUMBER',
                    DECODE(TO_CHAR(DATA_PRECISION), NULL, NULL,
                            TO_CHAR(DATA_PRECISION) || ',' ||
                             TO_CHAR(DATA_SCALE)), 'LONG', NULL,
                    '******ERROR')) ||
       RTRIM(DECODE(DATA_TYPE, 'DATE', NULL, 'LONG', NULL, 'NUMBER',
                    DECODE(TO_CHAR(DATA_PRECISION), NULL, NULL, ')'), ')')) || &TAB || &TAB ||
       RTRIM(DECODE(NULLABLE, 'N', 'NOT NULL', NULL))
FROM   DBA_TAB_COLUMNS TC
      ,DBA_OBJECTS     O
WHERE  O.OWNER = TC.OWNER
       AND O.OBJECT_NAME = TC.TABLE_NAME
       AND O.OBJECT_TYPE = 'TABLE'
       AND O.OWNER = UPPER('&schema')
UNION
SELECT TABLE_NAME Y
      ,999999 X
      ,')' || &CR || ' STORAGE(' || &CR || ' INITIAL ' || INITIAL_EXTENT || &CR ||
       ' NEXT ' || NEXT_EXTENT || &CR || ' MINEXTENTS ' || MIN_EXTENTS || &CR ||
       ' MAXEXTENTS ' || MAX_EXTENTS || &CR || ' PCTINCREASE ' ||
       PCT_INCREASE || ')' || &CR || ' INITRANS ' || INI_TRANS || &CR ||
       ' MAXTRANS ' || MAX_TRANS || &CR || ' PCTFREE ' || PCT_FREE || &CR ||
       ' PCTUSED ' || PCT_USED || &CR || ' PARALLEL (DEGREE ' ||
       RTRIM(DEGREE) || ') ' || &CR || ' TABLESPACE ' ||
       RTRIM(TABLESPACE_NAME) || &CR || '/' || &CR || &CR
FROM   DBA_TABLES
WHERE  OWNER = UPPER('&schema')
ORDER  BY 1
         ,2

或者

set pagesize 0
set long 90000
set feedback off
set echo off
spool table_ddl.sql
select dbms_metadata.get_ddl('TABLE','tablename','username') from dual;
select dbms_metadata.get_ddl('VIEW','viewname','username') from dual;
select dbms_metadata.get_ddl('INDEX','indexname','username') from dual;
spool off;
例如：
set pagesize 0
set long 90000
set feedback off
set echo off
spool table_ddl.sql
select dbms_metadata.get_ddl('TABLE','DEMO2','DEMO') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO') from dual;
spool off;
[oracle@oracle11g ~]$ cat table_ddl.sql
SQL> select dbms_metadata.get_ddl('TABLE','DEMO2','DEMO') from dual;
                                                                                                                                    
  CREATE TABLE "DEMO"."DEMO2"                                                                                                       
   (    "OWNER" VARCHAR2(30),                                                                                                          
        "OBJECT_NAME" VARCHAR2(128),                                                                                                       
        "SUBOBJECT_NAME" VARCHAR2(30),                                                                                                     
        "OBJECT_ID" NUMBER,                                                                                                                
        "DATA_OBJECT_ID" NUMBER,                                                                                                           
        "OBJECT_TYPE" VARCHAR2(19),                                                                                                        
        "CREATED" DATE,                                                                                                                    
        "LAST_DDL_TIME" DATE,                                                                                                              
        "TIMESTAMP" VARCHAR2(19),                                                                                                          
        "STATUS" VARCHAR2(7),                                                                                                              
        "TEMPORARY" VARCHAR2(1),                                                                                                           
        "GENERATED" VARCHAR2(1),                                                                                                           
        "SECONDARY" VARCHAR2(1),                                                                                                           
        "NAMESPACE" NUMBER,                                                                                                                
        "EDITION_NAME" VARCHAR2(30)                                                                                                        
   ) SEGMENT CREATION IMMEDIATE                                                                                                     
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                                                                     
NOCOMPRESS LOGGING                                                                                                                 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645                                                             
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                                                                       
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                                 
  TABLESPACE "USERS"                                                                                                                
                                                                                                                                    
SQL> select dbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO') from dual;
                                                                                                                                    
  CREATE INDEX "DEMO"."IDX_ID_DEMO2" ON "DEMO"."DEMO2" ("OBJECT_ID")                                                                
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                                                                             
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645                                                             
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1                                                                                       
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)                                                                 
  TABLESPACE "USERS"                                                                                                                
                                                                                                                                    
SQL> spool off;

2）imp.indexfile 和 impdp.sqlfile

#示例：
1.indexfile
1）先导出用户的数据
[oracle@oracle11g ~]$  exp demo/demo file=test.dmp owner=demo log=test.log;
2)从 dump 文件获取这些 DDL 语句
[oracle@oracle11g ~]$  imp demo/demo file=test.dmp fromuser=demo touser=demo indexfile=test.sql;

2.sqlfile

导出用户数据
[oracle@oracle11g ~]$  expdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp schemas=demo;
获取 DDL 语句
[oracle@oracle11g ~]$  impdp demo/demo directory=DATA_PUMP_DIR dumpfile=sqlfile.dmp sqlfile=demo.sql;

dba_tables视图学习

相关阅读