dbms_metadata.get_ddl学习

今天在网上看到介绍dbms_metadata.get_ddl的文章，很不错，学习下

1.显示设置：

/*创建DBMS_METADATA：
@?/rdbms/admin/catmeta.sql
*/
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999              
SET PAGESIZE 1000
/*若希望不显示storage参数：
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
*/

 

2.9i R2所支持的45个OBJECT TYPE：

Type Name                       Meaning
---------------------------- -- ------------------------------
AUDIT_OBJ                       audits of schema objects
AUDIT                           audits of SQL statements
ASSOCIATION                     associate statistics
CLUSTER                         clusters
COMMENT                         comments
CONSTRAINT                       constraints
CONTEXT                         application contexts
DB_LINK                         database links
DEFAULT_ROLE                     default roles
DIMENSION                       dimensions
DIRECTORY                       directories
FUNCTION                         stored functions
INDEX                           indexes
INDEXTYPE                       indextypes
JAVA_SOURCE                     Java sources
LIBRARY                         external procedure libraries
MATERIALIZED_VIEW               materialized views
MATERIALIZED_VIEW_LOG           materialized view logs
OBJECT_GRANT                     object grants
OPERATOR                         operators
OUTLINE                         stored outlines
PACKAGE                         stored packages
PACKAGE_SPEC                     package specifications
PACKAGE_BODY                     package bodies
PROCEDURE                       stored procedures
PROFILE                         profiles
PROXY                           proxy authentications
REF_CONSTRAINT                   referential constraint
ROLE                             roles
ROLE_GRANT                       role grants
ROLLBACK_SEGMENT                 rollback segments
SEQUENCE                         sequences
SYNONYM                         synonyms
SYSTEM_GRANT                     system privilege grants
TABLE                           tables
TABLESPACE                       tablespaces
TABLESPACE_QUOTA                 tablespace quotas
TRIGGER                         triggers
TRUSTED_DB_LINK                 trusted links
TYPE                             user - defined types
TYPE_SPEC                       type specifications
TYPE_BODY                       type bodies
USER                             users
VIEW                             views
XMLSCHEMA                       XML schema

3.举例：

--表：(注意：表名要大写)
SQL > SELECT DBMS_METADATA . GET_DDL ( ' TABLE ' , ' T2 ' ) FROM DUAL ;
 
DBMS_METADATA . GET_DDL ( ' TABLE ' , ' T2 ' )
------------------------------------------------------------------------------ --
 
  CREATE TABLE " TEST " . " T2 "
   (     " OWNER " VARCHAR2 ( 30 ) ,
         " OBJECT_NAME " VARCHAR2 ( 128 ) ,
         " SUBOBJECT_NAME " VARCHAR2 ( 30 ) ,
         " OBJECT_ID " NUMBER ,
         " DATA_OBJECT_ID " NUMBER ,
         " OBJECT_TYPE " VARCHAR2 ( 18 ) ,
         " CREATED " DATE ,
         " LAST_DDL_TIME " DATE ,
         " TIMESTAMP " VARCHAR2 ( 19 ) ,
         " STATUS " VARCHAR2 ( 7 ) ,
         " TEMPORARY " VARCHAR2 ( 1 ) ,
         " GENERATED " VARCHAR2 ( 1 ) ,
         " SECONDARY " VARCHAR2 ( 1 )
   ) 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 )
  TABLESPACE " EXAMPLE "
 
 
 
--索引：
SQL > SELECT DBMS_METADATA . GET_DDL ( ' INDEX ' , ' IDX_OBJECT_NAME ' ) FROM DUAL ;
 
DBMS_METADATA . GET_DDL ( ' INDEX ' , ' IDX_OBJECT_NAME ' )
------------------------------------------------------------------------------ --
 
  CREATE INDEX " TEST " . " IDX_OBJECT_NAME " ON " TEST " . " T2 " ( " OBJECT_NAME " )
  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 )
  TABLESPACE " EXAMPLE "
 
 
 
--主键：
SQL > SELECT DBMS_METADATA . GET_DDL ( ' CONSTRAINT ' , ' PK_AA ' ) FROM DUAL ;
 
DBMS_METADATA . GET_DDL ( ' CONSTRAINT ' , ' PK_AA ' )
------------------------------------------------------------------------------ --
 
  ALTER TABLE " TEST " . " PARENT " ADD CONSTRAINT " PK_AA " PRIMARY KEY ( " BB " )
  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 )
  TABLESPACE " EXAMPLE "   ENABLE
 
 
 
 
--外键：
SQL > SELECT DBMS_METADATA . GET_DDL ( ' REF_CONSTRAINT ' , ' FK_AA ' ) FROM DUAL ;
 
DBMS_METADATA . GET_DDL ( ' REF_CONSTRAINT ' , ' FK_AA ' )
------------------------------------------------------------------------------ --
 
  ALTER TABLE " TEST " . " CHILD " ADD CONSTRAINT " FK_AA " FOREIGN KEY ( " AA " )
           REFERENCES " TEST " . " PARENT " ( " BB " ) ENABLE
 
 
 
--表空间：
SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX')
------------------------------------------------------------------------

  CREATE TABLESPACE "SYSAUX" DATAFILE
  '/u01/oracle/oradata/orcl/sysaux01.dbf

 
 
 
--用户：
DBMS_METADATA.GET_DDL('USER','SYSTEM')
-------------------------------------------------------------------------------

   ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:C27C11320D7002613C610B3F5C293AE8

 

4.综上所述：select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8183550/viewspace-667778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8183550/viewspace-667778/