DBA_TABLES描述数据库中的所有关系表。它的列与ALL_TABLES中的列相同。要收集此视图的统计信息，请使用DBMS_STATS包。
ALL_TABLES描述当前用户可以访问的关系表。要收集此视图的统计信息，请使用DBMS_STATS包。
USER_TABLES描述当前用户拥有的关系表。此视图不显示所有者列。
|
|
|
|
|
|
|
|
|
Owner of the table 表的拥有者 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Name of the cluster, if any, to which the table belongs Oracle 提供了多种数据表存储结构。我们最常见的就是三种，分别为堆表（HeapTable）、索引组织表（Index Organization Table，简称为 IOT）和聚簇表（ClusterTable） |
|
|
|
|
溢出或映射表条目所属的索引组织表的名称(如果有的话)。如果IOT_TYPE列不为空，则此列包含基表名。 |
|
|
|
|
如果先前的删除表操作失败，则指示该表是不可用的(不可用的)还是有效的(有效的) |
|
|
|
|
Minimum percentage of free space in a block; NULL for partitioned tables 数据块中剩余百分比的最小值，分区表的话此列为空 |
|
|
|
|
Minimum percentage of used space in a block; NULL for partitioned tables 数据块中使用百分比的最小值，分区表的话此列为空 |
|
|
|
|
Initial number of transactions; NULL for partitioned tables 事务的初始化值，分区表的话此列为 |
|
|
|
|
Maximum number of transactions; NULL for partitioned tables 事务的最大值，分区表的话此列为空 |
|
|
|
|
Size of the initial extent (in bytes); NULL for partitioned tables 初始化 extent 大小（以字节为单位），分区表的话此列为空 |
|
|
|
|
Size of secondary extents (in bytes); NULL for partitioned tables 下一个 extent 分配大小，分区表的话此列为空 |
|
|
|
|
Minimum number of extents allowed in the segment; NULL for partitioned tables 段中分配的区中的最小值，分区表的话此列为空 |
|
|
|
|
Maximum number of extents allowed in the segment; NULL for partitioned tables 段中分配的区中的最大值，分区表的话此列为空 |
|
|
|
|
Percentage increase in extent size; NULL for partitioned tables 在 extents 中，增长的比例，分区表的话此列为空 |
|
|
|
|
Number of process freelists allocated to the segment; NULL for partitioned tables 分配到段中自由列表的数量，分区表的话此列为空 |
|
|
|
|
Number of freelist groups allocated to the segment; NULL for partitioned tables 分配到段中的自由列表组数量，分区表的话此列为空 |
|
|
|
|
是否记录日志，分区表的话此列为空 |
|
|
|
|
Indicates whether the table has been backed up since the last modification (Y) or not (N) 在上一次修改过后是否备份 |
|
|
|
|
Number of rows in the table 表的行数 |
|
|
|
|
Number of used data blocks in the table 表使用过的数据块数 |
|
|
|
|
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包收集表上的统计信息时，才会填充此列 |
|
|
|
|
Average amount of free space, in bytes, in a data block allocated to the table 分配给表的数据块中的平均可用空间量(以字节为单位) |
|
|
|
|
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 表中跨越多个块的行数量 |
|
|
|
|
Average length of a row in the table (in bytes) 表中一行的平均长度(以字节为单位) |
|
|
AVG_SPACE_FREELIST _BLOCKS |
|
Average freespace of all blocks on a freelist 自由列表中所有块的平均自由空间 |
|
|
NUM_FREELIST_BLOCKS |
|
Number of blocks on the freelist 自由列表上的块数 |
|
|
|
|
Number of threads per instance for scanning the table, or DEFAULT 每个实例有多少线程可以同时扫描表或者表的默认并行为 1 |
|
|
|
|
Number of instances across which the table is to be scanned, or DEFAULT 多少实例可以同时扫描表，默认值为1 |
|
|
|
|
Indicates whether the table is to be cached in the buffer cache (Y) or not (N) 是否是要在缓冲区高速缓存 ( Y ) or ( N ) |
|
|
|
|
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) 是否锁表 ( ENABLED ) or ( DISABLED ) |
|
|
|
|
Sample size used in analyzing this table 分析这个表所使用的样本大小 |
|
|
|
|
Date on which this table was most recently analyzed 最近一次分析表的时间 |
|
|
|
|
Indicates whether the table is partitioned (YES) or not (NO) 是否是分区表 |
|
|
|
|
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为空。 |
|
|
|
|
Indicates whether the table is temporary (Y) or not (N) 是否是临时表 |
|
|
|
|
Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) 是否是通过 ODCIIndexCreate 方法创建的辅助对象 |
|
|
|
|
Indicates whether the table is a nested table (YES) or not (NO) 是否是 nested 表 ( YES ) or ( NO |
|
|
|
|
表对象的默认 buffer,如果没有被缓存到buffer cache，则显示为null；分区表显示为 NULL |
|
|
|
|
Smart Flash Cache 提示用于表块(仅限于 Solaris and Oracle Linux) |
|
|
|
|
|
|
|
|
|
Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED) 行迁移是否开启 |
|
|
|
|
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 ) |
|
|
|
|
Indicates whether statistics were entered directly by the user (YES) or not (NO) 表示是否被用户统计信息 ( YES ) or ( NO ) |
|
|
|
|
如果是临时表，则表的持续时间:
|
|
|
|
|
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 |
|
|
|
|
Indicates whether the table has the MONITORING attribute set (YES) or not (NO) 表是否设置了 MONITORING 属性 |
|
|
|
|
Owner of the cluster, if any, to which the table belongs 簇表的拥有者 |
|
|
|
|
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) 行级依赖跟踪是否开启( ENABLED ) or ( DISABLED ) |
|
|
|
|
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables 表是否压缩 |
|
|
|
|
表压缩的类型 |
|
|
|
|
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables 表是否被 DROP 到了回收站中 |
|
|
|
|
Indicates whether the table IS READ-ONLY (YES) or not (NO) （11g才有） 表是否是只读的 |
|
|
|
|
Indicates whether the table segment is created (YES) or not (NO) （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;
