dbms_metadata.get_ddl( )方法查询建表语句及查询结果解析

dbms_metadata.get_ddl( )方法查询建表语句及查询结果解析

当我们想要查看某个表或者是表空间的DDL的时候，可以利用dbms_metadata.get_ddl这个包来查看。
dbms_metadata包中的get_ddl函数详细参数
GET_DDL函数返回创建对象的原数据的DDL语句，详细参数如下
– object_type —需要返回原数据的DDL语句的对象类型
– name — 对象名称
– schema —对象所在的Schema，默认为当前用户所在所Schema
– version —对象原数据的版本
– model —原数据的类型默认为Oracle
– transform. - XSL-T transform. to be applied.
– RETURNS: 对象的原数据默认以CLOB类型返回

dbms_metadata包中的get_ddl函数定义
FUNCTION get_ddl ( object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
model IN VARCHAR2 DEFAULT ‘ORACLE’,
transform. IN VARCHAR2 DEFAULT ‘DDL’) RETURN CLOB;

-- 查询SEQUENCE
SELECT sequence_name,DBMS_METADATA.GET_DDL('SEQUENCE',sequence_name) FROM user_sequences;
-- 查询TABLE
SELECT table_name,DBMS_METADATA.GET_DDL('TABLE',table_name) FROM user_tables;
-- 查询TRIGGER
SELECT trigger_name,DBMS_METADATA.GET_DDL('TRIGGER',trigger_name) FROM user_triggers;
-- 查询VIEW
SELECT view_name,DBMS_METADATA.GET_DDL('VIEW',view_name) FROM user_views;
-- 得到一个用户下的所有表，索引，存储过程，函数的ddl
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)FROM USER_OBJECTS uwhere U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION');
-- 查看当前用户表的SQL
select dbms_metadata.get_ddl('TABLE','EMPLOYEES') from dual;
-- 查看其他用表或索引的SQL
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
-- 查看所需表的索引
select INDEX_NAME, INDEX_TYPE, TABLE_NAME from user_indexes WHERE table_name='EMP';
--查看当前用户索引的SQL
select dbms_metadata.get_ddl('INDEX','PK_DEPT') from dual;
-- 查看其他用户索引的SQL 
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;
-- 查看所需表的约束
select owner, table_name, constraint_name, constraint_type from user_constraints where table_name='EMP';
-- 查看创建主键的SQL
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
-- 查看创建外键的SQL
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
-- 查看当前用户视图的SQL
SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES') from dual ;
-- 查看其他用户视图的SQL
SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;
-- 查看创建视图的SQL也可以
select text from user_views where view_name=upper('&view_name');
--得到所有表空间的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)FROM DBA_TABLESPACES TS;
--得到所有创建用户的ddl
SELECT DBMS_METADATA.GET_DDL('USER',U.username)FROM DBA_USERS U;
--去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 

通过dbms_metadata.get_ddl()方法查询后，得到查询对象的DDL语句，如下的表查询语句及查询结果。

-- 查询TABLE
SELECT table_name,DBMS_METADATA.GET_DDL('TABLE',table_name) FROM user_tables;

--查询结果
CREATE TABLE "SCOTT"."DEPT"
(	"DEPTNO" NUMBER(2,0),
    "DNAME" VARCHAR2(14),
    "LOC" VARCHAR2(13),
     CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 USING INDEX 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)
 TABLESPACE "USERS"  ENABLE
)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"

查询结果解析如下：
1. PCTFREE
PCTFREE：为数据更新准备的“留白”,即为一个块保留的空间百分比。
​向一个数据库写入数据时，对数据表中的块block来说，数据会依次填满数据块，在Oracle中，向数据库写数据的时候，需要先找到一个空闲块，之后向空闲块中写入数据。

Oracle是怎样判断一个块是空闲块？
​ 答案就是 PCTFREE 参数。该参数是一个百分比值，它的默认为10%。如果一个数据块的空闲空间（可用空间）低于 PCTFREE 设定值，就认为这个数据块已经写满。会将这个块从空闲块的列表（FREELIST）上删除。

为什么要设置这个参数？或者说这个参数的意义在哪？
数据在插入后，可能会进行update操作，这个参数意义就在这。数据保存在数据行里，随着不断的更新，每行所占有的空间是一个不定的范围。在数据插入之后，可能会发生存储空间增加（比如：varchar2类型字符长度变化），这个参数的设置值（默认10%）就是为了应对这个问题，留作数据行空间延展使用。

万一数据延伸超过10%该怎么办？
​ Oracle定位数据使用的物理rowid机制，实际上就是定位特定的数据行在某个数据块的第几个slot（槽）上。如果一个数据块再也装不下一些数据行，那么这些数据行就需要另找一个新的数据块进行保存。也就意味着这些行有一个新的new-rowid位置。但是，Oracle还会按照原来的old-rowid定位数据行。原来的数据块上，记录着该数据行的新位置new-rowid，再次找到新的数据行位置。这个技术过程就称为行迁移（row migrate） 。

注意：我们要读一个数据行的内容。理论上希望访问的数据块越少越好，最好只有一个块。但是，如果发生行迁移，我们就不得不访问多个数据块才能得到数据。所以，我们要尽量避免发生行迁移的情况出现，最好的办法就是保留一个适当的PCTFREE值。
通俗说法：

​ PCTFREE 后面的值是一个百分比，当数据块小于这个百分比时可以 insert 数据，这个时候又被称为上升期，当数据块大于这个百分比时只能 update 数据。它的值是为了防止数据空间延伸超过空间块儿出现行迁移的现象而存在的。

2. PCTUSED
PCTUSED ：数据块的判定容量底线，指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

Oracle可以插入数据，也可以删除数据，当一个块中的数据删除到一定程度时，Oracle又会认为这个块是空闲块。这个程度的设定值就是PCTUSED 。

当一个数据块的使用容量低于PCTUSED设置的限制，那么就认为这个数据块已经空闲，可以放置回FREELIST列表中，作为空闲数据块接受新数据行的插入。

​ 在一般而言，40%是可以接受的，如果PCTUSED 和 PCTFREE 值设置的太过于接近，则会导致频繁的数据块空闲或者写满切换，这样有损于数据库性能。而对于一些数据变化巨大、删除频繁的系统，这个参数可以配合PCTFREE 统一筹划。

​ 11gR2默认是使用segment creation deferred建立，即新建的空表不分配segment，当insert第一条记录时分配段空间，不会因truncate而回收，并且在sys schema里不支持，exp也不会导出。

3. INITRANS 和 MAXTRANS
INITRANS：设置初始事务
MAXTRANS ：设置最大事务

​事务是数据块级别的参数。数据库事务的本质还是对数据块的修改，而事务的信息是记录在数据块头。
参数INITRANS就是表示数据块上可以标记的初始事务数目。如果同时进行的事务数据量超过这个数量，事务数目可以增加，最大能够达到MAXTRANS 的限制。
​从性能角度看，我们不希望一个数据块同时进行过多的事务。因为这样起码意味着数据块过热。所以，建议设置一个合理的INITRANS。

4.NOCOMPRESS 和COMPRESS
Compress：压缩参数
nocompress：不压缩，一般默认为 nocompress。

​Compress参数含义：在存储表数据的时候是否启用压缩选项。压缩使用的级别是数据块block级别。Oracle对数据块的压缩采用相邻相同值合并的压缩算法。

Compress参数有两个系列参数：
​（1）OMPRESS FOR DIRECT_LOAD OPERATIONS：作用于Compress相同，适合于数据仓库OLAP系统。只在直接插入过程中在表或者分区上启用压缩技术；（2）COMPRESS FOR ALL OPERATIONS：适合于OLTP系统，针对所有的操作均启用了压缩选项。要求的版本较高。

压缩表数据该如何选择？
​ 表改动很小的或者几乎不改动的情况下使用compress，节省空间，提高查询的性能，
​ 经常改变的表不要使用压缩（nocompress），不然会使表的更新和插入操作变慢。

5. LOGGING 和 NOLOGGING
LOGGING ：写入日志
NOLOGGING 表示不写入日志

Nologging对表的某些特定操作不写入redo log, 如 insert /+ APPEND/直接插入，create table as select ……
这样做可以減少日志的生成量，加快记录的插入。但平时表的insert,update,delete 還是會写入redo log的

6. STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
在storage关键字里，包括了存储上对数据对象空间分配的一些重要参数。
其中的核心内容控制了Oracle如何给这个数据表对象分配空间。
先阐明一个概念：数据表空间Tablespace空间管理的机制问题

​storage关键字里面涉及到参数取值通常来自我们使用表空间的设置。数据表逻辑上是一种段对象segment object（Data Segment）。Data Segment的空间管理是通过Tablespace表空间进行管理的。

​ 在管理空闲空间和分配空间的问题上，有字典管理方式（Dictionary Management Tablespace DMT）和本地管理方式（Locally Management Tablespace LMT）。（声明：本部分参考陈吉平老师的《Oracle——高可用环境》）
​ 在Oracle8i以前，对空闲空间是采用数据字典DML的方式进行管理。两个底层数据字典UET （ 已 经 使 用 过 的 空 间 ） 和 F E T （已经使用过的空间）和FET （已经使用过的空间）和FET（未使用的空间）。当分配空间的时候，Oracle使用一系列递归的SQL来获取空闲空间。这种结构存在一些潜藏的问题。是并行操作引起的性能瓶颈。在寻找空间空间和分配空间的时候，Oracle对两个数据字典表进行递归SQL调用和更新。在DML操作频繁、空间分配管理的环境下，数据字典进行串行化操作，容易形成性能瓶颈。配合其他参数进行空间分配，容易形成过多的空间碎片。特别是在分配大小不一致的情况下。每个Segment所使用的所有extents信息都保存在数据字典中，如果数据表很大，字典数据表中记录数目也就相对较多。那么，进行批量的删除或者数据表drop，会引起长时间的数据表更新操作。长时间的串行化更新操作进一步就会影响系统整体的空间管理分配能力。

​ 鉴于这些问题，Oracle在8i之后，推出了本地管理表空间（LMT）机制，逐渐取代DMT方法。简单的说，就是将空闲空间管理职责，由统一的数据字典管理下放到文件级别，让文件自己管理空闲空间、分配空间。
​ 在LMT机制里，Oracle将存储信息保存在文件的文件头部。在文件头上，存在一个位图形式的存储信息段，其中记录了该文件空闲空间管理信息。这样，在表空间进行对象空间分配的时候，只需要进行文件级别的资源协调，不需要访问专门的数据字典。这样也就避免了空闲资源带来的争用。

① initial参数
initial：在创建这个对象（数据表）时，分配多大的空间。也就是初始段segment大小

在我们提取出的数据表信息中，initial参数设置为65536。系统的块为8K，也就是81024=8192byte空间。initial为88K，也就是说，初始段空间分配8个数据块，也就是64KB。
虽然initial参数是DMT时代确定的参数选项，但是在LMT机制下该参数依然有效，而且是必需的。Oracle是不能确定初始段空间大小。
② next参数
next：分配下一个空间片段的大小

在LMT进行表空间管理下，NEXT参数子句是过时的。在代码例子中，next=1048576，为1MB空间。
为了消除存储中的碎片现象，目前我们都是通过设置统一大小uniform. size的extent空间。也就是每个分区的大小都是一致的。
③ Minextents和Maxextents参数
Minextents：数据对象在创建开始分配分区的个数
Maxextents：最大可分配分区的个数信息

7. PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

①FREELISTS 和 Freelist Group

​ FREELISTS和FREELIST-GROUPS参数是段的存储参数。

​ 数据库在INSERT,UPDATE操作时都需要请求空闲的数据块，由于不可能每次操作时都去每个数据块查询一下看看有没有空闲的块（如果这样那效率太低了）。为了解决这个问题，ORACLE的在每个段的段头有一个空闲数据块指针链表的结构，这个就称为FREELISTS，FREELISTS列表代表链表的长度。表面上看像解决了问题，但在高并发对一个表的插入或更新时，一个空闲链表往往会出现等待的现象，所以为了提高性能，又增加了多个空闲链表的处理，也就是FREELIST-GROUPS，也就说段有多少个空闲链表是FREELIST-GROUPS参数决定。

​ 在ORACLE9.2以前，对于一些高并发的表需要检查FREELISTS和FREELIST-GROUPS参数是否满足应用的要求。从ORACLE9.2新增加了ASSM(表空间段自动管理)的新特性，有了ASSM，链接列表FREELISTS被位图结构所取代，所以也根本不需要指定什么FREELISTS和FREELIST-GROUPS参数了，一个段里哪些数据块是空闲的，那些是可以再使用的，在这个位图里全部可以取到，并且全部是由ORACLE自动管理。
通过测试与实践，采用自动段管理的表，在并发处理性能上表现非常好，所以基本上现在的表都采用段自动管理了，因此FREELISTS和FREELIST-GROUPS参数也就成为过去。

Freelists与Freelist Group都是涉及到段对象空间分配的机制。分配给数据段的空闲空间包括两个部分，高水位线HWM以上空闲块和HWM以下空闲块。

其中，HWM以下的空闲空间是通过Freelists进行管理。简单的说，Freelists就是一个列表，列表上连接着所有管理的空闲数据块。当进行insert或者update操作的时候，数据表段segment需要额外的空闲数据块，就需要段segment进行空闲块的管理。
在这个过程中，每个段头上都有专门的freelists，进行空闲块的管理。freelists参数就表示附加在这个段对象上处理freelist的个数，也就是一个freelists group上所容纳的freelists的个数。
在案例参数中，取定freelists=1,表示该数据表中每个freelists group上包括一个freelists。该参数的最小值为1，最大值的选取与当前数据库使用数据块大小db_block_size密切相关。如果设置不合适，会在运行阶段报错。
使用freelist的时候，当系统需要空闲块保存数据是，会向freelist进行空间请求，容易成为性能的瓶颈所在。所以从9i开始，Oracle引入了位图表进行freelist的管理。

指定创建数据对象上使用的Freelist Group的数量。我们说，默认情况下，是使用一个freelist进行数据空闲块管理。一些数据表如果分配比较频繁，单个freelist可能不能满足实际的需要，这时候可以考虑使用多个freelist group来缓解空间块管理压力。

说明： 对表空间空间的管理，Oracle存在手工段管理方式下Segment Management Manual。如果设置为自动段空间管理ASSM下，freelists和freelist group两个参数是不起效果的。
②Buffer_POOL参数

Buffer_Pool：决定了该数据段对象在SGA缓冲区中的管理策略。

Oracle是不会直接对数据文件中的数据进行操作的。对数据的读写操作，都是需要对文件以数据块的形式加载在内存SGA共享区中，之后对数据块进行操作。如果是修改或者新增加操作，则由DBWn后台进行写回数据文件。
数据块在SGA区中驻留的场所就是Buffer Pool。Oracle在访问一条数据的时候，首先会在Buffer Pool中寻找，看看该块是不是已经缓存在Buffer Pool中了。如果没有，就从数据文件中获取这个数据块。长期DBA们关注的数据块命中率，也就是在Buffer Pool中发生的。
一般内存是小于数据库容量的。为了加快速度，最理想的情况是将所有的数据加载在内存中。但是这种方法还存在一些距离。所以，总会有数据块被从Buffer Pool中替换掉。目前Oracle采用LRU算法进行数据块淘汰，也就是最常用、访问最频繁的数据块会更长时间保存在缓冲区内，很少访问的数据块可能很快的被剔出缓冲区。这样做的目的也就是保证经常访问数据访问速度。
在这样的基础上，Oracle对Buffer Pool进行了进一步的划分。划分为keep、default和recycle三个子池,这样的划分目的是在业务范畴上对数据进行进一步的分治。
​ keep池的含义就是保持最长的时间。keep池中的对象，都是定义为经常使用的对象，保持最大限度的驻留时间。不会轻易被剔出Buffer Cache；
​ recycle池是为了不经常访问的数据对象块准备的。通常被加载之后，不希望长时间的保存。
default
​default是buffer pool的默认选项，是介于keep和recycle两类之间的一种对象缓存方式。
要说明的是两点：
1、三种类型池对象虽然目的定义不同，但是使用的管理算法仍然是LRU算法。区别只是在从业务上对数据对象进行划分，而这个定义是掌握在系统设计和DBA手中；
2、当我们对SGA空间进行动态管理的时候，三个缓存池空间是通过Oracle动态进行调整的。所以，我们现在已经不需要分别定义空间的大小，而只需要定义三类对象是什么就可以了。
回到buffer_pool参数，经过了上面的说明。buffer_pool的含义也就比较清楚了。Buffer_pool定义了该段对象进行缓存的策略。取值有default、keep和recycle。
TABLESPACE “EAS_D_GYCS_TEMP2” ;
–表示用EAS_D_GYCS_TEMP2表空间

CREATE TABLE "SCOTT"."DEPT"
(	"DEPTNO" NUMBER(2,0),
    "DNAME" VARCHAR2(14),
    "LOC" VARCHAR2(13),
     CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 USING INDEX 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) -- 参考下面
 TABLESPACE "USERS"  ENABLE
)SEGMENT CREATION IMMEDIATE 	-- 立即分配段空间
PCTFREE 10 						-- 低于10%时不允许insert，但可以update
PCTUSED 40 						-- 当剩余空间大于40%时，又可以insert了
INITRANS 1 						-- 初始化事务槽的个数
MAXTRANS 255 					-- 最大事务槽个数
NOCOMPRESS 						-- 不压缩
LOGGING 						-- 写入日志
STORAGE							-- 存储参数
(INITIAL 65536 					-- 分配给Segment的第一个Extent的大小，以字节为单位，这个参数不能在alter语句中改变，如果指定的值小于最小值，则按最小值创建
 NEXT 1048576 					-- 第二个Extent的大小等于NEXT的初值，以后的NEXT值=前一NEXT大小乘以（1+PCTINCREASE/100）,如果指定的值小于最小值，则按最小值创建。如果在alter语句中改变NEXT的值，则下一个分配的Extent将具有指定的大小，而不管上一次分配的Extent大小和PCTINCREASE参数值
 MINEXTENTS 1 					-- 最小区段数
 MAXEXTENTS 2147483645			-- 最大区段无限制 
 PCTINCREASE 0 					-- 指定第三个及其后的Extent相对于上一个Extent所增加的百分比，如果 PCTINCREASE为0，则Segment中所有新增加的Extent的大小都相同，等于NEXT的值，如果PCTINCREASE大于0，则每次计算NEXT的值（用上面的公式），PCTINCREASE不能为负数。创建回滚段时，不可指定此参数，回滚段中此参数固定为0
 FREELISTS 1 FREELIST GROUPS 1 	-- 段的存储参数，但是在ORACLE9.2以后由ORACLE自动管理，两个参数就成为过去
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) -- BUFFER_POOL：决定了该数据段对象在SGA缓冲区中的管理策略
  TABLESPACE "USERS"