這篇文章主要介紹“Oracle怎么遷移數(shù)據(jù)到Greenplum”，在日常操作中，相信很多人在Oracle怎么遷移數(shù)據(jù)到Greenplum問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle怎么遷移數(shù)據(jù)到Greenplum”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

這篇文章主要介紹“Oracle怎么遷移數(shù)據(jù)到Greenplum”，在日常操作中，相信很多人在Oracle怎么遷移數(shù)據(jù)到Greenplum問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”O(jiān)racle怎么遷移數(shù)據(jù)到Greenplum”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！Oracle

祁門網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)建站,祁門網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為祁門上1000家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)營銷網(wǎng)站建設(shè)要多少錢，請找那個售后服務(wù)好的祁門做網(wǎng)站的公司定做！

端表結(jié)構(gòu)

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工具導(dǎo)出Oracle表數(shù)據(jù)

[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端創(chuàng)建表

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

導(dǎo)入數(shù)據(jù)

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)