Oracle数据迁移到GreenPlum

Oracle端表结构

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 10 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 10 PCTUSED 40 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"

使用sqluldr工具导出Oracle表数据

[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
54|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端创建表

[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 will 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)

导入数据

zwcdb=# copy tab_gp from '/tmp/tab_ora.csv' delimiter '|';
COPY 87975
zwcdb=# select count(*) from tab_gp;
 count 
-------
 87975
(1 row)

zwcdb=# select * from tab_gp;
  id   |       owner        |              name              
-------+--------------------+--------------------------------
    15 | SYS                | UNDO$
    29 | SYS                | C_COBJ#
     3 | SYS                | I_OBJ#
    25 | SYS                | PROXY_ROLE_DATA$
    41 | SYS                | I_IND1
    17 | SYS                | FILE$
    13 | SYS                | UET$
     9 | SYS                | I_FILE#_BLOCK#
    43 | SYS                | I_FILE1
    51 | SYS                | I_CON1
     7 | SYS                | I_TS#
    19 | SYS                | IND$
    21 | SYS                | COL$
    45 | SYS                | I_TS1
    35 | SYS                | I_UNDO2
     5 | SYS                | CLU$
    23 | SYS                | PROXY_DATA$
    47 | SYS                | I_USER2
    49 | SYS                | I_COL2
    37 | SYS                | I_OBJ2
    39 | SYS                | I_OBJ4
    57 | SYS                | I_CCOL1
    59 | SYS                | BOOTSTRAP$
    33 | SYS                | I_TAB1
    31 | SYS                | CDEF$
    53 | SYS                | I_CDEF1
    55 | SYS                | I_CDEF3
    11 | SYS                | I_USER#
    27 | SYS                | I_PROXY_ROLE_DATA$_2
    61 | SYS                | OBJAUTH$
    63 | SYS                | I_OBJAUTH2
    65 | SYS                | I_UGROUP1
    67 | SYS                | TSQ$
    69 | SYS                | VIEW$
    71 | SYS                | SUPEROBJ$
    73 | SYS                | I_SUPEROBJ2
    75 | SYS                | I_VIEW1
    77 | SYS                | I_SYN1
    79 | SYS                | I_SEQ1
    81 | SYS                | I_LOB1
    83 | SYS                | COLTYPE$
    85 | SYS                | I_COLTYPE2

zwcdb=# select gp_segment_id,count(*) from tab_gp group by gp_segment_id;
 gp_segment_id | count 
---------------+-------
             1 | 43982
             0 | 43993
(2 rows)