Extracting schemas using the EDB DDL Extractor v4

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

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.

For SQL*Plus

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

   → WrapCopy

   @edb_ddl_extractor.sql

   The script prompts you to provide the details described in the steps that follow.

2. If the Oracle user that's running the script has the required privileges, press Enter to continue running the script.

3. 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. 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. Enter yes to extract dependent objects from other schemas or no if you don't want to extract them.

6. Enter yes to extract grant statements from other schemas or no if you don't want to want to extract them.

Example

1. Run the extractor script:

   → WrapCopy

   @edb_ddl_extractor.sql

2. Press Enter.

3. Specify three schemas by separating them with commas:

   → WrapCopy

   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:

   → WrapCopy

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

   Similarly on Windows:

   → WrapCopy

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

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

   → WrapCopy

   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:

   → WrapCopy

   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 not to extract dependent objects.

5. Enter yes or no to extract grant statements, and select OK. The default 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.

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

See File encoding for information about the file encoding format Migration Portal expects.