oracle视图ddl,ORACLE如何使用DBMS_METADATA.GET_DDL获取表，表空间，用户，视图等的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

--用户：

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;