oracle dbms_metadata.get_ddl实现对子句输出的控制-CSDN博客

Oracle中，为了获取object的定义语句，我们可以通过以下几种方式来实现：
1、视图定义可以通过user_views的text来查询得到；
2、存储过程、函数、包、类型等的定义可以通过dba_source视图来查询得到；
3、通过dbms_metadata.get_ddl可以得到所有object的ddl语句。
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'
从以上参数定义中可以知道，我们基本上只要使用前两个参数即可，即对象类型和对象名称。如：
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------

CREATE TABLE "LYON"."TT"
   (    "X" VARCHAR2(10),
        "Y" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "LYONTBS"

已用时间: 00: 00: 00.45

即可得到表TT的定义语句。但是可以发现结果中含有storage子句和tablespace子句，有时候我们并不需要这些子句，那该怎么办呢？
这时就可以通过：set_transform_param过程来实现输出子句的控制。
如：
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------

CREATE TABLE "LYON"."TT"
   (    "X" VARCHAR2(10),
        "Y" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "LYONTBS"

已用时间: 00: 00: 00.34
可以发现，storage子句已经没有了。同样这个也适合于表空间子句：
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------

CREATE TABLE "LYON"."TT"
   (    "X" VARCHAR2(10),
        "Y" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING

已用时间: 00: 00: 00.39
可以进一步连pctfree这些存储参数也忽略掉：
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------

CREATE TABLE "LYON"."TT"
   (    "X" VARCHAR2(10),
        "Y" NUMBER(*,0)
   )

已用时间: 00: 00: 00.39
这样就只剩下了表的直接定义语句了。如果还想在这个定义语句的末尾加个截止符（;），那可以如下：
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.00
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------

CREATE TABLE "LYON"."TT"
   (    "X" VARCHAR2(10),
        "Y" NUMBER(*,0)
   ) ;

已用时间: 00: 00: 00.39
可能会有人说，加个终止标识符还不简单，直接在语句最后拼接一下不就行了。
不过如果要在多个对象多行记录输出的事情判断就不是那么简单了。而且这个参数的不仅会在表的结尾增加一个“;”那么简单，他还会在package、procedure、type的结尾增加一个“/”符号。如：
lyon@ORCL> select dbms_metadata.get_ddl('PROCEDURE', 'SHOW_SPACE') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','SHOW_SPACE')
--------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE "LYON"."SHOW_SPACE"
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE' )
    as
       l_free_blks                 number;
       l_total_blocks              number;
       l_total_bytes               number;
       l_unused_blocks             number;
       l_unused_bytes              number;
       l_LastUsedExtFileId         number;
       l_LastUsedExtBlockId        number;
     l_LAST_USED_BLOCK           number;
       procedure p( p_label in varchar2, p_num in number )
       is
       begin
           dbms_output.put_line( rpad(p_label,40,'.') ||
                                 p_num );
       end;
   begin
       dbms_space.free_blocks
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         freelist_group_id => 0,
         free_blks         => l_free_blks );
       dbms_space.unused_space
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         total_blocks      => l_total_blocks,
         total_bytes       => l_total_bytes,
         unused_blocks     => l_unused_blocks,
         unused_bytes      => l_unused_bytes,
         LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
         LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
         LAST_USED_BLOCK => l_LAST_USED_BLOCK );

       p( 'Free Blocks', l_free_blks );
       p( 'Total Blocks', l_total_blocks );
       p( 'Total Bytes', l_total_bytes );
       p( 'Unused Blocks', l_unused_blocks );
       p( 'Unused Bytes', l_unused_bytes );
       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
       p( 'Last Used Block', l_LAST_USED_BLOCK );
end;

已用时间: 00: 00: 00.35
因此这个参数还是比较实用的。
当然，Oracle还为我们考虑了很多其他的情况，以上我只是罗列了一些最常用的参数，全部的使用可以参考下表：

Object Type	Name	Datatype	Meaning
All objects	PRETTY	BOOLEAN	If TRUE, format the output with indentation and line feeds. Defaults to TRUE.
All objects	SQLTERMINATOR	BOOLEAN	If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.
TABLE	SEGMENT_ATTRIBUTES	BOOLEAN	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
TABLE	STORAGE	BOOLEAN	If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TABLE	TABLESPACE	BOOLEAN	If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TABLE	CONSTRAINTS	BOOLEAN	If TRUE, emit all non-referential table constraints. Defaults to TRUE.
TABLE	REF_CONSTRAINTS	BOOLEAN	If TRUE, emit all referential constraints (foreign keys). Defaults to TRUE.
TABLE	CONSTRAINTS_AS_ALTER	BOOLEAN	If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.
TABLE	OID	BOOLEAN	If TRUE, emit the OID clause for object tables. Defaults to FALSE.
TABLE	SIZE_BYTE_KEYWORD	BOOLEAN	If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.
INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER, TABLESPACE	SEGMENT_ATTRIBUTES	BOOLEAN	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER	STORAGE	BOOLEAN	If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER	TABLESPACE	BOOLEAN	If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TYPE	SPECIFICATION	BOOLEAN	If TRUE, emit the type specification. Defaults to TRUE.
TYPE	BODY	BOOLEAN	If TRUE, emit the type body. Defaults to TRUE.
TYPE	OID	BOOLEAN	If TRUE, emit the OID clause. Defaults to FALSE.
PACKAGE	SPECIFICATION	BOOLEAN	If TRUE, emit the package specification. Defaults to TRUE.
PACKAGE	BODY	BOOLEAN	If TRUE, emit the package body. Defaults to TRUE.
VIEW	FORCE	BOOLEAN	If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.
OUTLINE	INSERT	BOOLEAN	If TRUE, emit the INSERT statements into the OL$ dictionary tables that will create the outline and its hints. If FALSE, emit a CREATE OUTLINE statement. Defaults to FALSE. Note: This object type is being deprecated.
All objects	DEFAULT	BOOLEAN	Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform. to their default values. Setting this FALSE has no effect. There is no default.
All objects	INHERIT	BOOLEAN	If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform. parameters that apply are those explicitly set for that transform. handle. This has no effect if the transform. handle is the session transform. handle.
ROLE	REVOKE_FROM	Text	The name of a user from whom the role must be revoked. If this is a non-null string and if the CREATE ROLE statement grants you the role, a REVOKE statement is emitted after the CREATE ROLE. Note: When you issue a CREATE ROLE statement, Oracle may grant you the role. You can use this transform. parameter to undo the grant. Defaults to null string.
TABLESPACE	REUSE	BOOLEAN	If TRUE, include the REUSE parameter for datafiles in a tablespace to indicate that existing files can be reused. Defaults to FALSE.
CLUSTER, INDEX, ROLLBACK_SEGMENT, TABLE, TABLESPACE	PCTSPACE	NUMBER	A number representing the percentage by which space allocation for the object type is to be modified. The value is the number of one-hundreths of the current allocation. For example, 100 means 100%. If the object type is TABLESPACE, the following size values are affected: - in file specifications, the value of SIZE - MINIMUM EXTENT - EXTENT MANAGEMENT LOCAL UNIFORM SIZE For other object types, INITIAL and NEXT are affected.