獲取object的建立指令碼 - DBMS_METADATA.GET_DDL

dbms_metadata包中的get_ddl函式
 

--GET_DDL: Return the metadata for a single object as 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.得到一個表或索引的ddl語句
 
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
 
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;
 
2.得到一個使用者下的所有表，索引，儲存過程的ddl
 
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
 
3.得到所有表空間的ddl語句
 
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
 
4.得到所有建立使用者的ddl
 
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;