First of all we need to start a command shell and go to the directory where the scripts are:
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
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
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
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?
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
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
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
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?
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;
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\
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