Performing a schema extraction v4

Suggest edits

You can perform a schema extraction using either of the following methods. EDB recommends using the EDB DDL extractor to extract your schemas.

For more information, see Known issues, limitations, and notes.

Extracting schemas using the EDB DDL Extractor

Download the latest EDB DDL Extractor script from the Migration Portal Projects page.

Note

Migration Portal might become unresponsive for very large SQL files, depending on your system and browser resource availability. To resolve this, try extracting fewer schemas at a time to reduce the SQL file size.

Prerequisites

You can run the EDB DDL Extractor script 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 uploaded to the portal and analyzed for EDB Postgres Advanced Server compatibility.

Note

You must have CONNECT and SELECT_CATALOG_ROLE roles and CREATE TABLE privilege.

For SQL*Plus

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

    @edb_ddl_extractor.sql

  2. When prompted, if the Oracle user that is running the script has the required privileges, press Enter to continue running the script.

  3. When prompted, provide the schema name. When extracting multiple schemas, use a comma (‘,’) as a delimiter.

    Note

    If you want to extract all the user schemas from the current database, don't mention any schema names while extracting. However, we recommend mentioning the schema names that you want to extract.

  4. When prompted, enter the path or directory for the extractor to store the extracted DDL. If you don't specify a path, the extracted DDL is output to the directory where you ran the SQL*Plus command.

  5. When prompted, enter yesor no depending on whether you want to extract dependent objects from other schemas.

  6. When prompted, enter yesor no depending on whether you want to extract grant statements from other schemas.

For example:

  1. Run the extractor script:

    @edb_ddl_extractor.sql

  2. Press Enter

  3. Specify three schemas by separating them with commas:

    Enter a comma separated list of schemas to be extracted (Default all schemas): HR, SCOTT, FINANCE

  4. Enter a path for the output file:

    On Linux, you might use:

    Location for output file (Default current location) : /home/oracle/extracted_ddls/

    Similarly on Windows:

    Location for output file (Default current location) : c:\Users\Example\Desktop\

  5. Enter yes to extract dependent objects in other schemas:

    WARNING:

Given schema(s) list may contain objects which are dependent on objects 
from other schema(s), not mentioned in the list.` `Assessment may fail 
for such objects. It is suggested to extract all dependent objects 
together.
Extract dependent object from other schemas?(yes/no) (Default no / Ignored for all schemas option): yes

  6. Enter yes to extract grant statements:

    Extract GRANT statements?(yes/no) (Default no): yes

For SQL Developer

After loading the edb_ddl_extractor.sql script into SQL Developer and connecting to the source Oracle database, run the script. As the script executes, respond to the prompts.

  1. If the user for the database connection has the required privileges as listed in the Script Output tab, select Yes to continue running the script.

  2. Enter a comma-separated list of schemas, and select OK.

  3. Enter the path for the output file, and select OK. The default is the location of the DDL Extractor script.

  4. Enter yes or no to extract dependent objects, and select OK. The default is to not extract dependent objects.

  5. Enter yes or no to extract grant statements, and select OK. The default setting is not to extract grant statements.

Output of the DDL Extractor run appears in the Script Output tab. The name of the output file appears after the Extraction Completed message in the script output.

Note

You can also enter single schema name in both SQL*Plus and SQL Developer.

The script then iterates through the object types in the source database. Once the task is completed, the .SQL output is stored at the location you entered (e.g., c:\Users\Example\Desktop\).

See file encoding for information about the file encoding format expected by Migration Portal.

Schemas and objects support

The lists and tables that follow show supported and unsupported schemas and objects in Migration Portal.

Unsupported schemas

Exclude these Oracle systems schemas while generating the SQL dump file.

ANONYMOUSAPEX_PUBLIC_USERAPEX_030200APEX_040000APEX_040200
APPQOSSYSAUDSYSCTXSYSDMSYSDBSNMP
DBSFWUSERDEMODIPDMSYSDVF
DVSYSEXFSYSFLOWS_FILESFLOWS_020100FRANCK
GGSYSGSMADMIN_INTERNALGSMCATUSERGSMROOTUSERGSMUSER
LBACSYSMDDATAMDSYSMGMT_VIEWOJVMSYS
OLAPSYSORDPLUGINSORDSYSORDDATAOUTLN
ORACLE_OCMOWBSYSOWBYSS_AUDITPDBADMINRMAN
REMOTE_SCHEDULER_AGENTSI_INFORMTN_SCHEMASPATIAL_CSW_ADMIN_USRSPATIAL_WFS_ADMIN_USRSQLTXADMIN
SQLTXPLAINSYS$UMFSYSSYSBACKUPSYSDG
SYSKMSYSRACSYSTEMSYSMANTSMSYS
WKPROXYWKSYSWK_TESTWMSYSXDB
XS$NULL
Note

EDB Postgres Advanced Server doesn't support schema names starting with pg_.

Supported object types

  • Tables
  • Sequences
  • Constraints
  • Indexes (Except LOB indexes and indexes on materialized views)
  • Synonyms
  • Views
  • Materialized views
  • DB links
  • Types and type body
  • Triggers
  • Functions
  • Procedures
  • Packages
  • Users
  • Roles
  • Profiles
  • Role and object grants
Note

COMMENTS on columns, tables, and materialized views are also supported.

Unsupported object types

  • Editions
  • Operators
  • Schedulers
  • LOB indexes and indexes on materialized views
  • XML schemas
  • Tablespaces
  • Directories
  • RLS policy
  • Queues *
  • Library
  • Indextype

* Even though EDB Postgres Advanced Server provides support for Queue tables, Migration Portal does not currently support it. Queue tables in the source DDL are not uploaded as source and target DDL objects.

← Prev

Migrating a database

Next →

Extracting schemas using Oracle Data Pump utilities