| 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才有）
结果缓存中是否表注释 |
| | | | |
首先运行 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 ,2set 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;
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
