dba_tables视图学习
Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized tables指定表所属的表空间，但是通过查询可以发现有一部分 tablespace 为空的表，一般情况下要么是分区表，要么是临时表，要么是索引组织表（iottype）CLUSTER_NAMEVARCHAR2(30)
Name of the cluster, if any, to which the table belongsOracle 提供了多种数据表存储结构。我们最常见的就是三种，分别为堆表（HeapTable）、索引组织表（Index Organization Table，简称为 IOT）和聚簇表（ClusterTable）IOT_NAMEVARCHAR2(30)
Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If theIOT_TYPEcolumn is not NULL, then this column contains the base table name.溢出或映射表条目所属的索引组织表的名称(如果有的话)。如果IOT_TYPE列不为空，则此列包含基表名。STATUSVARCHAR2(8)
If a previousDROP TABLEoperation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID)如果先前的删除表操作失败，则指示该表是不可用的(不可用的)还是有效的(有效的)PCT_FREENUMBER
Minimum percentage of free space in a block; NULL for partitioned tables数据块中剩余百分比的最小值，分区表的话此列为空PCT_USEDNUMBER
Minimum percentage of used space in a block; NULL for partitioned tables数据块中使用百分比的最小值，分区表的话此列为空INI_TRANSNUMBER
Initial number of transactions; NULL for partitioned tables事务的初始化值，分区表的话此列为MAX_TRANSNUMBER
Maximum number of transactions; NULL for partitioned tables事务的最大值，分区表的话此列为空INITIAL_EXTENTNUMBER
Size of the initial extent (in bytes); NULL for partitioned tables初始化 extent 大小（以字节为单位），分区表的话此列为空NEXT_EXTENTNUMBER
Size of secondary extents (in bytes); NULL for partitioned tables下一个 extent 分配大小，分区表的话此列为空MIN_EXTENTSNUMBER
Minimum number of extents allowed in the segment; NULL for partitioned tables段中分配的区中的最小值，分区表的话此列为空MAX_EXTENTSNUMBER
Maximum number of extents allowed in the segment; NULL for partitioned tables段中分配的区中的最大值，分区表的话此列为空PCT_INCREASENUMBER
Percentage increase in extent size; NULL for partitioned tables在 extents 中，增长的比例，分区表的话此列为空FREELISTSNUMBER
Number of process freelists allocated to the segment; NULL for partitioned tables分配到段中自由列表的数量，分区表的话此列为空FREELIST_GROUPSNUMBER
Number of freelist groups allocated to the segment; NULL for partitioned tables分配到段中的自由列表组数量，分区表的话此列为空LOGGINGVARCHAR2(3)
Indicates whether or not changes to the table are logged; NULL for partitioned tables:YESNO是否记录日志，分区表的话此列为空BACKED_UPVARCHAR2(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_BLOCKSNUMBER
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 _BLOCKSNUMBER
Average freespace of all blocks on a freelist自由列表中所有块的平均自由空间NUM_FREELIST_BLOCKSNUMBER
Number of blocks on the freelist自由列表上的块数DEGREEVARCHAR2(10)
Number of threads per instance for scanning the table, or DEFAULT每个实例有多少线程可以同时扫描表或者表的默认并行为 1INSTANCESVARCHAR2(10)
Number of instances across which the table is to be scanned, or DEFAULT多少实例可以同时扫描表，默认值为1CACHEVARCHAR2(5)
Indicates whether the table is to be cached in the buffer cache (Y) or not (N)是否是要在缓冲区高速缓存 ( Y ) or ( N )TABLE_LOCKVARCHAR2(8)
Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED)是否锁表 ( ENABLED ) or ( DISABLED )SAMPLE_SIZENUMBER
Sample size used in analyzing this table分析这个表所使用的样本大小LAST_ANALYZEDDATE
Date on which this table was most recently analyzed最近一次分析表的时间PARTITIONEDVARCHAR2(3)
Indicates whether the table is partitioned (YES) or not (NO)是否是分区表IOT_TYPEVARCHAR2(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为空。TEMPORARYVARCHAR2(1)
Indicates whether the table is temporary (Y) or not (N)是否是临时表SECONDARYVARCHAR2(1)
Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N)是否是通过 ODCIIndexCreate 方法创建的辅助对象NESTEDVARCHAR2(3)
Indicates whether the table is a nested table (YES) or not (NO)是否是 nested 表 ( YES ) or ( NOBUFFER_POOLVARCHAR2(7)
Buffer pool for the table; NULL for partitioned tables:DEFAULTKEEPRECYCLENULL表对象的默认 buffer,如果没有被缓存到buffer cache，则显示为null；分区表显示为 NULLFLASH_CACHEVARCHAR2(7)
Database Smart Flash Cache hint to be used for table blocks:（11g才有）DEFAULTKEEPNONESolaris and Oracle Linux functionality only.Smart Flash Cache 提示用于表块(仅限于 Solaris and Oracle Linux)CELL_FLASH_CACHEVARCHAR2(7)
Cell flash cache hint to be used for table blocks:DEFAULTKEEPNONESee Also:Oracle Exadata Storage Server Software documentation for more informationCell flash cache 提示用于表块ROW_MOVEMENTVARCHAR2(8)
Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED)行迁移是否开启GLOBAL_STATSVARCHAR2(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_STATSVARCHAR2(3)
Indicates whether statistics were entered directly by the user (YES) or not (NO)表示是否被用户统计信息 ( YES ) or ( NO )DURATIONVARCHAR2(15)
Indicates the duration of a temporary table:SYS$SESSION- Rows are preserved for the duration of the sessionSYS$TRANSACTION- Rows are deleted afterCOMMITNull - Permanent table如果是临时表，则表的持续时间:• SYS$SESSION : the rows are preservedfor the duration of the session• SYS$TRANSACTION : the rows aredeleted after COMMIT分区表显示为 NULL 空SKIP_CORRUPTVARCHAR2(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_BLOCKSMONITORINGVARCHAR2(3)
Indicates whether the table has the MONITORING attribute set (YES) or not (NO)表是否设置了 MONITORING 属性CLUSTER_OWNERVARCHAR2(30)
Owner of the cluster, if any, to which the table belongs簇表的拥有者DEPENDENCIESVARCHAR2(8)
Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED)行级依赖跟踪是否开启( ENABLED ) or ( DISABLED )COMPRESSIONVARCHAR2(8)
Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables表是否压缩COMPRESS_FORVARCHAR2(12)
Default compression for what kind of operations:（11g才有）BASICOLTPQUERY LOWQUERY HIGHARCHIVE LOWARCHIVE HIGHNULL表压缩的类型DROPPEDVARCHAR2(3)
Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables表是否被 DROP 到了回收站中READ_ONLYVARCHAR2(3)
Indicates whether the table IS READ-ONLY (YES) or not (NO) （11g才有）表是否是只读的SEGMENT_CREATEDVARCHAR2(3)
Indicates whether the table segment is created (YES) or not (NO) （11g才有）表的段是否创建RESULT_CACHEVARCHAR2(7)
Result cache mode annotation for the table: （11g才有）DEFAULT- Table has not been annotatedFORCEMANUAL结果缓存中是否表注释
获取表的DDL的方法：1）利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句2）imp.indexfile 和 impdp.sqlfile1）利用 DBMS_METADATA.GET_DDL，获取表的 DDL 语句参考: How To Obtain Table DDL Without Using DBMS_METADATA.GET_DDL(文档 ID 1922301.1)
首先运行setechooffsetheadingoffsetfeedbackoffsetverifyoffsetpagesize0setlinesize132defineschema=&1输入schema的名字然后执行：defineCR=chr(10)defineTAB=chr(9)colxnoprintcolynoprintSELECTTABLE_NAMEY,0X,'CREATETABLE'||RTRIM(TABLE_NAME)||'('FROMDBA_TABLESWHEREOWNER=UPPER('&schema')UNIONSELECTTC.TABLE_NAMEY,COLUMN_IDX,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','NOTNULL',NULL))FROMDBA_TAB_COLUMNSTC,DBA_OBJECTSOWHEREO.OWNER=TC.OWNERANDO.OBJECT_NAME=TC.TABLE_NAMEANDO.OBJECT_TYPE='TABLE'ANDO.OWNER=UPPER('&schema')UNIONSELECTTABLE_NAMEY,999999X,')'||&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||&CRFROMDBA_TABLESWHEREOWNER=UPPER('&schema')ORDERBY1,2或者
setpagesize0setlong90000setfeedbackoffsetechooffspooltable_ddl.sqlselectdbms_metadata.get_ddl('TABLE','tablename','username')fromdual;selectdbms_metadata.get_ddl('VIEW','viewname','username')fromdual;selectdbms_metadata.get_ddl('INDEX','indexname','username')fromdual;spooloff;例如：setpagesize0setlong90000setfeedbackoffsetechooffspooltable_ddl.sqlselectdbms_metadata.get_ddl('TABLE','DEMO2','DEMO')fromdual;selectdbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO')fromdual;spooloff;[oracle@oracle11g~]$cattable_ddl.sqlSQL>selectdbms_metadata.get_ddl('TABLE','DEMO2','DEMO')fromdual;CREATETABLE"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))SEGMENTCREATIONIMMEDIATEPCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGINGSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"SQL>selectdbms_metadata.get_ddl('INDEX','IDX_ID_DEMO2','DEMO')fromdual;CREATEINDEX"DEMO"."IDX_ID_DEMO2"ON"DEMO"."DEMO2"("OBJECT_ID")PCTFREE10INITRANS2MAXTRANS255COMPUTESTATISTICSSTORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)TABLESPACE"USERS"SQL>spooloff;2）imp.indexfile 和 impdp.sqlfile
#示例：1.indexfile1）先导出用户的数据[oracle@oracle11g~]$expdemo/demofile=test.dmpowner=demolog=test.log;2)从dump文件获取这些DDL语句[oracle@oracle11g~]$impdemo/demofile=test.dmpfromuser=demotouser=demoindexfile=test.sql;
2.sqlfile
导出用户数据[oracle@oracle11g~]$expdpdemo/demodirectory=DATA_PUMP_DIRdumpfile=sqlfile.dmpschemas=demo;获取DDL语句[oracle@oracle11g~]$impdpdemo/demodirectory=DATA_PUMP_DIRdumpfile=sqlfile.dmpsqlfile=demo.sql;
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
