Oracle to PostgreSQL
The process of database migration from Oracle to PostgreSQL
consists of the following steps:
- Select Migration Mode
- Connect to Oracle
- Connect to PostgreSQL
- Select Destination
- Conversion Settings
- Select Tables
- Select Views
Step 1 of 7. Select Migration Mode
Specify the method to migrate Oracle database to PostgreSQL. Oracle to
PostgreSQL converter provides two options of database migration:
- Move to PostgreSQL server directly - use this approach
to migrate database from Oracle to PostgreSQL server directly.
You need PostgreSQL server launched and necessary privileges to write
into the target database. See Connect to PostgreSQL
Server section for related information.
- Export into script file - use this approach to export
the Oracle database into a local PostgreSQL script file. The resulting
file contains SQL statements to create all tables and to fill them
with the data. See Import
PostgreSQL script files article for related information.
Step 2 of 7. Connect to Oracle Server
This page allows specifying the necessary settings to establish
connection with Oracle server:
- Server - IP address or network name of Oracle server. If Oracle
to PostgreSQL converter has been installed on the server machine,
"127.0.0.1" or "localhost" can be specified in this box.
- Database name - enter the same title that is used by SQLPlus or
any other standard Oracle tools to point the database.
- Port - port for connecting to Oracle. It must be specified only
when the value differs from the default value 1521.
- Username - Oracle user name
- Password - Oracle user password
Oracle to PostgreSQL migration tool provides two methods of connecting
to Oracle server: direct and via TNSNAME.ORA entry. To use the first method,
enter information in all fields except the port if it is configured
by default (1521). The second method requires leaving both "Server"
and "Port" fields empty and enter Oracle service name as it
appears in TNSNAME.ORA file in "Database" field.
Sometime user may get the following error: "Oracle data source
does not exist. Check to verify correct name was given." despite
of the fact that all Oracle connection settings have been specified
properly. It means the converter failed to connect to Oracle server using
TNSNAME.ORA. To bypass the error leave both "Server" and
"Port" fields empty and enter in "Database" field
the connection string as follows:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=IP address of Oracle server)(PORT=the port)))
Step 3 of 7. Connect to PostgreSQL Server
This wizard screen collects information to connect to PostgreSQL server.
Oracle to PostgreSQL converter is able to connect to local or remote target
server. Local mode allows to connect PostgreSQL server launched on the same
machine where the application is running. Remote mode is designed to connect
to PostgreSQL server launched on another computer.
By default, Oracle to PostgreSQL converter uses local connection mode.
It is indicated by radio button "Local" selected. To establish remote
connection, you should select "Remote" radio button. For remote PostgreSQL
servers you should also specify the host and the port to connect through.
Host could be either network name or IP address. TCP/IP port value is
necessary only if it differs from the default PostgreSQL port number 5432.
Otherwise you may leave this field as is.
Enter user name and password or leave these fields blank for anonymous
connection. To use an existing PostgreSQL database, specify it on this page.
Step 4 of 7. Select Destination
On this wizard screen the destination PostgreSQL database name
or script file must be specified. Oracle to PostgreSQL converter
can process existing database using any of the following options:
- Overwrite the entire database
- Overwrite existing tables only
- Skip existing tables
Also, you can specify a log file to enable the program write execution
traces. If is used to analyze possible error messages and get more control
over the conversion process. If no log file is specified, the logging is
Step 5 of 7. Conversion Settings
On this wizard screen you can customize Oracle to PostgreSQL
migration using the following settings:
- Convert table definitions only - select this checkbox to
convert only table definitions of Oracle data. All the data will not
- Make names case sensitive - select this checkbox to make table
and column names case sensitive. In this mode all table and column names
are enclosed in double quotes to preserve upper/lower case. If you don't
want to use double quotes around names in queries, keep this option
- Skip converting indexes - select this checkbox if you don't
want to convert indexes
- NUMBER w/o precision -> bigint - select this checkbox to
convert all columns having type NUMBER without precision into BIGINT
(by default it is converted into DOUBLE PRECISION based on the range of
You can handle schema in the destination database according one of the
- Use existing: specify existing schema to import all tables there.
- Create new: create new schema to import all tables there.
- Full replication - import full list of schemas from the original database.
Use this mode to preserve full names of all tables the same as in the original database.
Step 6 of 7. Select Tables
This wizard page is designed to select tables and to compose filtering queries
for Oracle to PostgreSQL migration.
To add a table for migration, select it in "Available tables" list
and click "Add" button (or double-click the selected item).
Use "Add all" button to add all available tables. To remove a table
from "Selected tables" list highlight it and click "Remove"
button. Use "Remove all" button to remove all items from
"Selected tables" list.
Use "Add Query" button for composing SELECT-query to filter data
that will be migrated to PostgreSQL. See
article for the further information.
Step 7 of 7. Select Views
This wizard page is designed to manage Oracle views for migrating
To add Oracle view for converting it into PostgreSQL format,
highlight it in "Available queries" list and click
"Add" button (or double-click the selected item). Use
"Add all" button to add all Oracle views. To remove
an item from "Selected queries" list, highlight it and
click "Remove" button (or double-click the selected
item). Use "Remove all" button to remove all Oracle views
from "Selected queries" list.
Sometimes it is important to convert views into PostgreSQL format
according to the particular order (for example, to preserve dependencies).
Use "up" and "down" buttons in right part of
the wizard page to organize all queries in the appropriate order.