Usage

First of all we need to start a command shell and go to the directory where the scripts are:

  1. Open a command shell:
    • e.g. on Windows: Start > Execute : cmd
  2. Go to the subdirectory script (of where you extracted the file):
    • cd script
All generated files will be created in a subdirectory build.

Show available targets

We can list the available targets defined in the ant build file (build.xml):

ant -p

=>

J:\prj_ddl_extraction\src\script>ant -p
Buildfile: build.xml
Extract DDL from Oracle
Main targets:

 all
 clean     Deletes all the generated files.
 generate  extract DDL and spool to files
 init      initializes all properties and path
 remap
 spool     spool the extracted DDL to files (subdirectories)
Default target: all


Create initial directory structure

The initial directory structure is created using this ant script.

ant init

=>

J:\prj_ddl_extraction\src\script>ant init
Buildfile: build.xml

init:
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\user
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\sequence
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\db_link
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\table
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\trigger
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\view
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\function
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\package
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\procedure
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\type
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\synonym
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\comment
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\grant
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\other

BUILD SUCCESSFUL
Total time: 0 seconds


Remove all generated files

Call this target to remove all generated files.

ant clean

=>

J:\prj_ddl_extraction\src\script>ant clean
Buildfile: build.xml

init:

clean:
   [delete] Deleting directory J:\prj_ddl_extraction\src\script\build

BUILD SUCCESSFUL
Total time: 0 seconds



Generate DDL and store it in the table XLIB_DDL_STATEMENTS

The ant target GENERATE will generate the ddl statmements and insert them into the table XLIB_DDL_STATEMENTS.

ant generate

=>

J:\prj_ddl_extraction\src\script>ant generate
Buildfile: build.xml

init:
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\user
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\sequence
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\db_link
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\table
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\trigger
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\view
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\function
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\package
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\procedure
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\type
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\synonym
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\comment
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\grant
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\other

generate:
    [input] Please enter userid: [scott/tiger@orcl]
demo/test@xe
    [input] Custom sql script to remove unwanted objects from spool table: [custom_export.sql]

     [exec]
     [exec] SQL*Plus: Release 11.1.0.7.0 - Production on Do Jun 23 12:41:38 2011
     [exec]
     [exec] Copyright (c) 1982, 2008, Oracle.  All rights reserved.
     [exec]
     [exec]
     [exec] Verbunden mit:
     [exec] Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
     [exec]
     [exec]
     [exec] Tabelle mit TRUNCATE geleert.
     [exec]
     [exec]
     [exec] CURR_USER
     [exec] ------------------------------
     [exec] DEMO
     [exec]
     [exec] -- PROFILES
     [exec] -- CREATE USER
     [exec] -- DEFAULT_ROLE
     [exec] -- TABLESPACE QUOTAS
     [exec] -- ROLES
     [exec] -- SYSTEM_GRANTS
     [exec] -- OBJECT_GRANTS
     [exec] -- SEQUENCES
     [exec] -- DB_LINKS
     [exec] -- DIRECTORIES
     [exec] -- TABLES
     [exec] -- REF_CONSTRAINTS
     [exec] -- VIEWS
     [exec] -- FUNCTIONS
     [exec] -- TRIGGERS
     [exec] -- PACKAGES
     [exec] -- PROCEDURES
     [exec] -- TYPES
     [exec] -- SYNONYMS
     [exec] -- MATERIALIZED_VIEWS
     [exec] -- INDEXES
     [exec] -- COMMENTS
     [exec] -- GRANTS
     [exec] -- JOBS
     [exec] -- REMOVE UNWANTED OBJECTS
     [exec] Verbindung zu Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production beendet
     [exec] *** empty file

BUILD SUCCESSFUL
Total time: 44 seconds


What happens here?

  1. Since the ant target GENERATE is dependent on the ant target INIT (see build.xml file for details), the required directories are created automatically.
  2. We are then prompted for the userid. The default is scott/tiger@orcl and would be used if we just pressed the <enter> key. We enter demo/test@xe in order to connect as the oracle user demo with the password test to the local Oracle instance XE (we have a tnsnames entry called xe defined elsewhere).
  3. Then we are prompted to supply the filename for the custom export, the default is custom_export.sql.Here we usually delete objects from the table XLIB_DDL_STATEMENTS before the objects are spooled into the filesystem, e.g.:
    prompt ********************************************************************
    prompt *** Remove unwanted export object from xlib_ddl_statements table ***
    prompt ********************************************************************
    DELETE FROM xlib_ddl_statements
          WHERE ddl_object_name IN (
                           SELECT object_name
                             FROM user_objects
                            WHERE object_name LIKE 'AQ$%'
                                  OR object_name LIKE 'AE%'
                                  or (object_name like 'SYS_%' and object_type = 'TYPE')
                                  or (object_name in ('CREATE$JAVA$LOB$TABLE', 'AQ_SHDB_EVENTS')))
             OR ddl_base_object IN (
                           SELECT object_name
                             FROM user_objects
                            WHERE object_name LIKE 'AQ$%'
                                  OR object_name LIKE 'AE%'
                                  or (object_name like 'SYS_%' and object_type = 'TYPE')
                                  or (object_name in ('CREATE$JAVA$LOB$TABLE', 'AQ_SHDB_EVENTS')))
    /

    prompt *** remove database links
    delete
      FROM   xlib_ddl_statements
     WHERE       ddl_object_type = 'DB_LINK'
             AND ddl_object_name NOT LIKE 'MIG_SRC%'
    /

    prompt *** remove database jobs, will be installed manually
    delete
      from  xlib_ddl_statements
     where ddl_object_type = 'JOB'
    /

  4. The different object types are exported and stored in the table XLIB_DDL_STATEMENTS.

We can supress the prompts and supply the parameters on the command line in order to have a completely scripted approach:
ant generate -Ddb.userid="demo/test@www_xe" -Dcustom_export_file=custom_export.sql

Spool into the filesystem

Call this target to spool all ddl objects into the filesystem.

ant spool

=>

J:\prj_ddl_extraction\src\script>ant spool
Buildfile: build.xml

init:

spool:
    [input] Please enter userid: [scott/tiger@orcl]
demo/test@xe

BUILD SUCCESSFUL
Total time: 31 seconds


Here we get prompted for the userid and enter demo/test@xe. The script then spools the ddl scripts from the table XLIB_DDL_STATEMENTS into the subdirectory structure build/export_ddl.
Also the files run_sys.sql and run_user.sql are automatically generated, so that you could rerun all scripts in a hopefully correct order in the target system. We don't use this feature typically, so there might still be bugs.

run_sys.sql:

set sqlblanklines on
set scan off
set timing off
spool run_sys.log
prompt *** user/user.sql
@@"./user/user.sql"

prompt *** other/user.sql
@@"./other/user.sql"

prompt *** user/object_grant.sql
@@"./user/object_grant.sql"

commit;
spool off
exit


run_user.sql:

set sqlblanklines on
set scan off
set timing off
column object_name format a40;
column object_type format a40;
spool run_user.log
prompt *** sequence/sequences.sql
@@"./sequence/sequences.sql"

prompt *** table/demo_customers.sql
@@"./table/demo_customers.sql"

prompt *** table/demo_images.sql
@@"./table/demo_images.sql"

prompt *** table/demo_orders.sql
@@"./table/demo_orders.sql"

prompt *** table/demo_order_items.sql
@@"./table/demo_order_items.sql"

prompt *** table/demo_page_hierarchy.sql
@@"./table/demo_page_hierarchy.sql"

prompt *** table/demo_product_info.sql
@@"./table/demo_product_info.sql"

prompt *** table/demo_states.sql
@@"./table/demo_states.sql"

...

commit;
spool off
exit


Remap schema name

We need this target since the generated ddl statements have the schema name hardcoded, e.g.

  CREATE TABLE "DEMO"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "FLOW_1" ;
  ALTER TABLE "DEMO"."EMP" ADD PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "FLOW_1"  ENABLE;


ant remap

=>

J:\prj_ddl_extraction\src\script>ant remap
Buildfile: build.xml

init:

remap:
    [input] REMAP: Please enter source schema: [SCOTT]
DEMO
    [input] REMAP: Please enter target schema: [HR]

     [copy] Copying 47 files to J:\prj_ddl_extraction\src\script\build\export_ddl_HR
     [copy] Copied 15 empty directories to 6 empty directories under J:\prj_ddl_extraction\src\script\build\export_ddl_HR

BUILD SUCCESSFUL
Total time: 11 seconds


What happens here?

  1. We get prompted for the source schema name, in our sample we used DEMO.
  2. Then we get prompted for the target schema name, since we simply hit the <enter> key, the default HR is used.
  3. All files from the directory tree build/export_ddl are copied to the directory tree export_ddl_HR.
  4. Then regular expressions are applied to all files in the subdirectory tree build/export_ddl_HR to replace the patterns "DEMO". with "HR"..
If we simply want to remove the hardcoded value "DEMO". we enter DEMO for the target schema, then all references are removed, e.g.:

  CREATE TABLE "EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "FLOW_1" ;
  ALTER TABLE "EMP" ADD PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "FLOW_1"  ENABLE;



Putting it all together ... the final script

We use the following script in our development environment to do several steps with a single script:

set USER_LOGIN="demo/test@xe"
set TNS_ADMIN=T:\Dropbox\My Dropbox\config\oracle

call ant clean
call ant generate -Ddb.userid=%USER_LOGIN% -Dcustom_export_file=custom_export.sql
call ant spool -Ddb.userid=%USER_LOGIN%
call ant remap -Dremap.source_schema=DEMO -Dremap.target_schema=DEMO

xcopy /Y /S /E build\export_ddl_DEMO ..\demo_generated\


What happens here?
  1. We store the connect information for the user login in the environment variable USER_LOGIN.
  2. We set TNS_ADMIN explicitly in our environment. If it already defined properly in your environment, then this is not required.
  3. All previous files are deleted (ant clean).
  4. The DDL for all objects in the schema DEMO is generated and stored in the table XLIB_DDL_STATEMENTS.
  5. The DDL statements are spooled into the filesystem.
  6. The hardcoded "DEMO." is removed from all files.
  7. All files are copied to the directory ../demo_generated.
See the transcript here:

J:\prj_ddl_extraction\src\script>extract_current_ddl.cmd

J:\prj_ddl_extraction\src\script>set USER_LOGIN="demo/test@xe"

J:\prj_ddl_extraction\src\script>set TNS_ADMIN=T:\Dropbox\My Dropbox\config\oracle

J:\prj_ddl_extraction\src\script>call ant clean
Buildfile: build.xml

init:

clean:
   [delete] Deleting directory J:\prj_ddl_extraction\src\script\build
   [delete] Deleting: J:\prj_ddl_extraction\src\script\tmp_export.sql

BUILD SUCCESSFUL
Total time: 2 seconds
Buildfile: build.xml

init:
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\user
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\sequence
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\db_link
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\table
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\trigger
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\view
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\function
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\package
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\procedure
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\type
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\synonym
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\comment
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\grant
    [mkdir] Created dir: J:\prj_ddl_extraction\src\script\build\export_ddl\other

generate:
    [input] skipping input as property db.userid has already been set.
    [input] skipping input as property custom_export_file has already been set.
     [exec]
     [exec] Tabelle mit TRUNCATE geleert.
     [exec]
     [exec]
     [exec] CURR_USER
     [exec] ------------------------------
     [exec] DEMO
     [exec]
     [exec] -- PROFILES
     [exec] -- CREATE USER
     [exec] -- DEFAULT_ROLE
     [exec] -- TABLESPACE QUOTAS
     [exec] -- ROLES
     [exec] -- SYSTEM_GRANTS
     [exec] -- OBJECT_GRANTS
     [exec] -- SEQUENCES
     [exec] -- DB_LINKS
     [exec] -- DIRECTORIES
     [exec] -- TABLES
     [exec] -- REF_CONSTRAINTS
     [exec] -- VIEWS
     [exec] -- FUNCTIONS
     [exec] -- TRIGGERS
     [exec] -- PACKAGES
     [exec] -- PROCEDURES
     [exec] -- TYPES
     [exec] -- SYNONYMS
     [exec] -- MATERIALIZED_VIEWS
     [exec] -- INDEXES
     [exec] -- COMMENTS
     [exec] -- GRANTS
     [exec] -- JOBS
     [exec] -- REMOVE UNWANTED OBJECTS
     [exec] *** empty file custom_export.sql executed ...

BUILD SUCCESSFUL
Total time: 41 seconds
Buildfile: build.xml

init:

spool:
    [input] skipping input as property db.userid has already been set.

BUILD SUCCESSFUL
Total time: 23 seconds
Buildfile: build.xml

init:

remap:
    [input] skipping input as property remap.source_schema has already been set.
    [input] skipping input as property remap.target_schema has already been set.
     [copy] Copying 47 files to J:\prj_ddl_extraction\src\script\build\export_ddl_DEMO
     [copy] Copied 15 empty directories to 6 empty directories under J:\prj_ddl_extraction\src\script\build\export_ddl_DEMO

BUILD SUCCESSFUL
Total time: 3 seconds
build\export_ddl_DEMO\run_sys.sql
build\export_ddl_DEMO\run_user.sql
build\export_ddl_DEMO\function\custom_auth.sql
build\export_ddl_DEMO\function\custom_hash.sql
build\export_ddl_DEMO\other\user.sql
build\export_ddl_DEMO\package\xlib_component.pkb
build\export_ddl_DEMO\package\xlib_component.pks
build\export_ddl_DEMO\package\xlib_conf.pkb
build\export_ddl_DEMO\package\xlib_conf.pks
build\export_ddl_DEMO\package\xlib_http.pkb
build\export_ddl_DEMO\package\xlib_http.pks
build\export_ddl_DEMO\package\xlib_jasperreports.pkb
build\export_ddl_DEMO\package\xlib_jasperreports.pks
build\export_ddl_DEMO\package\xlib_log.pkb
build\export_ddl_DEMO\package\xlib_log.pks
build\export_ddl_DEMO\package\xlib_utl.pkb
build\export_ddl_DEMO\package\xlib_utl.pks
build\export_ddl_DEMO\procedure\xlog.sql
build\export_ddl_DEMO\sequence\sequences.sql
build\export_ddl_DEMO\table\demo_customers.sql
build\export_ddl_DEMO\table\demo_images.sql
build\export_ddl_DEMO\table\demo_orders.sql
build\export_ddl_DEMO\table\demo_orders_fk.sql
build\export_ddl_DEMO\table\demo_order_items.sql
build\export_ddl_DEMO\table\demo_order_items_fk.sql
build\export_ddl_DEMO\table\demo_page_hierarchy.sql
build\export_ddl_DEMO\table\demo_product_info.sql
build\export_ddl_DEMO\table\demo_states.sql
build\export_ddl_DEMO\table\demo_users.sql
build\export_ddl_DEMO\table\dept.sql
build\export_ddl_DEMO\table\emp.sql
build\export_ddl_DEMO\table\emp_fk.sql
build\export_ddl_DEMO\table\xlib_components.sql
build\export_ddl_DEMO\table\xlib_conf_values.sql
build\export_ddl_DEMO\table\xlib_demo_images.sql
build\export_ddl_DEMO\table\xlib_jasperreports_demos.sql
build\export_ddl_DEMO\table\xlib_logs.sql
build\export_ddl_DEMO\trigger\demo_customers_trg.sql
build\export_ddl_DEMO\trigger\demo_order_items_trg.sql
build\export_ddl_DEMO\trigger\demo_product_info_trg.sql
build\export_ddl_DEMO\trigger\demo_users_trg.sql
build\export_ddl_DEMO\trigger\xlib_conf_values_trg.sql
build\export_ddl_DEMO\trigger\xlib_ddl_statements_trg.sql
build\export_ddl_DEMO\trigger\xlib_jasperreports_demos_trg.sql
build\export_ddl_DEMO\trigger\xlib_logs_trg.sql
build\export_ddl_DEMO\user\object_grant.sql
build\export_ddl_DEMO\user\user.sql
47 Datei(en) kopiert