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.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference