Migrating from PostgreSQL to Oracle

Dump the database

$ pg_dump -d -O
  -d, --inserts            dump data as INSERT, rather than COPY, commands
  -O, --no-owner           skip restoration of object ownership
                           in plain text format

Fix the dump

1. Remove lines

• Remove lines beginning with SET.
• Remove lines beginning with COMMENT.
• Remove CREATE FUNCTION lines.
• Remove CREATE TRUSTED lines.

2. Fix syntax

Fix the create sequence syntax.

3. Fix datatypes

Date columns, with and without timezones. You may also need to change the NLS_TIMESTAMP_FORMAT by adding

alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

to your script. There is also a NLS_DATE_FORMAT

Change text columns to CLOBs.

Change bytea columns to BLOBs.

Change boolean columns to Integer 0,1

4. Fix object name lengths

Oracle’s maximum is only 30 chars.

sqlplus

Starting sqlplus

Use the command line and type sqlplus user/pass

or click on “Run SQL Command Line”

connect

connect <user>/<password>;

where <password> is your password and <user> is your username.

User Management

Start the HTML administration site. Go to Home → Administration → Manage Database Users → Create Database User