In our latest DCW 2.1 release, we’ve made significant improvements to the Oracle DDL extraction script.
What is the DDL extraction script?
It’s a SQL*Plus script created for extracting DDL and additional information from an Oracle database. Oracle’s SQL*Plus is required to run the script which can be found in an Oracle client install or on the Oracle database server.
How do I create the DDL extraction script?
In the Database Conversion perspective, right click on a DCW project and select "Database Conversion > Extract DDL...". Choose the option "Generate a DDL extraction script" and press Finish.
In this wizard, you can set the script's file name and save location, and choose between three options to specify which Oracle's system schemas show not be considered in the DDL extraction:
- Exclude all system schemas (default): the extraction script will not extract the DDL for any system schema objects.
- Customized list of schemas to exclude: provides a list of all system schemas that you can modify so that the extraction script can generate the DDL for some system schema objects.
- Advanced schema selection: provides the option to use operators IN and NOT IN to specify which schemas should be considered (IN) or not considered (NOT IN) for the DDL extraction.
Once all options are completed, press Finish to complete the wizard. For more details on these options, please use the Help topic in the Oracle to DB2 LUW Task Launcher.
How do I run the script?
After creating the script through the wizard, move the file to the location or system with the Oracle database. Then, connect to the Oracle database in SQL*Plus as a SYSDBA user or a user with access to the catalog, and execute the script using the @ symbol and the file name. The example below uses a script with the file prefix OFFLINE within SQL*Plus:
sqlplus "/ as sysdba"
NOTE: the output files will be saved in the current directory.
What are the results?
The script completes by exiting SQL*Plus. Seven files will be created in the user-specified directory:
- OFFLINE_ddl.sql: this is the main DDL file that should be used for DDL evaluation/conversion in DCW. It contains extracted DDL for table spaces, clusters, sequences, views, packages, procedures, tables, functions, triggers, types, indexes, materialized views, materialized view logs, directories, Java source, libraries, grants, roles, synonyms, table and column comments, dimensions, and database links.
- OFFLINE_INVALID_ddl.sql: contains extracted DDL for invalid function, procedure, package, and trigger PL/SQL objects.
- OFFLINE.alldep.txt and OFFLINE.allobjs.txt: contains the object list and their dependencies required for viewing dependencies in DCW’s Package Visualizer.
- OFFLINE.log: It contains extra information about the Oracle environment such as script version, Oracle version, database information and parameters, and SQL*Plus information.
- OFFLINE.MAQ.txt: contains many additional reports to understand the Oracle environment with statistics, sizing, and others. This includes schema summary, database object counts, column data types and counts, partition information, clusters and cluster tables, replication, profiles, NLS parameters, storage statistics, object complexity, wrapped objects, invalid objects, table spaces details, and memory pool information.
Why use the DDL extraction script compared to Data Studio’s Generate DDL wizard?
The extraction script has the following advantages:
- The offline script is faster and a better choice for large databases since it relies only on Oracle native functions, which Data Studio's functions carries an overhead of its underlying database modelling framework.
- Users can make changes and execute the script without the need of Data Studio.
- The script can extract the DDL for more object types than what is available in Data Studio’s wizard.
- More flexibility. For instance, any errors (such as from execution of the DBMS_METADATA package), can be fixed without the need of submitting a PMR to the Data Studio team.
Updates to the Oracle DDL extraction script in DCW 2.1:
- Extended use of DBMS_METADATA:
In this release, almost all types of DDL are extracted using the dbms_metadata package.
What is DBMS_METADATA and what advantages does it have?
DBMS_METADATA is an Oracle package to extract DDL from an Oracle database. It was released in Oracle 9i and it simplifies the extraction process.
This utility allows the user to extract individual objects or an entire Oracle schema by simply executing the dbms_metadata.get_ddl, specifying the
object type and names, and Oracle will extract ready-to-use DDL.
- Added double quotations around all objects to allow spaces and extended chars
Eg: "Schema Á"."Table A"
- Improved logging while running script such as displaying progress for user
Since the DDL extraction can take some time to extract, the user will now see the progress of the script while running and not need to break during the process because of limited details
- Expanded extraction of object types:
LIBRARY: program in C related to Oracle external procedures.
JAVA CLASS: program in Java running at a Java Virtual Machine within the Oracle database
DATABASE LINK: connection details for other local and remote databases
NOTE: A dummy password is extracted for security reasons.
DIRECTORY: directory location at the operating system which is used in BFILE, external tables, and UTL_FILE.
MATERIALIZED VIEW LOG: captures all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view. This usually is used on big tables or to keep changes applied in a remote database.
CLUSTER: join more than one table based on key to reduce I/O.
- Expanded filtering for dependent objects
Filtering improved to not extract deleted or dependent objects, which caused issues in the past.
- Extended roles, grants, and system privileges extraction based on filter clause
Added extraction of dependent grants for roles at several levels based on the IN or NOT IN clause used.
- Added prompt after DDL extraction for exiting or continuing script to gather extra data about the Oracle environment
The script can be exited after the DDL extraction is complete by using Ctrl-C or following the directions inside the script
- Improved MAQ environment and database details file with table space definitions and table row count based on statistics
- Row count script changed to a report that uses the analyze command on Oracle created rows
This new report based on Oracle statistics replaces an older row count statement that would cause full scans at the database, requiring excessive processing. The report produces a usable estimate about rows in tables with no cost to the database. Please be aware that this will only display values if the tables were analyzed.
NOTE: below are statistics shown in the report. An explanation about columns can also be found at the report.
NUM_ROWS: Number of rows in the table.
LAST_ANALYZED: Date on which this table was most recently analyzed. If NULL, the table is not analyzed and the row_num colum will be zero.
- Included additional file (suffix: _ddl_INVALID.sql) with invalid PL/SQL objects
Invalid PACKAGES, PROCEDURES, FUNCTIONS, and TRIGGERS are now extracted to a separate file with suffix ‘_ddl_INVALID.sql’. This will assist with solving missing dependencies and with object errors.
- Enabled optional extraction of table space and storage clause
Although the MAQ file contains table space information, extracting the original table space and storage clause will assist with POCs and re-creating table spaces on larger Oracle environments. To enable this extraction, please follow the directions within the script.
NOTE: Only the names of permanent table spaces are extracted. For additional table space information, please review the MAQ report file.
After running the script, an Oracle ERROR ORA- is issued by DBMS_METADATA. What should I do?
It is possible that the user will see an error issued by dbms_metadata. This may happen when DBMS_METADATA requires dependencies that are missing. It also may happen because of defects related to older versions of Oracle.
If any errors happen during the extraction, the offline extraction script includes a query that extracts DDL errors from the DBMS_METADATA package. These details will be saved in the result file to assist fixing any issues that occurred during DDL extraction.