Create DDL for all objects in a database


SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET LINESIZE 1024
SET TRIMOUT ON
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET TERMOUT OFF

COLUMN  sid NOPRINT new_value sid;
SELECT value sid from v$parameter
 where name='db_name';

SPOOL &sid._database_ddl.sql

DECLARE
   meta_data_handle  NUMBER;
   transform_handle  NUMBER;
   DDL               CLOB;
BEGIN
  meta_data_handle := DBMS_METADATA.OPEN ('DATABASE_EXPORT');
  transform_handle:= DBMS_METADATA.add_transform (meta_data_handle, 'DDL');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''DIRECTORY''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''TABLE''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''PROCEDURE''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''TRIGGER''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''VIEW''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''INDEX''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''SYNONYM''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''CONSTAINT''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''OPERATOR''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''SNAPSHOT''');
  DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''FUNCTION''');
  DBMS_METADATA.SET_TRANSFORM_PARAM (transform_handle, 'XXXXXX',true);

  LOOP
    DDL := DBMS_METADATA.fetch_clob (meta_data_handle);
    DBMS_OUTPUT.PUT_LINE (to_char(DDL));
  END LOOP;

  DBMS_METADATA.CLOSE (meta_data_handle);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_METADATA.CLOSE (meta_data_handle);
END;
/

SPOOL OFF
SET TERMOUT ON
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: