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、从数据字典中获得指定用户下的table和index的DDL脚本

set heading off;

set echo off;

Set pages 999;

set long 90000;

spool d:ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

2、从数据字典中获得指定用户下所有table和index的DDL脚本

set pagesize 0 set long 90000

set feedback off

set echo off

spool d:scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

spool off;

摘自：Get table and index DDL the easy way

Don Burleson

http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm

SET   HEADING   OFF;  
  SET   ECHO   OFF;  
  SET   FEEDBACK   OFF;  
  SET   PAGESIZE   0;  
  SET   TRIMSPOOL   ON;  
  SPOOL   MyDDL.sql;  
  select   DBMS_LOB.substr(DBMS_METADATA.get_ddl('TABLE',u.TABLE_NAME))   FROM   USER_TABLES   U;  
  SPOOL   OFF;  
  SET   HEADING   ON;  
  SET   ECHO   ON;  
  SET   FEEDBACK   ON;  
  SET   PAGESIZE   14;  
  SET   TRIMSPOOL   OFF;