oracle获取对象定义 dbms_metadata.get_ddl

一、 get_ddl函数定义

-- This interface is meant for casual browsing (e.g., from SQLPlus)
-- vs. the programmatic OPEN / FETCH / CLOSE interfaces above.

-- PARAMETERS:
-- object_type - The type of object to be retrieved.
-- name - Name of the object.
-- schema - Schema containing the object. Defaults to the caller's schema.
-- version - The version of the objects' metadata.
-- model - The object model for the metadata.
-- transform. - XSL-T transform. to be applied.
-- RETURNS: Metadata for the object transformed to DDL as a CLOB.

FUNCTION get_ddl ( object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform. IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;

二、 简单用法

1. 首先格式化

sqlplus适用，避免输出语句被截断

set line 200
set pagesize 0
set long 99999
set feedback off
set echo off

去除storage等多余参数

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 

2. 获取各类对象的用法

得到一个表或索引的ddl语句

SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

得到一个用户下所有表、索引、存储过程的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

得到一个用户下所有对象的ddl

SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,object_name) FROM USER_OBJECTS;

得到所有表空间的ddl语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE',tablespace_name) FROM DBA_TABLESPACES;

得到所有用户的ddl

SELECT DBMS_METADATA.GET_DDL('USER',username)  FROM DBA_USERS;

当然也可以输出到文本中

spool DEPT.sql
select dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual; 
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','SCOTT') from dual; 
select dbms_metadata.get_ddl('INDEX','IDX_NAME','SCOTT') from dual;
spool off;

spool object.sql
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u;
spool off;