Oracle 獲取ddl語句

--得到所有表空間的ddl語句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

--得到所有建立使用者的ddl語句
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;

--所支援的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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1080658/，如需轉載，請註明出處，否則將追究法律責任。