Oracle to Greenplum Migration Tools and Services

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to Greenplum.

  • Oracle 11g, 10g and 9i
  • Greenplum 4.2 and 4.1

Migration Tools

Oracle to Greenplum Migration Reference

Technical information on migration from Oracle to Greenplum.

Last Update: Oracle 11g and Greenplum 4.2

Data Types

Converting built-in SQL data types:

Oracle Greenplum
1 BINARY_DOUBLE Double-precision floating point number DOUBLE PRECISION
2 CLOB Large character object TEXT
3 DATE Date and time (without fraction) TIMESTAMP(0)
4 NUMBER(p [,0]) 1 <= p < 5 16-bit integer SMALLINT
5 NUMBER(p [,0]) 5 <= p < 9 32-bit integer INT
6 NUMBER(p [,0]) p > 19 Integer number DECIMAL(p, 0)
7 NUMBER(p, s) s <> 0 Fixed-point number DECIMAL(p, s)
8 RAW(n) 1 <= n <= 2000 Variable-length binary data BYTEA
9 ROWID Row physical address CHAR(10)
10 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)

Data type attributes and options:

Oracle Greenplum
BYTE and CHAR column size semantics Removed

Built-in SQL Functions

Converting functions:

Oracle Greenplum
1 SYSDATE Get the current date and time CURRENT_TIMESTAMP
2 SYSTIMESTAMP Get the current date and time with fraction CURRENT_TIMESTAMP
3 USER Get database user name CURRENT_USER

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Oracle Greenplum
1 ENABLE constraint attribute Removed
2 NOVALIDATE constraint attribute Removed

Data distribution:

Oracle Greenplum
1 There is PRIMARY KEY or UNIQUE constraint DISTRIBUTED BY (key, …)
2 No PRIMARY KEY and UNIQUE constraint DISTRIBUTED RANDOMLY

Storage, physical and other attributes:

Oracle Greenplum
1 PCTFREE num Removed
2 PCTUSED num Removed
3 INITRANS num Removed
4 MAXTRANS num Removed
5 COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS Removed
6 LOGGING | NOLOGGING Removed
7 SEGMENT CREATION IMMEDIATE | DEFERRED Removed
8 TABLESPACE tablespace_name Removed
9 LOB (column) STORE AS (params) Removed
10 PARALLEL num Removed
11 COMPUTE STATISTICS Removed
12 ENABLE ROW MOVEMENT Removed

STORAGE clause:

Oracle Greenplum
1 INITIAL num Removed
2 NEXT num Removed
3 MINEXTENTS num Removed
4 MAXEXTENTS num Removed
5 PCTINCREASE num Removed
6 FREELISTS num Removed
7 FREELIST GROUPS num Removed
8 BUFFER_POOL DEFAULT | KEEP | RECYCLE Removed
9 FLASH_CACHE DEFAULT | KEEP | NONE Removed
10 CELL_FLASH_CACHE DEFAULT | KEEP | NONE Removed

LOB storage clause:

Oracle Greenplum
1 TABLESPACE name Removed
2 DISABLE | ENABLE STORAGE IN ROW Removed
3 CHUNK num Removed
4 NOCACHE Removed
5 LOGGING Removed
6 PCTVERSION Removed

Table partitioning:

Oracle Greenplum
1 PARTITION BY RANGE (column) PARTITION BY RANGE (column)
2 PARTITION name VALUES LESS THAN (value) PARTITION name END (value) EXCLUSIVE
3 SUBPARTITION BY HASH (column, …) BY HASH not supported by Greenplum, removed
4 SUBPARTITION BY LIST (column, …) SUBPARTITION BY LIST (column, …)
5 SUBPARTITIONS num Removed
6 SUBPARTITION TEMPLATE (template) SUBPARTITION TEMPLATE (template)

CREATE INDEX Statement

Converting CREATE INDEX statement and USING INDEX clause for PRIMARY KEY constraints.

Oracle Greenplum
1 Index LOCAL and GLOBAL partitions Removed
2 REVERSE Removed

For conversion of physical attributes and STORAGE clause in CREATE INDEX, see CREATE TABLE Statement.