宜宾网站建设公司

dba_tables视图学习

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视图学习
URL地址：http://ybzwz.com/article/jidopd.html

其他资讯