Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how Oracle migrates data to Greenplum". In daily operation, I believe many people have doubts about how Oracle migrates data to Greenplum. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how Oracle migrates data to Greenplum". Next, please follow the editor to study!

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

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"

Export Oracle data using the sqluldr tool

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

..

Create tables on the GP side

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

Zwcdb (# name varchar)

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)

Import data

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)

At this point, the study on "how Oracle migrates data to Greenplum" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.