How Oracle data is migrated to Greenplum

Oracle End Table Structure

Sql> Select Dbms_metadata.get_ddl (' TABLE ', ' Tab_ora ', ' ZWC ') from dual;

CREATE TABLE "ZWC". Tab_ora "

("ID" number,

"OWNER" VARCHAR2 (30),

"NAME" VARCHAR2 (128),

CONSTRAINT "pk_id" PRIMARY KEY ("ID")

USING INDEX PCTFREE Initrans 2 Maxtrans 255 COMPUTE STATISTICS

STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist GROUPS 1

Buffer_pool default Flash_cache default Cell_flash_cache default)

Tablespace "USERS" ENABLE

) SEGMENT Creation IMMEDIATE

PCTFREE pctused Initrans 1 Maxtrans 255

Nocompress LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist GROUPS 1

Buffer_pool default Flash_cache default Cell_flash_cache default)

Tablespace "USERS"

Using the SQLULDR tool to export Oracle table data

[Oracle@ggos ~]$./sqluldr zwc/zwc@oraprod query= "select * from Tab_ora" field=0x7c records=0x0d0x0a, CHARSET=GBK file=tab _ora.csv Head=off

0 rows exported at 2013-12-04 22:08:14, size 0 MB.

87975 rows exported at 2013-12-04 22:08:14, size 3 MB.

Output file Tab_ora.csv closed at 87975 rows, size 3 MB.

[Oracle@ggos ~]$ more Tab_ora.csv

20| sys| icol$

46| sys| I_user1

28| sys| con$

15| sys| undo$

29| sys| c_cobj#

3| sys| i_obj#

25| sys| proxy_role_data$

41| sys| I_ind1

34: sys| I_cdef2

40| sys| I_obj5

26| sys| I_proxy_role_data$_1

17| sys| file$

13| sys| uet$

9| sys| i_file#_block#

43| sys| I_file1

51| sys| I_con1

38| sys| I_obj3

7| sys| i_ts#

56| sys| I_cdef4

19| sys| ind$

14| sys| seg$

6| sys| c_ts#

44| sys| I_file2

21| sys| col$

.......................................

GP-Side Creation table

[Gpadmin@mdw gpseg-1]$ psql-h 192.168.1.23-d zwcdb-u zhongwc-w

Password for user zhongwc:

Psql (8.2.15)

Type ' help ' for help.

zwcdb=# CREATE TABLE TAB_GP (

ZWCDB (# ID integer primary key,

ZWCDB (# owner varchar (200),

ZWCDB (# Name varchar (200)

ZWCDB (#);

Notice:create table/primary KEY'll CREATE implicit index "Tab_gp_pkey" for TABLE "TAB_GP"

CREATE TABLE

zwcdb=# \d+ TAB_GP

Table "PUBLIC.TAB_GP"

Column | Type | modifiers | Storage | Description

--------+------------------------+-----------+----------+-------------

ID | Integer | NOT NULL | Plain |

Owner |           Character varying (200) | | Extended |

name |           Character varying (200) | | Extended |

Indexes:

"Tab_gp_pkey" PRIMARY KEY, Btree (ID)

Has Oids:no

Distributed by: (ID)