How to generate entire DDL of an Oracle schema (scriptable)?

Asked 2012-06-04T18:40:35.293

Can anyone tell me how I can generate the DDL for all tables, views, indexes, packages, procedures, functions, triggers, types, sequences, synonyms, grants, etc. inside an Oracle schema? Ideally, I would like to copy the rows too but that is less important.

I want to do this on a scheduled job of some kind and not manually each time, so that rules out using the wizard in SQL Developer.

Ideally, since I will be running this on several schemas that have grants and synonyms to one another, I would like to have a way to do a find/replace in the output so the schema names match whatever the names of my new schemas are going to be.

Thanks!

oracle-database schema ddl

Edit Report

2 Answers

You can spool the schema out to a file via SQL*Plus and dbms_metadata package. Then replace the schema name with another one via sed. This works for Oracle 10 and higher.

sqlplus<<EOF
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.
        --That code is not included since many database don't have Java installed.
        object_name,
        decode(object_type,
            'DATABASE LINK',           'DB_LINK',
            'JOB',                     'PROCOBJ',
            'RULE SET',                'PROCOBJ',
            'RULE',                    'PROCOBJ',
            'EVALUATION CONTEXT',      'PROCOBJ',
            'CREDENTIAL',              'PROCOBJ',
            'CHAIN',                   'PROCOBJ',
            'PROGRAM',                 'PROCOBJ',
            'SQL TRANSLATION PROFILE', 'PROCOBJ',
            'REWRITE EQUIVALENCE',     'PROCOBJ',
            'PACKAGE',                 'PACKAGE_SPEC',
            'PACKAGE BODY',            'PACKAGE_BODY',
            'TYPE',                    'TYPE_SPEC',
            'TYPE BODY',               'TYPE_BODY',
            'MATERIALIZED VIEW',       'MATERIALIZED_VIEW',
            'QUEUE',                   'AQ_QUEUE',
            'JAVA CLASS',              'JAVA_CLASS',
            'JAVA TYPE',               'JAVA_TYPE',
            'JAVA SOURCE',             'JAVA_SOURCE',
            'JAVA RESOURCE',           'JAVA_RESOURCE',
            'XML SCHEMA',              'XMLSCHEMA',
            object_type
        ) object_type
    from dba_objects 
    where owner in ('OWNER1')
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','INDEX


                        
                        
                            answered 2012-06-04T18:52:59.850


            
            
                
                    
                        
                            
                            
                        
                        5
                        
                            
                            
                        
                        
                    
                    
                        
                        There is a problem with objects such as PACKAGE_BODY:

SELECT DBMS_METADATA.get_ddl(object_Type, object_name, owner) FROM ALL_OBJECTS WHERE OWNER = 'WEBSERVICE';


ORA-31600 invalid input value PACKAGE BODY parameter OBJECT_TYPE in function GET_DDL
ORA-06512: на  "SYS.DBMS_METADATA", line 4018
ORA-06512: на  "SYS.DBMS_METADATA", line 5843
ORA-06512: на  line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.



SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type,' ','_'), object_name, owner)
  FROM all_OBJECTS 
  WHERE (OWNER = 'OWNER1');


                        
                        
                            answered 2013-06-24T12:36:23.873



        
        
            Your Answer