Performing a Schema Extraction

Prerequisites Before extracting a schema, you must download the latest EDB DDL Extractor script from the Migration Portal Projects page or from the link provided in the DDL Extractor guide in the Portal Wiki. The script can be run in SQL Developer or SQL*Plus. It uses Oracle’s DBMS_METADATA built-in package to extract DDLs for different objects under schemas (specified while running the script). The EDB DDL extractor creates the DDL file that will be uploaded to the portal and analyzed for EDB Postgres compatibility.

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.

  3. If you want to extract dependant objects from other schemas, enter yes or no.

    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/

    Extract dependent objects from other schemas?(yes/no): yes

    On Windows:

    Enter SCHEMA NAME[S] to extract DDLs:

    HR, SCOTT, FINANCE

    Enter the PATH to store DDL file:

    C:\Users\Example\Desktop\

    Extract dependent objects from other schemas?(yes/no): yes

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.

  1. Extract dependent objects from other schemas?(yes/no): yes
specify the output file path

Extracting dependent objects.

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

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

Additional Notes - The EDB DDL Extractor does not extract objects that have names like:

BIN$b54+4XIEYwPgUAB/AQBWwA= =$0

To extract these objects, you must change the name of the objects and re-run the extraction process.

  • DDL Extractor extracts nologging tables as normal tables. Once these tables are migrated to Advanced Server, WAL log files will be created.

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