Performing a Schema Extraction

Prerequisites

Before extracting a schema, you must download the latest EDB DDL Extractor script from the Quick help panel on the Migration Portal Projects page. The script will extract data definitions, stored procedures, views, etc., from an Oracle database into a text file. Then, you can use EDB DDL Extractor with either SQL*Plus or SQL Developer to extract a schema.

The DDL Extractor for Oracle databases is used as a part of EDB Migration Portal. The EDB DDL Extractor for Oracle database uses Oracle’s DBMS_METADATA built-in package when extracting DDL. The EDB DDL extractor creates the DDL file that will be uploaded to the portal and analyzed for EDB Postgres compatibility.

Please note: You must have SELECT CATALOG ROLE and SELECT ANY DICTIONARY privileges in the Oracle database.

For SQL*Plus

  1. Connect to SQL*Plus and run the command:

    SQL>@edb_ddl_extractor.sql

  2. Provide the schema name and the pathdirectory in which the extractor will store the extracted DDL. When extracting multiple schemas, use a comma (‘,’) as a delimiter.

    For example, on Linux:

    Enter SCHEMA NAME[S] to extract DDLs:

    HR, SCOTT, FINANCE

    Enter the PATH to store DDL file:

    /home/oracle/extracted_ddls/

    On Windows:

    Enter SCHEMA NAME[S] to extract DDLs:

    HR, SCOTT, FINANCE

    Enter the PATH to store DDL file:

    C:\Users\Example\Desktop\

For SQL Developer

  1. Connect to the SQL server and run the following command:
enter the path for linux or windows

Enter the path for Linux or Windows.

  1. Enter a comma separated list of schemas:
migration portal image

Provide a list of schemas.

  1. Enter file path for the output file:
specify the output file path

Specify the output file path.

Please note: You can also enter a single schema name in both SQL*Plus and SQL Developer tools.

The script iterates through the object types in the database and once the task is completed, the .SQL output is stored at the entered location, (i.e., C:\Users\Example\Desktop\).

The EDB DDL Extractor does not extract objects that have names like BIN$b54+4XIEYwPgUAB/AQBWwA= =$0. If you want to extract these objects, you must change the name of the objects and re-run the extraction process.

Supported Object Types

The migration portal supports migration of the following object types:

  • Synonyms
  • DB Links
  • Types and Type Body
  • Sequences
  • Tables
  • Constraints
  • Indexes (Except LOB indexes and indexes on materialized views)
  • Views
  • Materialized Views
  • Triggers
  • Functions
  • Procedures
  • Packages