Dbms_metadata use Session_transform to filter the DDL that you do not want to get

When we use DBMS_METADATA.GET_DDL to get the DDL of an object, we sometimes get some additional information, such as when you want to get the table's creation statement, you get the table's constraint information, which is probably not what you want, so you can use the Session_ The transform filters it.

Look at the following example, create a table with a primary key and a foreign key, and get his DDL statement:

Sql> CREATE TABLE tb1 (id int primary key);

Table created.

Sql> CREATE TABLE TB2 (ID int primary KEY references tb1 (ID));
Table created.

sql> INSERT INTO TB1 values (1);

1 row created.

Sql> commit;

Commit complete.

sql> INSERT INTO TB2 values (1);

1 row created.

Sql> commit;

Sql> Select Dbms_metadata.get_ddl (' TABLE ', ' TB2 ', ' TEST ') from dual; Dbms_metadata.

  GET_DDL (' TABLE ', ' TB2 ', ' TEST ')-------------------------------------------------------------------------------- CREATE TABLE "TEST". " TB2 "(" id "number (*,0), PRIMARY KEY (" id ") USING INDEX PCTFREE Initrans 2 Maxtrans 255 STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT FLASH_CA CHE DE FAULT cell_flash_cache DEFAULT) tablespace "USERS" ENABLE, FOREIGN KEY ("ID") REFERENCES "TEST". TB1 "(" ID ") ENABLE) SEGMENT creation IMMEDIATE PCTFREE pctused-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 DE FAULT cell_flash_cache default) tablespace "USERS"

Not only get to the creation of the basic fields of statements, as well as primary keys, constraints, foreign keys, storage parameters, table space, etc., if you do not need, can be filtered, such as I filter out the primary key, foreign keys, storage information

Use the following statement:

EXEC dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' ref_constraints ', false);
EXEC dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' CONSTRAINTS ', false);
EXEC dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' STORAGE ', false);

  

EXEC dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' ref_constraints ', false);

Pl/sql procedure successfully completed.

EXEC dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' CONSTRAINTS ', false);

Pl/sql procedure successfully completed.

Sql> exec dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' STORAGE ', false);

Pl/sql procedure successfully completed.

sql> 
sql>  Select Dbms_metadata.get_ddl (' TABLE ', ' TB2 ', ' TEST ') from dual;

Dbms_metadata. GET_DDL (' TABLE ', ' TB2 ', ' TEST ')
--------------------------------------------------------------------------- -----

  CREATE TABLE "TEST". " TB2 "
   (	" ID "number (*,0)
   ) SEGMENT creation IMMEDIATE PCTFREE-pctused-Initrans
  1 Maxtrans 255 NOC Ompress LOGGING
  tablespace "USERS"


 

Some other information can also be filtered, the form is as follows:

Table 87-22 set_transform_param:transform Parameters for the Ddltransform

Object Type	Name	Datatype	meaning
All objects	Pretty	BOOLEAN	If TRUE, format the output with indentation and line feeds. Defaults Totrue.
All objects	Sqlterminator	BOOLEAN	If TRUE, append a SQL terminator (; or/) to each DDL statement. Defaults to FALSE.
TABLE	Segment_attributes	BOOLEAN	If TRUE, include segment attributes clauses in the DDL. If FALSE, omit them. Defaults to TRUE.
TABLE	STORAGE	BOOLEAN	If TRUE, include storage clauses in the DDL. If FALSE, omit them. Defaults to TRUE. (Ignored if Segment_attributes is FALSE.)
TABLE	Tablespace	BOOLEAN	If TRUE, include tablespace clauses in the DDL. If FALSE, omit them. (Ignored if Segment_attributes is FALSE.) Defaults to TRUE.
TABLE	CONSTRAINTS	BOOLEAN	If TRUE, include the Non-referential table constraints in the DDL. If FALSE, omit them. Defaults to TRUE.
TABLE	Ref_constraints	BOOLEAN	If TRUE, include all referential constraints (foreign keys) in the DDL. Iffalse, omit them. Defaults to TRUE.
TABLE	Constraints_as_alter	BOOLEAN	If TRUE, include 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 thatconstraints be TRUE.
TABLE	Oid	BOOLEAN	If TRUE, include the OID clause for object tables in the DDL. If FALSE, omit it. Defaults to FALSE.
TABLE	Size_byte_keyword	BOOLEAN	If TRUE, include the byte keyword as part of the size specification Ofchar and VARCHAR2 columns this use BYTE semantics. If FALSE, omit the keyword. Defaults to FALSE.
TABLE, INDEX	Partitioning	BOOLEAN	If TRUE, include partitioning clauses in the DDL. If FALSE, omit them. Defaults to TRUE.
INDEX, Constraint,rollback_segment,cluster, tablespace	Segment_attributes	BOOLEAN	If TRUE, include segment attributes clauses (physical attributes, storage attributes, tablespace, logging) in the DDL. If FALSE, omit them. Defaults to TRUE.
INDEX, Constraint,rollback_segment, CLUSTER	STORAGE	BOOLEAN	If TRUE, include storage clauses in the DDL. If FALSE, omit them. (Ignored if Segment_attributes is FALSE.) Defaults to TRUE.
INDEX, Constraint,rollback_segment, CLUSTER	Tablespace	BOOLEAN	If TRUE, include tablespace clauses in the DDL. If FALSE, omit them. (Ignored if Segment_attributes is FALSE.) Defaults to TRUE.
TYPE	Specification	BOOLEAN	If TRUE, include the type specification in the DDL. If FALSE, omit it. Defaults to TRUE.
TYPE	Body	BOOLEAN	If TRUE, include the type body in the DDL. If FALSE, omit it. Defaults Totrue.
TYPE	Oid	BOOLEAN	If TRUE, include the OID clause in the DDL. If FALSE, omit it. Defaults Tofalse.
PACKAGE	Specification	BOOLEAN	If TRUE, include the package specification in the DDL. If FALSE, omit it. Defaults to TRUE.
PACKAGE	Body	BOOLEAN	If TRUE, include the package body in the DDL. If FALSE, omit it. Defaults to TRUE.
VIEW	FORCE	BOOLEAN	If TRUE, use the FORCE keyword in the CREATE VIEW statement. If FALSE, don't use the FORCE keyword in the CREATE VIEW statement. Defaults Totrue.
OUTLINE	INSERT	BOOLEAN	If TRUE, include the INSERT statements into the ol$ dictionary tables that would create the outline and its hints. If FALSE, omit a CREATE outlinestatement. 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 t o their default values. Setting This FALSE has no effect. There is no default.
All objects	INHERIT	BOOLEAN	If true, Inher Its session-level parameters. Defaults To false. If an application calls add_transform to ADD to the DDL TRANSFORM, then by default to TRANSFORM parameters th At the apply are those explicitly set for that transform handle. This has no effect if the transform handle are the session transform handle.
Role	Revoke_from	Text	The name of a user from whom the must is revoked. If this is a non-null string and if the CREATE role statement grants for you, Arevoke statement are included in the DD L after the CREATE rolestatement. Note:when you are issue a CREATE role statement and Oracle may grant for you. 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 this existing files can be reused. If FALSE, omit the reuseparameter. Defaults to FALSE.
CLUSTER, Index,rollback_segment, Table,tablespace	Pctspace	Number	A number representing the percentage by which spaces allocation for the "object type is" modified. The value is the number of one-hundreths to the current allocation. For example, 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 the other object types, INITIAL and NEXT are affected.