13518219792
这篇文章主要介绍“Oracle怎么迁移数据到Greenplum”，在日常操作中，相信很多人在Oracle怎么迁移数据到Greenplum问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle怎么迁移数据到Greenplum”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 这篇文章主要介绍“Oracle怎么迁移数据到Greenplum”，在日常操作中，相信很多人在Oracle怎么迁移数据到Greenplum问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Oracle怎么迁移数据到Greenplum”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！Oracle 祁门网站建设公司创新互联建站,祁门网站设计制作，有大型网站制作公司丰富经验。已为祁门上1000家提供企业网站建设服务。企业网站搭建\外贸营销网站建设要多少钱，请找那个售后服务好的祁门做网站的公司定做！ 端表结构 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)
