新建 文本文档.txt

来自「本人在工程上收集的比较常用的oracle sql语句 有问题请发email:l」· 文本 代码 · 共 129 行

oracle 分区表数据定期迁移到其他数据库测试方案
源数据库保存在线数据和一周的日志数据以供查询，历史数据库保存三个月的历史数据以供查询，每天大概会产生1000万的日志记录，计划日志表通过按照周建立分区进行数据的迁移。

--新数据库的规划的exp_test的设计：

--第一次使用，创建全部三个数据表空间和索引表空间，以后每周增加下周要使用的新分区，移出上上周的数据到31数据库
--计划表exp_test是按照日期建立的每周一个的范围分区，将每周的周一的日期拼在分区的名字里面，
--便于自动处理。

--第一次使用，创建三个的数据表空间和索引表空间，大小按照每周的容量建，减少表空间自动扩展对性能的消耗
--以后只是删除旧的分区，对应的表空间复用，这样不用每次创建表空间，可以节省创建表空间的时间，热备份的脚本也不用做成动态的。
--数据表空间，按照每个10G建立



CREATE TABLESPACE PEXPTEST_DATA_TB1 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_data_tb1.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_DATA_TB2 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_data_tb2.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_DATA_TB3 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_data_tb3.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

--索引表空间，按照每个4G建立
CREATE TABLESPACE PEXPTEST_INDEX_TB1 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_index_tb1.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_INDEX_TB2 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_index_tb2.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;

CREATE TABLESPACE PEXPTEST_INDEX_TB3 DATAFILE 
'/home1/oracle/oradata/dwtest/pexptest_index_tb3.dbf' SIZE 4G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO ;


-----创建表，假设从20050801上线，默认创建三个分区，这样定期执行的任务从第二周的数据写满以后开始执行
CREATE TABLE dwtest.EXP_TEST
(
SSN VARCHAR2(24 BYTE) NOT NULL,
...
)
PARTITION BY RANGE (CREATE_TIME)
(PARTITION PEXPTEST_DATA_20050801 VALUES LESS THAN (TO_DATE('2005/08/08','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB1,
PARTITION PEXPTEST_DATA_20050808 VALUES LESS THAN (TO_DATE('2005/08/15','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB2,
PARTITION PEXPTEST_DATA_20050815 VALUES LESS THAN (TO_DATE('2005/08/22','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB3)
;

-----建立索引
CREATE INDEX dwtest.PEXPTEST_SSN ON dwtest.EXP_TEST
(SSN)
INITRANS 16
MAXTRANS 255
STORAGE (
MAXEXTENTS 2147483645
)
LOCAL ( 
PARTITION PEXPTEST_DATA_20050801 tablespace PEXPTEST_INDEX_TB1,
PARTITION PEXPTEST_DATA_20050808 tablespace PEXPTEST_INDEX_TB2,
PARTITION PEXPTEST_DATA_20050815 tablespace PEXPTEST_INDEX_TB3)
NOPARALLEL;

--至此，应用可以向新表中填写数据。
--规划在第三周的周一开始执行增加新分区，移出老分区的任务，以下是手工执行的脚本，可以通过定义自动执行的任务完成。

--首先新增加分区，保证下周可用，不会因为数据转出有问题而耽搁了前台应用的填写
------增加新分区

----新增分区（这里有点疑惑，新增分区的时候不能指定索引分区，需要再rebuild，索性没有数据，rebuild也很快）
alter table dwtest.exp_test add partition PEXPTEST_DATA_20050822 
VALUES LESS THAN (TO_DATE('2005/08/29','yyyy/mm/dd'))
tablespace PEXPTEST_DATA_TB1;

-----rebuild本地分区到新的表空间
alter index dwtest.PEXPTEST_SSN rebuild PARTITION PEXPTEST_DATA_20050822 
TABLESPACE PEXPTEST_INDEX_TB1;

--在源数据库上删除已经迁移过去的分区，不删除对应的表空间，表空间是复用的
alter table dwtest.exp_test drop partition PEXPTEST_DATA_20050801;

--截至到这一步，一周的数据可以迁移到目标数据库的分区表中，测试的数据是1亿4千万条记录，大概需要的时间是2个小时，
--实际上我们一周的数据应该是7000万左右，所以时间还可以更少一些。

--对于历史数据的数据库，我们需要将3个月以上的记录移出数据库，操作实际上比在线数据库还更简单些，可以创建13个数据表空间和13个索引表空间，
--每周一在在线数据库的新数据倒入之前，将最前面一个分区删掉，创建一个新的分区使用原有的那个表空间，然后在临时表处理完毕后
--和临时表交换表空间，得到新的一周的数据，在这样的规划中，历史数据数据库默认保存13周的数据。


--另外在在线数据库中新增分区的脚本：add_new_partition_35.sh
--在线数据库使用数据泵导出最前面一个分区的脚本：expdp_old_partition_35.sh，调用parfile_out.sql生成expdp的参数文件
--历史数据库中删除第一个分区，使用数据泵倒入记录，分区交换，并在源数据库上删除第一个分区的脚本：exchange_data_31.sh,调用parfile_in.sql生成impdp的参数文件
--最后，因为表空间和数据文件不是动态重建的，所以对热备份没有影响，热备份的脚本不是动态的：dwtest_hot.sh