⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 oracle 分区表数据定期迁移到其他数据库测试方案.htm

📁 本人在工程上收集的比较常用的oracle sql语句 有问题请发email:leafboyman@tom.com
💻 HTM
📖 第 1 页 / 共 2 页
字号:
🔍
12 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3c.org/TR/1999/REC-html401-19991224/loose.dtd">
<!-- saved from url=(0042)http://orchidllh.itpub.net/post/4020/38420 -->
<HTML xmlns="http://www.w3.org/1999/xhtml"><HEAD><TITLE>岁月留痕</TITLE><!--    ITPUB_STYLE Templete Written by oldwain( http://blog.itpub.net/oldwain ), 2004   Based on "Boxed-Green" by Scott Yang         "Boxed-Green" written by Scott Yang of www.movablestyles.org   and released under the Creative Commons attribution license   (http://creativecommons.org/licenses/by/2.0/).    2005.06.02 -- porting to plog 1.0   -->
<META http-equiv=Content-Type content="text/html; charset=GB2312">
<META content=TRUE name=MSSmartTagsPreventParsing>
<STYLE type=text/css media=screen>@import url( http://blog.itpub.net//templates/blog_4020/Itpub_Style_v5_pub2_plug//style.css );
</STYLE>

<META content="MSHTML 6.00.2800.1555" name=GENERATOR></HEAD>
<BODY>
<DIV id=banner>
<H1><A href="http://orchidllh.itpub.net/">岁月留痕</A></H1></DIV>
<DIV id=menu><A href="http://orchidllh.itpub.net/">首页</A> | <A 
href="http://blog.itpub.net/admin.php">管理</A> | <A 
title="Link to the RSS 1.0 feed." 
href="http://orchidllh.itpub.net/rss/rss10/4020">RSS feed</A> | <A 
title="Link to the Atom feed." 
href="http://orchidllh.itpub.net/rss/atom/4020">Atom feed</A> | == | <A 
href="http://orchidllh.itpub.net/category/4020/12068">Mysql</A> | <A 
href="http://orchidllh.itpub.net/category/4020/12069">MaxDB</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7406">生活点滴</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7404">ORACLE_转贴</A> | <A 
href="http://orchidllh.itpub.net/category/4020/9100">工作笔记</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7405">BI</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7614">ORACLE_分区</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7407">OS</A> | <A 
href="http://orchidllh.itpub.net/category/4020/7423">SPSS</A> </DIV>
<DIV id=content>
<DIV class=blog>
<H2 class=date>2005 年 08 月 18 日, 星期四</H2>
<DIV class=blogbody><!-- <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"                                   xmlns:dc="http://purl.org/dc/elements/1.1/"                                   xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"><rdf:Description                             rdf:about="http://orchidllh.itpub.net/post/4020/38420"                             dc:identifier="http://orchidllh.itpub.net/post/4020/38420"                             dc:title="oracle 分区表数据定期迁移到其他数据库测试方案"                             trackback:ping="http://blog.itpub.net//trackback.php?id=38420"/></rdf:RDF> -->
<H3 class=title>oracle 分区表数据定期迁移到其他数据库测试方案</H3>
<P>源数据库保存在线数据和一周的日志数据以供查询，历史数据库保存三个月的历史数据以供查询，每天大概会产生1000万的日志记录，计划日志表通过按照周建立分区进行数据的迁移。</P>
<P>--新数据库的规划的exp_test的设计：</P>
<P>--第一次使用，创建全部三个数据表空间和索引表空间，以后每周增加下周要使用的新分区，移出上上周的数据到31数据库<BR>--计划表exp_test是按照日期建立的每周一个的范围分区，将每周的周一的日期拼在分区的名字里面，<BR>--便于自动处理。</P>
<P>--第一次使用，创建三个的数据表空间和索引表空间，大小按照每周的容量建，减少表空间自动扩展对性能的消耗<BR>--以后只是删除旧的分区，对应的表空间复用，这样不用每次创建表空间，可以节省创建表空间的时间，热备份的脚本也不用做成动态的。<BR>--数据表空间，按照每个10G建立</P><BR>
<P>CREATE TABLESPACE PEXPTEST_DATA_TB1 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_data_tb1.dbf' SIZE 10G AUTOEXTEND ON 
NEXT 100M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 100M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P>CREATE TABLESPACE PEXPTEST_DATA_TB2 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_data_tb2.dbf' SIZE 10G AUTOEXTEND ON 
NEXT 100M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 100M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P>CREATE TABLESPACE PEXPTEST_DATA_TB3 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_data_tb3.dbf' SIZE 10G AUTOEXTEND ON 
NEXT 100M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 100M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P>--索引表空间，按照每个4G建立<BR>CREATE TABLESPACE PEXPTEST_INDEX_TB1 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_index_tb1.dbf' SIZE 4G AUTOEXTEND ON 
NEXT 50M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 50M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P>CREATE TABLESPACE PEXPTEST_INDEX_TB2 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_index_tb2.dbf' SIZE 4G AUTOEXTEND ON 
NEXT 50M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 50M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P>CREATE TABLESPACE PEXPTEST_INDEX_TB3 DATAFILE 
<BR>'/home1/oracle/oradata/dwtest/pexptest_index_tb3.dbf' SIZE 4G AUTOEXTEND ON 
NEXT 50M MAXSIZE UNLIMITED<BR>LOGGING<BR>ONLINE<BR>PERMANENT<BR>EXTENT 
MANAGEMENT LOCAL UNIFORM SIZE 50M<BR>BLOCKSIZE 8K<BR>SEGMENT SPACE MANAGEMENT 
AUTO ;</P>
<P><BR>-----创建表，假设从20050801上线，默认创建三个分区，这样定期执行的任务从第二周的数据写满以后开始执行<BR>CREATE TABLE 
dwtest.EXP_TEST<BR>(<BR>SSN VARCHAR2(24 BYTE) NOT NULL,<BR>...<BR>)<BR>PARTITION 
BY RANGE (CREATE_TIME)<BR>(PARTITION PEXPTEST_DATA_20050801 VALUES LESS THAN 
(TO_DATE('2005/08/08','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB1,<BR>PARTITION 
PEXPTEST_DATA_20050808 VALUES LESS THAN (TO_DATE('2005/08/15','yyyy/mm/dd')) 
tablespace PEXPTEST_DATA_TB2,<BR>PARTITION PEXPTEST_DATA_20050815 VALUES LESS 
THAN (TO_DATE('2005/08/22','yyyy/mm/dd')) tablespace PEXPTEST_DATA_TB3)<BR>;</P>
<P>-----建立索引<BR>CREATE INDEX dwtest.PEXPTEST_SSN ON 
dwtest.EXP_TEST<BR>(SSN)<BR>INITRANS 16<BR>MAXTRANS 255<BR>STORAGE 
(<BR>MAXEXTENTS 2147483645<BR>)<BR>LOCAL ( <BR>PARTITION PEXPTEST_DATA_20050801 
tablespace PEXPTEST_INDEX_TB1,<BR>PARTITION PEXPTEST_DATA_20050808 tablespace 
PEXPTEST_INDEX_TB2,<BR>PARTITION PEXPTEST_DATA_20050815 tablespace 
PEXPTEST_INDEX_TB3)<BR>NOPARALLEL;</P>
<P>--至此，应用可以向新表中填写数据。<BR>--规划在第三周的周一开始执行增加新分区，移出老分区的任务，以下是手工执行的脚本，可以通过定义自动执行的任务完成。</P>
<P>--首先新增加分区，保证下周可用，不会因为数据转出有问题而耽搁了前台应用的填写<BR>------增加新分区</P>
<P>----新增分区（这里有点疑惑，新增分区的时候不能指定索引分区，需要再rebuild，索性没有数据，rebuild也很快）<BR>alter table 
dwtest.exp_test add partition PEXPTEST_DATA_20050822 <BR>VALUES LESS THAN 
(TO_DATE('2005/08/29','yyyy/mm/dd'))<BR>tablespace PEXPTEST_DATA_TB1;</P>
<P>-----rebuild本地分区到新的表空间<BR>alter index dwtest.PEXPTEST_SSN rebuild PARTITION 
PEXPTEST_DATA_20050822 <BR>TABLESPACE PEXPTEST_INDEX_TB1;</P>
<P>-----对新增分区进行表分析：<BR>analyze table dwtest.exp_test partition 
(PEXPTEST_DATA_20050822) <BR>estimate statistics sample 20 percent;</P>
<P>--第二步是将第一周的数据迁移到31数据库，关于数据迁移的方案，有三个可选，分别是可移动表空间、拖出表空间和数据泵倒入倒出<BR>--前两个方案要求表空间是自包含的，不能对分区的单个表进行迁移，虽然也可以通过将分区的表空间先交换到一个临时表，然后将<BR>--临时表的表空间通过移动表空间到目标数据库，然后再做一次分区交换到目标数据库的分区表中，应该会是速度上最快的一个方案，<BR>--但是问题是在表空间从源数据库迁移到到目标数据库的时候，这一周的数据将是不能访问的。</P>
<P>--以下的试验是针对数据泵倒入倒出这个方案的：</P>
<P>--首先源数据库创建一个目录对象来存放转储的文件。（只需要创建一次）<BR>CREATE DIRECTORY dumpout_dir AS 
'/home/oracle/backup/detail';</P>
<P>--用数据泵导出工具导出数据。 按照指定分区的方式导出该分区的所有数据<BR>expdp system/****** 
TABLES=dwtest.exp_test:PEXPTEST_DATA_20050801 DUMPFILE=detail_out20050801.dmp 
DIRECTORY=dumpout_dir CONTENT = DATA_ONLY<BR>指定只导出数据，不导出元数据。</P>
<P>----试验31的一个分区的expdp导出，记录142226992，需要11分钟，不到12个G。<BR>[lisa@bj31 detail]$ expdp 
system/****** TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp 
DIRECTORY=dumpout_dir CONTENT = DATA_ONLY</P>
<P>Export: Release 10.1.0.3.0 - Production on Monday, 15 August, 2005 9:58</P>
<P>Copyright (c) 2003, Oracle. All rights reserved.</P>
<P>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 
Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Starting 
"SYSTEM"."SYS_EXPORT_TABLE_01": system/******** 
TABLES=dwtest.exp_test:PEXPTEST_P2 DUMPFILE=detail_out20050815.dmp 
DIRECTORY=dumpout_dir CONTENT = DATA_ONLY <BR>Estimate in progress using BLOCKS 
method...<BR>Processing object type 
TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA<BR>Total estimation using 
BLOCKS method: 15.52 GB<BR>. . exported "PCARD"."EXP_TEST":"PEXPTEST_P2" 11.22 
GB 142226992 rows<BR>Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully 
loaded/unloaded<BR>******************************************************************************<BR>Dump 
file set for SYSTEM.SYS_EXPORT_TABLE_01 
is:<BR>/home2/oracle/lisa/detail/detail_out20050815.dmp<BR>Job 
"SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 10:09</P>
<P><BR>--将文件detail_out20050801.dmp转移到远程系统中，放在目录指定目录下，可以导出的时候直接放置在nas设备上，这样目标数据库可以<BR>--直接从nas设备上取，减少了数据文件传输的时间</P>
<P>--在目标数据库中创建一个目录对象。（只需要创建一次）<BR>CREATE DIRECTORY dumpout_dir AS 
'/home/oracle/backup/detail';</P>
<P>--目标数据库上创建一个临时用户，用户做分区交换前的临时表<BR>CREATE USER PCARD_TEMP<BR>IDENTIFIED BY 
VALUES '******'<BR>......;</P>
<P>--创建一个临时表，该表的名字和结构和分区表一样，但是是个普通表，表空间和目标分区使用的表空间一样。<BR>--这个表需要每次重建，以保证使用的分区是正确的<BR>drop 
table dwtest_temp.exp_test;<BR>CREATE TABLE dwtest_temp.EXP_TEST<BR>(<BR>SSN 
VARCHAR2(24 BYTE) NOT NULL,<BR>......<BR>)<BR>tablespace 
PEXPTEST_DATA_20050801<BR>nologging;</P>
<P>--使用数据泵导入工具将该文件导入到该数据库中。 <BR>impdp system/****** DIRECTORY=dumpout_dir 
DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp 
TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA 
NOLOGFILE=Y<BR>--指定将数据倒入到dwtest_temp.exp_test表中，不需要记录日志，不需要使用原有的表空间<BR>----倒入只用了18分钟<BR>[oracle@localhost 
local]$ impdp system/****** DIRECTORY=dumpout_dir 
DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp 
TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y</P>
<P>Import: Release 10.1.0.2.0 - Production on Tuesday, 16 August, 2005 13:58</P>
<P>Copyright (c) 2003, Oracle. All rights reserved.</P>
<P>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 
Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Master 
table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded<BR>Starting 
"SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=dumpout_dir 
DUMPFILE=detail_out20050815.dmp REMAP_SCHEMA=dwtest:dwtest_temp 
TRANSFORM=SEGMENT_ATTRIBUTES:N INCLUDE=TABLE_DATA NOLOGFILE=Y <BR>Processing 
object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA<BR>. . imported 
"PCARD_TEMP"."EXP_TEST":"PEXPTEST_P2" 11.22 GB 142226992 rows<BR>Job 
"SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:16</P>
<P><BR>--数据倒入以后创建索引，这个是所有的步骤中最耗时的部分，测试的时候是非并行的写日志的，如果采用并行不写日志，应该会再快一些<BR>CREATE 
INDEX dwtest_temp.PEXPTEST_SSN ON dwtest_temp.EXP_TEST<BR>(SSN)<BR>INITRANS 
16<BR>MAXTRANS 255<BR>STORAGE (<BR>MAXEXTENTS 2147483645<BR>)<BR>tablespace 
PEXPTEST_INDEX_20050801<BR>NOPARALLEL<BR>nologging;</P>
<P>---测试的时候：创建索引，写日志的情况，用了1个半小时<BR>SQL&gt; CREATE INDEX dwtest_temp.PEXPTEST_SSN 
ON dwtest_temp.EXP_TEST<BR>2 (SSN)<BR>INITRANS 16<BR>MAXTRANS 255<BR>STORAGE 
(<BR>MAXEXTENTS 2147483645<BR>)<BR>tablespace 
PEXPTEST_INDEX_20050801<BR>NOPARALLEL;</P>
<P>--在数据倒入且索引创建完毕以后，交换表空间，包括索引表空间也可以交换过去，速度非常快<BR>ALTER TABLE dwtest.exp_test 
<BR>EXCHANGE PARTITION PEXPTEST_DATA_20050801 <BR>WITH TABLE 
dwtest_temp.exp_test <BR>INCLUDING INDEXES <BR>WITHOUT VALIDATION;</P>
<P>--在源数据库上删除已经迁移过去的分区，不删除对应的表空间，表空间是复用的<BR>alter table dwtest.exp_test drop 
partition PEXPTEST_DATA_20050801;</P>
<P>--截至到这一步，一周的数据可以迁移到目标数据库的分区表中，测试的数据是1亿4千万条记录，大概需要的时间是2个小时，<BR>--实际上我们一周的数据应该是7000万左右，所以时间还可以更少一些。</P>
<P>--对于历史数据的数据库，我们需要将3个月以上的记录移出数据库，操作实际上比在线数据库还更简单些，可以创建13个数据表空间和13个索引表空间，<BR>--每周一在在线数据库的新数据倒入之前，将最前面一个分区删掉，创建一个新的分区使用原有的那个表空间，然后在临时表处理完毕后<BR>--和临时表交换表空间，得到新的一周的数据，在这样的规划中，历史数据数据库默认保存13周的数据。</P>
<P><BR>--另外在在线数据库中新增分区的脚本：add_new_partition_35.sh<BR>--在线数据库使用数据泵导出最前面一个分区的脚本：expdp_old_partition_35.sh，调用parfile_out.sql生成expdp的参数文件<BR>--历史数据库中删除第一个分区，使用数据泵倒入记录，分区交换，并在源数据库上删除第一个分区的脚本：exchange_data_31.sh,调用parfile_in.sql生成impdp的参数文件<BR>--最后，因为表空间和数据文件不是动态重建的，所以对热备份没有影响，热备份的脚本不是动态的：dwtest_hot.sh</P>
<P></P>
<DIV class=posted><A 
href="http://orchidllh.itpub.net/user/4020/4292">orchidllh</A> 发表于:2005.08.18 
10:49 ::分类: (<A href="http://orchidllh.itpub.net/category/4020/9100">工作笔记</A>) 
::阅读:(852次) :: <A href="http://orchidllh.itpub.net/post/4020/38420">评论 (0)</A> 
:: <A href="http://orchidllh.itpub.net/trackbacks/4020/38420">引用 (0)</A> 
</DIV></DIV>
<P>
<H3>发表评论</H3>
<FORM name=NewComment action=http://orchidllh.itpub.net/index.php method=post>
<P>标题<BR><INPUT style="WIDTH: 70%" name=commentTopic> 
<P>在此添加评论 <BR><TEXTAREA style="WIDTH: 70%" name=commentText rows=10 cols=50></TEXTAREA></P>
<P>称呼<BR><INPUT name=userName></P>
<P>邮箱地址（可选）<BR><INPUT name=userEmail></P>
<P>个人主页（可选）<BR><INPUT 
name=userUrl></P><INPUT type=submit value=发表 name=Add><BR><BR><INPUT type=hidden 
value=AddComment name=op> <INPUT type=hidden value=38420 name=articleId> <INPUT 
type=hidden value=4020 name=blogId> <INPUT type=hidden name=parentId> 
<P></FORM></P></DIV></DIV>
<DIV id=links><!--
<div class="sidetitle">导航</div>
<div class="side">
<ul>
<p>
   <li><a href="http://orchidllh.itpub.net">首页</a></li>
   <li><a href="http://blog.itpub.net/admin.php">管理</a></li>
   <li><a title="Link to the RSS 1.0 feed." href="http://orchidllh.itpub.net/rss/rss10/4020">RSS feed</a></li>
   <li><a title="Link to the Atom feed." href="http://orchidllh.itpub.net/rss/atom/4020">Atom feed</a></li>
12

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -