oracle檢視物件DDL語句

物件型別有（ TABLE,DB_LINK,VIEW,JOB,INDEX,FUNCTION,TRIGGER,SYNONYM,PROCEDURE… ）

set line 200
set pagesize 9999
set long 99999
select dbms_metadata.get_ddl('VIEW','V_SAPVOUCHER_OLD','SAP') from dual;

值分別對應物件型別名，物件名，物件所在使用者

檢視 DBLINK 物件 DDL 語句（ DBLINK 檢視為 dba_db_links; ）

set line 200
set pagesize 9999
set long 99999
select dbms_metadata.get_ddl('DB_LINK','DB_LINK名','擁有者') from dual;

注意了 dbms_metadata.get_ddl 裡面 引數全部大寫

下面大寫部分是所有的OBJECT_TYPE

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