Dumping a complete Oracle 11g database schema to a set of SQL creation statements from a script

Question

I need to dump the complete schema (ddl only, no data) of an Oracle database to a text file or a set of text files in order to be able to systematically track revisions to the database schema using standard VCS tools like git.

Using my favorite RDBMS, postgresql, this is an almost trivially easy task, using pg_dump --schema-only.

However, dumping an Oracle DB schema to an SQL file has proved to be a maddeningly difficult task with Oracle 11g. I'm interested to know about approaches that others have figured out.

Data pump export (no ☹)

Unfortunately, I cannot use the data pump export tools introduced in Oracle 10g, because these require DBA-level access and I cannot easily obtain this level of access for most of my clients' databases.

SQL developer

I've used Oracle's SQL developer GUI and it mostly does what I want with the "Separate files" setting:

• Emits a syntactically correct SQL file to create each database object
• Emits a summary SQLs file which includes each of the individual-object files in the correct order

However there are several major issues with it:

• It's a GUI only; no way to script this behavior from the command line as far as I can tell
• Running as an unprivileged user, it can only emit the DDL for that user's owned objects (even when that user has been granted privileges to view other users' objects ... @#$(*&!!)
• It's extremely slow, taking around 20 minutes to output about 1 MB of DDL

exp and imp

Oracle's older exp command-line tool does not require DBA access. It can export the complete DDL for a database (with DBA access), or just the DDL for an individual user's owned objects.

Unfortunately, it is even slower than SQL developer (takes >1 hour for the same database even with a few performance tweaks.

However, the worst thing about exp is that it does not emit SQL, but rather a proprietary binary-format dump file (e.g. expdat.dmp).

The corresponding imp tool can "translate" these dump files into severely mangled SQL which does not contain syntactically correct end-of-statement delimiters.

Example of the horrible mangled SQL that imp show=y emits; notice the crazy line wrapping and lack of semicolons at the end of some but not all statements.

Export file created by EXPORT:V11.02.00 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing FPSADMIN's objects into FPSADMIN
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'*******', inst_scn=>'371301226');"
 "COMMIT; END;"
 "CREATE TYPE "CLOBSTRINGAGGTYPE" TIMESTAMP '2015-06-01:13:37:41' OID '367CDD"
 "7E59D14CF496B27D1B19ABF051'           "
 "AS OBJECT"
 "("
 "     theString CLOB,"
 "     STATIC FUNCTION"
 "          ODCIAggregateInitialize(sctx IN OUT CLOBSTRINGAGGTYPE )"
 "          RETURN NUMBER,"
 "     MEMBER FUNCTION"
 "          ODCIAggregateIterate(self IN OUT CLOBSTRINGAGGTYPE, VALUE IN VARC"
 "HAR2 )"
 "          RETURN NUMBER,"
 "     MEMBER FUNCTION"
 "          ODCIAggregateTerminate(self IN CLOBSTRINGAGGTYPE, returnValue OUT"
 " CLOB, flags IN NUMBER)"
 "          RETURN NUMBER,"
 "     MEMBER FUNCTION"
 "          ODCIAggregateMerge(self IN OUT CLOBSTRINGAGGTYPE, ctx2 IN CLOBSTR"
 "INGAGGTYPE)"
 "          RETURN NUMBER"
 ");"
 "GRANT EXECUTE ON "CLOBSTRINGAGGTYPE" TO PUBLIC"
 "GRANT DEBUG ON "CLOBSTRINGAGGTYPE" TO PUBLIC"
 "CREATE OR REPLACE TYPE BODY          CLOBSTRINGAGGTYPE"

I have written a Python script to demangle the output of imp show=y, but it cannot reliably demangle the output because it doesn't understand the complete Oracle SQL syntax.

dbms_metadata

Oracle has a dbms_metadata package which supports introspection of the database contents.

It's relatively easy to write an SQL statement which will retrieve the DDL for some but not all database objects. For example, the following statement will retrieve CREATE TABLE statements, but won't retrieve the corresponding privilege GRANTs on those tables.

select sub.*, dbms_metadata.get_ddl(sub.object_type, sub.object_name, sub.owner) sql
from (
    select
        created,
        owner,
        object_name,
        decode(object_type,
            'PACKAGE',      'PACKAGE_SPEC',
            'PACKAGE BODY', 'PACKAGE_BODY',
            'TYPE BODY', 'TYPE_BODY',
            object_type
        ) object_type
    from all_objects 
    where owner = :un
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','LOB','LOB PARTITION','TABLE PARTITION','DATABASE LINK')
        --Ignore system-generated types that support collection processing.
        and not (object_type like 'TYPE' and object_name like 'SYS_PLSQL_%')
) sub

Attempting to fetch the complete set of objects quickly leads down a very complex rabbit hole. (See "Reverse engineering object DDL and finding object dependencies" for more gory details.)

What else?

Any advice? I'm at a total loss for a sane and maintainable way to perform this seemingly indispensable database programming task.

Answer 1

Combine DBMS_DATAPUMP, Oracle Copy (OCP), and a simple shell script to create a one-click solution.

Sample Schema to Export

--Create test user.
drop user test_user cascade;
create user test_user identified by test_user;
create table test_user.table1(a number);
create view test_user.view1 as select 1 a from dual;
create or replace procedure test_user.procedure1 is begin null; end;
/

Create Directory and Procedure

Run these as steps as SYS. The definer's rights procedure runs as SYS. This way no roles or privileges need to be granted to any users.

--Create directory that will contain SQL file.
create directory ddl_directory as 'C:\temp';
grant read on directory ddl_directory to jheller;

--Create procedure that can only export one hard-coded schema.
--This is based on René Nyffenegger's solution here:
--dba.stackexchange.com/questions/91149/how-to-generate-an-sql-file-with-dbms-datapump
create or replace procedure sys.generate_ddl authid definer is

    procedure create_export_file is
      datapump_job number;
      job_state    varchar2(20);
    begin
        datapump_job := dbms_datapump.open(
            operation    => 'EXPORT',
            job_mode     => 'SCHEMA',
            remote_link  =>  null,
            job_name     => 'Export dump file',
            version      => 'LATEST');

        dbms_output.put_line('datapump_job: ' || datapump_job);

        dbms_datapump.add_file(
            handle    =>  datapump_job,
            filename  => 'export.dmp',
            directory => 'DDL_DIRECTORY',
            filetype  =>  dbms_datapump.ku$_file_type_dump_file);

        dbms_datapump.metadata_filter(
            handle    =>  datapump_job,
            name      =>  'SCHEMA_LIST',
            value     =>  '''TEST_USER''');

        dbms_datapump.start_job(
            handle       => datapump_job,
            skip_current => 0,
            abort_step   => 0);

        dbms_datapump.wait_for_job(datapump_job, job_state);

        dbms_output.put_line('Job state: ' || job_state);

        dbms_datapump.detach(datapump_job);
    end create_export_file;


    procedure create_sql_file is
        datapump_job number;
        job_state    varchar2(20);
    begin
        datapump_job := dbms_datapump.open(
            operation    => 'SQL_FILE',
            job_mode     => 'SCHEMA',
            remote_link  =>  null,
            job_name     => 'Export SQL file',
            version      => 'LATEST');

        dbms_output.put_line('datapump_job: ' || datapump_job);

        dbms_datapump.add_file(
            handle    =>  datapump_job,
            filename  => 'export.dmp',
            directory => 'DDL_DIRECTORY',
            filetype  =>  dbms_datapump.ku$_file_type_dump_file);

        dbms_datapump.add_file(
            handle    =>  datapump_job,
            filename  => 'schema.sql',
            directory => 'DDL_DIRECTORY',
            filetype  =>  dbms_datapump.ku$_file_type_sql_file);

        dbms_datapump.start_job(
            handle       => datapump_job,
            skip_current => 0,
            abort_step   => 0);

        dbms_datapump.wait_for_job(datapump_job, job_state);

        dbms_output.put_line('Job state: ' || job_state);

        dbms_datapump.detach(datapump_job);
    end create_sql_file;

begin
    create_export_file;
    create_sql_file;
end;
/

--Grant to users.
grant execute on generate_ddl to jheller;

Setup OCP on the Client

Files on an Oracle directory can be easily transferred to a client PC using OCP as described in this answer. The setup is a bit tricky - download the precise version of the program and the instant client and unzip them into the same directory. I think I also had some problems with a VC++ redistributable or something the first time.

Commands to Run

Now the easy part - creating and moving the files is done in two simple steps:

execute sys.generate_ddl;

C:\Users\jonearles\Downloads\ocp-0.1-win32>ocp jheller/jheller@orcl12 DDL_DIRECTORY:schema.sql schema.sql

Sample Output

This script contains a lot of weird things. Some weird extra commands that nobody will understand, and some weird options that nobody will understand. That's probably one of the reasons this seemingly obvious feature is so difficult - due to the thousands of odd features, it's impossible to have output that is both understandable and completely unambiguous.

CREATE TABLE "TEST_USER"."TABLE1" 
   (    "A" NUMBER
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

...

-- new object type path: SCHEMA_EXPORT/PROCEDURE/PROCEDURE
-- CONNECT TEST_USER
CREATE EDITIONABLE procedure           procedure1 is begin null; end;
/

...

-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE EDITIONABLE VIEW "TEST_USER"."VIEW1" ("A") AS 
  select 1 a from dual
;