通過DB_LINK按照分割槽表抽取資料

DB:11.2.0.3.0
OS:oracle-linux 5.7

建表語句：
create table YOON.YOON_HIS
(
c_id NUMBER not null,
YOON_code VARCHAR2(30),
create_time DATE,
update_time DATE not null,
)
partition by range (CREATE_TIME)
(
partition PART_01 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255,
partition SYS_P29637 values less than (TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P30739 values less than (TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P31828 values less than (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P32711 values less than (TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
partition SYS_P33052 values less than (TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace YOON
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
)
);

建立DB_LINK，按分割槽表來抽取資料。（因資料量過大，大約8億行，直接抽取會報undo回滾段錯誤）

按照分割槽抽數方法如下：

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME<=(TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

insert /*+APPEND*/ into YOON.YOON_HIS select /*+ parallel（t 8） */ * from YOON.YOON_HIS@cts01 t
where CREATE_TIME>(TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) and
CREATE_TIME<=(TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
commit;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-1389526/，如需轉載，請註明出處，否則將追究法律責任。

通過DB_LINK按照分割槽表抽取資料

相關文章