In DBMS_METADATA, use SESSION_TRANSFORM to filter DDL and dbmsmetadata.

Source: Internet
Author: User

In DBMS_METADATA, use SESSION_TRANSFORM to filter DDL and dbmsmetadata.

When dbms_metadata.get_ddl is used to obtain the object ddl, other additional information is sometimes obtained. For example, when you want to obtain the table creation statement, you will get the table constraint information, you may not want this information, so you can use SESSION_TRANSFORM to filter it.

Take the following example to create a table with a primary key and a foreign key and obtain its 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 10 INITRANS 2 MAXTRANS 255  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"  ENABLE, FOREIGN KEY ("ID")  REFERENCES "TEST"."TB1" ("ID") 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"  

It is not just a statement for obtaining the created basic fields, but also a primary key, constraint, foreign key, storage parameter, tablespace, etc. If you do not need these fields, you can filter them, for example, if I filter out the primary key, foreign key, and 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, 'straints', false );
Exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'store', 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 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  TABLESPACE "USERS" 
You can also filter other information, as shown in the following table:

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 clses 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 all 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. ults 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 that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.

TABLE, INDEX

PARTITIONING

BOOLEAN

If TRUE, include partitioning clses 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 clses 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, do not 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 will 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 to their default values. Setting this FALSE has no effect. There is no default.

All objects

INHERIT

BOOLEAN

If TRUE, inherits session-level parameters. ults to FALSE. if an application calladd_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, arevke statement is supported in the DDL after the CREATE ROLEstatement.

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. If FALSE, omit the REUSEparameter.

Ults 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.

 



Select username from dba_users; select dbms_metapaiet_ddl ('user', 'username') from dual;

Select dbms_metadata.get_ddl ('user', U. USERNAME) from dba_users U

How Does oracle 11g allocate segments to empty tables without any records in the database?

Use expdp instead of exp for export
Insert a piece of data to these unallocated tables and delete them to allocate space.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.