dbms_metadata.get_ddl学习

dbms_metadata.get_ddl学习(转)  2012-09-12 10:56:16|  分类： Oracle |  标签：dbms_metadata  get_ddl  oracle  建表  sql   |字号大
中
小 订阅
今天在网上看到介绍dbms_metadata.get_ddl的文章，很不错，学习下

1.显示设置：

/*创建DBMS_METADATA：
@?/rdbms/admin/catmeta.sql
*/
SETSERVEROUTPUTON
SETLINESIZE1000
SETFEEDBACKOFF
setlong999999            
SETPAGESIZE1000
/*若希望不显示storage参数：
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
*/
 
2.9i R2所支持的45个OBJECT TYPE：

TypeName                     Meaning
------------------------------ ------------------------------
AUDIT_OBJ                     auditsofschemaobjects
AUDIT                         auditsofSQLstatements
ASSOCIATION                   associatestatistics
CLUSTER                       clusters
COMMENT                       comments
CONSTRAINT                     constraints
CONTEXT                       applicationcontexts
DB_LINK                       databaselinks
DEFAULT_ROLE                   defaultroles
DIMENSION                     dimensions
DIRECTORY                     directories
FUNCTION                       storedfunctions
INDEX                         indexes
INDEXTYPE                     indextypes
JAVA_SOURCE                   Javasources
LIBRARY                       externalprocedurelibraries
MATERIALIZED_VIEW             materializedviews
MATERIALIZED_VIEW_LOG         materializedviewlogs
OBJECT_GRANT                   objectgrants
OPERATOR                       operators
OUTLINE                       storedoutlines
PACKAGE                       storedpackages
PACKAGE_SPEC                   packagespecifications
PACKAGE_BODY                   packagebodies
PROCEDURE                     storedprocedures
PROFILE                       profiles
PROXY                         proxyauthentications
REF_CONSTRAINT                 referentialconstraint
ROLE                           roles
ROLE_GRANT                     rolegrants
ROLLBACK_SEGMENT               rollbacksegments
SEQUENCE                       sequences
SYNONYM                       synonyms
SYSTEM_GRANT                   systemprivilegegrants
TABLE                         tables
TABLESPACE                     tablespaces
TABLESPACE_QUOTA               tablespacequotas
TRIGGER                       triggers
TRUSTED_DB_LINK               trustedlinks
TYPE                           user-definedtypes
TYPE_SPEC                     typespecifications
TYPE_BODY                     typebodies
USER                           users
VIEW                           views
XMLSCHEMA                     XMLschema
3.举例：

--表：(注意：表名要大写)
SQL>SELECTDBMS_METADATA.GET_DDL('TABLE','T2')FROMDUAL;
 
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
 
 CREATETABLE"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)
  )PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE"
 
 
 
--索引：
SQL>SELECTDBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')FROMDUAL;
 
DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')
--------------------------------------------------------------------------------
 
 CREATEINDEX"TEST"."IDX_OBJECT_NAME"ON"TEST"."T2"("OBJECT_NAME")
 PCTFREE10INITRANS2MAXTRANS255
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE"
 
 
 
--主键：
SQL>SELECTDBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')FROMDUAL;
 
DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')
--------------------------------------------------------------------------------
 
 ALTERTABLE"TEST"."PARENT"ADDCONSTRAINT"PK_AA"PRIMARYKEY("BB")
 USINGINDEXPCTFREE10INITRANS2MAXTRANS255
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE" ENABLE
 
 
 
 
--外键：
SQL>SELECTDBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')FROMDUAL;
 
DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')
--------------------------------------------------------------------------------
 
 ALTERTABLE"TEST"."CHILD"ADDCONSTRAINT"FK_AA"FOREIGNKEY("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

 
 
 
--用户：
select  DBMS_METADATA.GET_DDL('USER','SCOTT') from  dual ;

-------------------------------------------------------------------------------

 

set serveroutput on
set long 999999
set pagesize 0
select DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from dual;

desc  DBMS_METADAT
FUNCTION GET_DDL RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_TYPE                    VARCHAR2                IN
 NAME                           VARCHAR2                IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 VERSION                        VARCHAR2                IN     DEFAULT
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM DUAL;