Database Conversion Workbench

nested-group-icon.png

DB2

DCW offers an option to extract an Oracle schema using the Extraction Script.

By Jordan Hodges posted Fri April 17, 2020 06:53 PM

  
This script extracts DDL and other additional information from an Oracle database environment.

  • This script uses SQL*Plus Oracle client
  • User needs DBA privilege to the database to execute the script
  • For security reasons, this script "DOES NOT" bring user definitions and real passwords from dblinks
Download the extract utility zip here

Detail instructions (Also contained in README.txt)

* This directory contains following files:

1. dcw-extract-gen.sh : Main shell script used to generate schema extract files
2. sample-schema-sel.sql : Sample SQL file to select schema names from Oracle database
3. lib : Directory containing helper files
4. README.txt : This file

* Executing dcw-extract-gen.sh with proper arguments

Usage: ./dcw-extract-gen.sh [-single | -multiple] OutputDir SchemaFile
-single : Generates schema ddl file one per schema
-multiple : Generates multiple schema ddls in one file
OutputDir : Output dir (Should not exist)
SchemaFile : Text file containing schema names, one per line

* How to extract schema names from Oracle database:

Create a file named schema-sel.sql and add the following content:

--Start of script
set newpage 0;
set echo off;
set feedback off;
set heading off;
set pagesize 0;

spool schema-names.txt;
select username as schema_name
from sys.all_users
order by username;
spool off;
exit;
--End of script

Execute the above with SQL*Plus editor with proper permissions. If it succeeds it will
generate schema-names.txt in the same directory.

e.g. sqlplus user/password as sysdba @schema-sel.sql

You can add necessary filter conditions to filter out Oracle SYSTEM schemas. See the
accompanied sample-schema-sel.sql, which filters out system schemas and selects only
user schemas.
You can also edit schema-names.txt later to include/exclude for schema names.

Assume the contents of schema-names.txt contains the following schema names:
HR
HRPLUS
SALES
PURCHASE
STORE

* Examples:

Using -single option

./dcw-extract-gen.sh -single /tmp/s1 /tmp/schema-names.txt

This will generate following files in /tmp/s1
dcw-schema-extractor.sh
extract_script_HR.sql
extract_script_HRPLUS.sql
extract_script_PURCHASE.sql
extract_script_SALES.sql
extract_script_STORE.sql
schema-dir

Execute dcw-schema-extractor.sh with proper arguments (Oracle user/password) and
it will generate DDL files in schema-dir, one DDL file per schema

schema-dir/

HR_DDL.sql
HRPLUS_DDL.sql
PURCHASE_DDL.sql
SALES_DDL.sql
STORE_DDL.sql

HR_DDL_INCOMPATIBLE.sql
HRPLUS_DDL_INCOMPATIBLE..sql
PURCHASE_DDL_INCOMPATIBLE..sql
SALES_DDL_INCOMPATIBLE..sql
STORE_DDL_INCOMPATIBLE..sql

HR_DDL_INVALID.sql
HRPLUS_DDL_INVALID.sql
PURCHASE_DDL_INVALID.sql
SALES_DDL_INVALID.sql
STORE_DDL_INVALID.sql

HR.log
HRPLUS.log
PURCHASE.log
SALES.log
STORE.log

For every schema, four files are generated
{SchemaName}_DDL.sql : Schema DDL file
{SchemaName}_DDL_INCOMPATIBLE.sql : Contains Incompatible SQL's
{SchemaName}_DDL_INVALID.sql : Contains detected invalid SQL's, marked Invalid by Oracle
{SchemaName}.log : Run log for each file

You can also individually execute the schema extract file with same results.

e.g sqlplus <OracleDBUser>/<OraclDBPassword> as sysdba @HR_DDL.sql


Using -multiple option

./dcw-extract-gen.sh -multiple /tmp/s2 /tmp/schema-names.txt

This will generate following files in /tmp/s1
dcw-schema-extractor.sh
extract_script_100.sql
schema-dir

Execute dcw-schema-extractor.sh with proper arguments (Oracle user/password) and
it will generate DDL files in schema-dir, multiple schemas in one DDL file

schema-dir/

100_DDL.sql 100_DDL_INVALID_ddl.sql 100_DDL_INCOMPATIBLE.sql 100.log

Since multipe option is selected, there is only one schema extract file generated.
The numbering starts with 100, 101 and so on.
If you check inside 100_ddl.sql, the schema filter is set to:
--[DDL_EXTRACTION] VARIABLE SCHEMA_FILTER "IN ('HR','HRPLUS','SALES','PURCHASE','STORE')"

{3 digit number}_DDL.sql : Schema extract DDL file for multiple schemas
{3 digit number}_DDL_INVALID.sql : Contains Incompatible SQL's
{3 digit number}_DDL_INCOMPATIBLE.sql : Contains detected invalid SQL's, marked Invalid by Oracle
{3 digit number}.log : Run log

You can also individually execute the schema extract file with same results.

e.g sqlplus <OracleDBUser>/<OraclDBPassword> as sysdba @100_DDL.sql

0 comments
24 views

Permalink