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博客

通过DB_LINK按照分区表抽取数据

最新文章