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:
是否记录日志，分区表的话此列为空 | |
|
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:
表对象的默认 buffer,如果没有被缓存到buffer cache，则显示为null；分区表显示为 NULL | |
|
FLASH_CACHE |
VARCHAR2(7) |
Database Smart Flash Cache hint to be used for table blocks:（11g才有）
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:
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:
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才有）
表压缩的类型 | |
|
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才有）
结果缓存中是否表注释 | |
获取表的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视图学习
当前网址：http://shouzuofang.com/article/jidopd.html