oracle备份表几张表,oracle对一个大表的备份



业务介绍：

index_data_test一个有8亿行数据的表，有联合索引，其中有2014年(每天几百万，20150401日期字段)的数据(5亿行)和2015的数据(3亿行)，下面是把2014年的数据备份到另外一个表里

以下是开发过程的主要流程：

v_flow:='1002';--备份物理网格数据

select count(1)

into v_flagnum

from index_data_test

where acct_date >= v_day_begin

and acct_date <= v_day_end;

--如果有需要备份的数据，才需要后续处理

if v_flagnum>0  then

begin

execute immediate 'drop table index_data_test_tmp1';

exception when others then

null;

end;

--将需要备份的数据存入临时表

v_flow:='100201';

execute immediate '

create table index_data_test_tmp1 nologging parallel 8 as

SELECT /*+ parallel(c,8) */

acct_date,

province_code,

eparchy_code,

city_code,

dgrid_id,

pgrid_id,

index_code,

dvalue,

ldvalue,

mtotalvalue,

lmdvalue

from index_data_test

where acct_date >='|| v_day_begin||' and  acct_date <='|| v_day_end   ;

begin

execute immediate 'drop table index_data_test_tmp2';

exception when others then

null;

end;

--备份最新数据

v_flow:='100202';

execute immediate '

create table index_data_test_tmp2 nologging parallel 8 as

SELECT /*+ parallel(c,8) */

acct_date,

province_code,

eparchy_code,

city_code,

dgrid_id,

pgrid_id,

index_code,

dvalue,

ldvalue,

mtotalvalue,

lmdvalue

from index_data_test

where acct_date > ' ||v_day_end ;

v_flow:='100202';

--插入到备份表

v_flow:='100203';

execute immediate '

INSERT INTO index_data_test_bak nologging

SELECT /*+ parallel(a,8) */

acct_date,

province_code,

eparchy_code,

city_code,

dgrid_id,

pgrid_id,

index_code,

dvalue,

ldvalue,

mtotalvalue,

lmdvalue

FROM index_data_test_tmp1 a ';

COMMIT;

--删掉正式表

execute immediate ' drop table index_data_test ';

--创建正式表      v_flow:='100204';      execute immediate '      create table index_data_test nologging parallel 8 as      SELECT /*+ parallel(c,8) */            acct_date,            province_code,            eparchy_code,            city_code,            dgrid_id,            pgrid_id,            index_code,            dvalue,            ldvalue,            mtotalvalue,            lmdvalue       from index_data_test_tmp2  ';             --给正式表创建索引       v_flow:='100205';       execute immediate '       create index PK_index_data_test_0655 on index_data_test (ACCT_DATE, PGRID_ID, INDEX_CODE)       tablespace GRID_INDX_02       pctfree 10       initrans 2       maxtrans 255       storage       (         initial 64K         next 1M         minextents 1         maxextents unlimited       )';         --删除临时表        execute immediate 'drop table index_data_test_tmp1';        execute immediate 'drop table index_data_test_tmp2';  else        --删除临时表        execute immediate 'drop table index_data_test_tmp1';  end if;