Best practices for Oracle to Azure Database for PostgreSQL flexible server schema conversion

This article provides best practices and recommendations for the Oracle to Azure Database for PostgreSQL schema conversion feature in Visual Studio Code with Microsoft Foundry. Follow these guidelines to get reliable, high-quality results.

Plan the schema conversion

A successful conversion starts with planning. Decide what to convert and which Azure Database for PostgreSQL target to use before running the tool.

Scope the source schemas

Specify which Oracle application schemas you want to convert. The extraction workflow automatically excludes Oracle system and built-in schemas, such as SYS, SYSTEM, XDB, MDSYS, CTXSYS, and WMSYS.

Align the target PostgreSQL major version

Use the same PostgreSQL major version on the scratch database as on the production target Azure Database for PostgreSQL flexible server. The conversion tool emits DDL that targets a specific PostgreSQL major version. Converting against one major version and deploying to another can surface syntax or feature differences during deployment.

Scan for unsupported objects in advance

Review the Oracle to Azure Database for PostgreSQL flexible server schema conversion limitations before you start. For each unsupported object, decide in advance whether to recreate the functionality natively on PostgreSQL, replatform it to an appropriate Azure service, or drop it from the migration scope.

Plan remediation for unsupported objects

For each unsupported object identified in the previous step, record the chosen remediation path before you run the conversion. Track:

The Oracle object name and type.
The chosen path: recreate, replatform, or drop.
The target Azure service or PostgreSQL pattern, if you're replatforming.

Choose where to run the conversion

For small schemas, you can run the conversion from your local workstation. For larger schemas, run Visual Studio Code and the schema conversion tool on an Azure virtual machine instead.

A large conversion runs for a long time and makes sustained calls to your Oracle source, the scratch database, and Microsoft Foundry. Running from an Azure virtual machine gives you:

Network proximity: The virtual machine sits in the same Azure region as your Azure Database for PostgreSQL flexible server and your Microsoft Foundry resource, which reduces round-trip latency on the many calls a conversion makes.
Stable, long-running sessions: The conversion isn't interrupted by workstation sleep, reboots, VPN drops, or corporate network timeouts.
Private connectivity: You can place the virtual machine in the same virtual network as your target server and Microsoft Foundry private endpoint, so traffic doesn't traverse the public internet.
Predictable resources: You can size CPU, memory, and disk for the conversion workload, and keep artifacts on a managed disk that you back up.

Place the virtual machine in the region that hosts your scratch database, and give it network access to the source Oracle database. If you use thick client mode, install Oracle Instant Client on the virtual machine. For more information, see Oracle connectivity modes.

Prepare the source Oracle environment

Before you run a conversion, prepare the source Oracle environment. Grant the conversion tool the privileges it needs to read schema metadata, and verify that concurrent session capacity is sufficient so the tool can extract a complete and accurate schema.

Required Oracle privileges

The Oracle connection user that the conversion tool uses needs the privileges listed in Source Oracle privileges in the tutorial:

Privilege	Purpose
CONNECT	Basic database connection
SELECT_CATALOG_ROLE	Access to data dictionary views
SELECT ANY DICTIONARY	Read system metadata and dictionary objects
SELECT `SYS.ARGUMENT$`	Access to procedure and function argument information
SELECT `SYS.V_$RESOURCE_LIMIT`	Access to session and process utilization and limits through `V$RESOURCE_LIMIT`

Use least-privilege access according to organizational policy. The tool reads schema metadata from DBA_* catalog views. The user doesn't need privileges on any application table or to read row-level data.

Set the Oracle sessions parameter

Make sure the Oracle sessions parameter is greater than 10 so the tool can open enough concurrent metadata reads. Check the current value with:

SELECT name, value
FROM v$parameter
WHERE name = 'sessions';

Prepare the scratch database

The schema conversion tool uses a scratch database on Azure Database for PostgreSQL flexible server to validate converted objects. Provision and configure the server before you start a conversion so validation behavior matches the eventual production target.

Warning

If schemas corresponding to the Oracle schemas you select already exist in the selected scratch database, the tool drops them with DROP SCHEMA ... CASCADE and then recreates them. This action deletes their existing objects and data and can remove dependent objects in other schemas. Use an empty scratch database or a dedicated, unused scratch database that contains no objects or data you need to preserve.

Required PostgreSQL privileges

The PostgreSQL connection user needs permission to recreate the matching schemas and validate objects in the scratch database:

CONNECT privilege on the scratch database.
CREATE privilege on the scratch database to create validation schemas.
Permission to drop existing matching schemas. Coordinate schema ownership with your database administrator; CREATE and USAGE privileges alone don't permit dropping another role's schema.
Permission to create objects in the validation schemas.

Have an administrator with the appropriate extension-installation permissions prepare the required extensions. The conversion account doesn't need to install extensions that an administrator already installed in the selected database.

Choose an appropriate scratch database size

The scratch database validates DDL only; it doesn't host application workload. Use a compute tier that provides stable connection capacity for conversion and validation activity. Size the scratch database separately from the production target, and downsize it after conversion is complete.

Allow and install required extensions

The schema conversion tool depends on several PostgreSQL extensions. These extensions translate Oracle built-in packages, spatial types, partitioning, and full-text search. They also enable observability on the scratch database. Before your first conversion run, add the extensions that the converted schema needs to the server's allow list, then install them in the scratch database.

The following table lists commonly used extensions for Oracle to Azure Database for PostgreSQL conversions. Include the ones that apply to the source schema, and add any others the workload requires.

Address any Recommended extensions are not installed warning for the selected scratch database by adding the missing extensions to the server's allow list and installing them in that database. You can continue without them, but objects that depend on those capabilities might require manual changes.

Extension	Purpose
`orafce`	Oracle built-in package compatibility (`DBMS_`, `PLV`, `UTL_FILE`, and common functions)
`uuid-ossp`	UUID generation, equivalent to Oracle `SYS_GUID`
`pgcrypto`	Cryptographic and hashing functions, equivalent to Oracle `DBMS_CRYPTO`
`pg_trgm`	Trigram indexes for `LIKE`/`ILIKE` and fuzzy text search
`tablefunc`	Table-returning functions, including `crosstab` for pivot tables
`fuzzystrmatch`	String similarity and edit-distance functions
`postgis`	Spatial types and operators (replaces Oracle Spatial)
`postgis_topology`	Topology model for PostGIS
`postgis_tiger_geocoder`	Geocoder bundled with PostGIS
`pg_partman`	Time- and range-based partition management
`pg_stat_statements`	Per-query performance telemetry
`plpgsql_check`	Additional static checks of converted PL/pgSQL routine bodies on the scratch database
`dblink`	Separate connections used by converted autonomous-transaction logic

Add plpgsql_check to the server's allow list, then install it in the selected scratch database to make its additional static checks available. These checks supplement compilation; they don't prove equivalent business logic or production readiness. A Converted status doesn't establish that every routine passed these checks.

If the generated code uses dblink, install it in the destination database as well as the scratch database.

Check extension availability and configuration for the PostgreSQL version you use in the extension list by PostgreSQL version.

Step 1: Add extensions to the allow list

In the Azure portal, open the Azure Database for PostgreSQL flexible server that hosts your scratch database. Select Server parameters, search for azure.extensions, and select each extension from the list. Save your changes.

Configure shared_preload_libraries only for libraries required by the extensions and features you use. For example, pg_stat_statements and plpgsql_check require their corresponding libraries. For pg_partman background maintenance, the library is pg_partman_bgw, not pg_partman. Preserve existing entries and restart the server after changing this parameter. See Load libraries and Configure pg_partman.

Step 2: Install the extensions in the scratch database

Connect to the selected scratch database as a member of the azure_pg_admin role. The following example includes the recommended extensions and pg_trgm; run the statements for the extensions that apply to your conversion:

CREATE EXTENSION IF NOT EXISTS orafce;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS tablefunc;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
CREATE EXTENSION IF NOT EXISTS pg_partman;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS plpgsql_check;
CREATE EXTENSION IF NOT EXISTS dblink;

Step 3: Configure search_path for Oracle compatibility

orafce installs Oracle-compatible packages in dedicated schemas (oracle, dbms_*, plv*, utl_file). Set search_path at the database level so those schemas, along with the PostGIS (topology, tiger) schemas, are available to every connection. A database-level setting also covers objects that can't qualify a reference themselves, such as views, CHECK constraints, column defaults, and generated columns.

ALTER DATABASE <database_name> SET search_path = public, oracle, topology, tiger,
    dbms_random, dbms_alert, dbms_assert, dbms_output, dbms_pipe,
    dbms_sql, dbms_utility, plvchr, plvdate, plvlex, plvstr,
    plvsubst, plunit, utl_file;

Include only the schemas for the extensions that you installed. Reconnect after you run this statement, because the new value applies to sessions that start after the change. To scope the setting to a single role instead, use ALTER ROLE <role_name> SET search_path = ....

Important

When pg_catalog isn't explicitly listed in search_path, PostgreSQL searches it before the listed schemas. For functions that also have built-in versions, check overload resolution and qualify calls such as oracle.to_char(...) when you need the Oracle-compatible implementation.

Configure Microsoft Foundry capacity

Microsoft Foundry capacity directly affects conversion reliability, especially for large or complex Oracle schemas. Provision sufficient tokens per minute (TPM) and monitor usage so conversions complete without interruption.

Provision sufficient tokens per minute

Configure your Microsoft Foundry deployment with a quota greater than 1,000,000 tokens per minute (TPM) for optimal performance. Complex schema objects consume significant token capacity during conversion.
Monitor consumption from the Microsoft Foundry portal and raise the limit if you observe throttling during a conversion run.

Run one project at a time

Run a single schema conversion project at a time. Concurrent projects compete for the same Microsoft Foundry quota and can cause throttling, partial conversions, and unexpected token costs. Process projects sequentially to keep behavior predictable and easier to debug.

Secure the conversion workflow

The conversion tool runs locally in Visual Studio Code and connects to three endpoints: the Microsoft Foundry deployment, the source Oracle database, and the target Azure Database for PostgreSQL flexible server. Before you start a conversion, confirm that Visual Studio Code can reach all three endpoints from your workstation, then apply standard enterprise security controls to each connection.

Confirm network connectivity from Visual Studio Code

Verify that the workstation running Visual Studio Code can reach the Microsoft Foundry endpoint, the Oracle source database, and the Azure Database for PostgreSQL flexible server. If any connection is blocked by a corporate firewall, VPN, or network security group, work with your network team to allow outbound access before you start a conversion.

Use private endpoints or firewall rules for the target

Restrict network access to the Azure Database for PostgreSQL flexible server. Use private endpoints for VNet-integrated workstations or configure firewall rules that allow only the IP ranges your team uses.

Use Microsoft Entra ID authentication

Connect to Azure Database for PostgreSQL flexible server with Microsoft Entra authentication instead of password authentication. Microsoft Entra authentication centralizes access control, supports conditional access policies, and produces auditable sign-in events.

Manage credentials safely

Don't embed Oracle or Azure Database for PostgreSQL credentials in plain text and don't commit them to source control. Store them in Azure Key Vault or your organization's secret manager, and reference them from the conversion tool's connection configuration in Visual Studio Code at connect time.

Validate the converted schema

Automated conversion accelerates migration, but manual validation is essential to catch semantic differences, platform-specific behaviors, and edge cases that AI or tooling might miss. Use Conversion Object Explorer to review and verify both converted and not-converted objects. Compare the Oracle and PostgreSQL definitions where available, and read the conversion notes.

For more information, see Review converted objects in Conversion Object Explorer.

Validate complex code objects

Manually validate the following complex Oracle code objects after conversion:

Stored procedures: Review the converted procedure logic, parameter handling, and exception management.
Packages: Validate package structure and dependency resolution against PostgreSQL schemas.
Functions: Verify return types, parameter mappings, and business logic accuracy.

Validation workflow

Review and verify both converted and not-converted objects in Conversion Object Explorer and resolve identified issues, using GitHub Copilot assistance as needed.
Review every complex object converted by AI, even when its conversion status indicates success.
Confirm that deployment SQL includes the intended corrections, then use the Setup Experience to deploy to a separate nonproduction database. Resolve reported deployment issues.
Run procedures, functions, and application workloads in that nonproduction database with representative test data. Don't use the conversion scratch database for functional or data validation.
Confirm that business logic and result sets match the Oracle source before you promote the schema to production.

Retain conversion artifacts

Save the generated PostgreSQL DDL folder along with all reports produced by the conversion tool as the audit artifact for the migration project. These artifacts are useful for:

Compliance and audit reviews.
Knowledge transfer to operations or application teams.

Store the artifacts in your team's source-control or document-management system.

Feedback

Was this page helpful?

Last updated on 2026-10-05