Tags:
交换分区:exchange partition
表空间传输:transport tablespace
标题:
利用交换分区和传输表空间技术,对历史数据实施分离。
第一部分:准备测试环境
一、首先模拟源库(JCR10G):
CREATE TABLESPACE TBS_DATA_0906 DATAFILE 'F:\ORACLE\ORADATA\JCR10G\TBS_DATA_0906.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON /
CREATE TABLESPACE TBS_indx_0906 DATAFILE 'F:\ORACLE\ORADATA\JCR10G\TBS_indx_0906.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON /
CREATE TABLESPACE TBS_DATA_0907 DATAFILE 'F:\ORACLE\ORADATA\JCR10G\TBS_DATA_0907.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON /
CREATE TABLESPACE TBS_indx_0907 DATAFILE 'F:\ORACLE\ORADATA\JCR10G\TBS_indx_0907.DBF' SIZE 15M AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON / create user edu identified by jyedu DEFAULT TABLESPACE jing TEMPORARY TABLESPACE TEMP; GRANT CONNECT TO edu; GRANT RESOURCE TO edu; ALTER USER edu DEFAULT ROLE CONNECT, RESOURCE; GRANT SELECT ANY DICTIONARY TO edu; /
|
Edu用户下存储各历史数据,这些数据按表存放,均为分区表。
一个月一个分区,存储在不同的表空间中,索引和数据分开存储。
二、目标库(HIS)
CREATE TABLESPACE tbs_his DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HIS\tbs_his01.DBF' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
create user his identified by his DEFAULT TABLESPACE tbs_his TEMPORARY TABLESPACE TEMP; GRANT CONNECT TO his; GRANT RESOURCE TO his; ALTER USER his DEFAULT ROLE CONNECT, RESOURCE; GRANT SELECT ANY DICTIONARY TO his; / create directory jcr as 'f:\'; |
用户his,默认表空间tbs_his
第二部分:在源库准备测试用表
create table message (id number(5),name varchar2(30),createdate date) tablespace jing partition by range(createdate) (partition part0906 values less than(to_date('20090701','yyyymmdd')) tablespace tbs_data_0906 compress, partition part0907 values less than(to_date('20090801','yyyymmdd')) tablespace tbs_data_0907) / create index idx_message_createdate on message(createdate) local (partition part0906 tablespace tbs_indx_0906, partition part0907 tablespace tbs_indx_0907) tablespace indx; / create index idx_message_id on message(id) tablespace indx; / create table sms_sent (id number(5),name varchar2(30),createdate date) tablespace jing partition by range(createdate) (partition part0906 values less than(to_date('20090701','yyyymmdd')) tablespace tbs_data_0906 compress, partition part0907 values less than(to_date('20090801','yyyymmdd')) tablespace tbs_data_0907) / create index idx_sms_sent_createdate on sms_sent(createdate) local (partition part0906 tablespace tbs_indx_0906, partition part0907 tablespace tbs_indx_0907) tablespace indx; /
|
select t.TABLE_NAME,t.PARTITION_NAME,t.PARTITION_POSITION,t.TABLESPACE_NAME from user_tab_partitions t order by 1,3 |
TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | TABLESPACE_NAME |
MESSAGE | PART0906 | 1 | TBS_DATA_0906 |
MESSAGE | PART0907 | 2 | TBS_DATA_0907 |
SMS_SENT | PART0906 | 1 | TBS_DATA_0906 |
SMS_SENT | PART0907 | 2 | TBS_DATA_0907 |
select i.TABLE_NAME,i.INDEX_NAME,i.TABLESPACE_NAME,i.STATUS from user_indexes i order by 1 |
TABLE_NAME | INDEX_NAME | TABLESPACE_NAME | STATUS |
MESSAGE | IDX_MESSAGE_CREATEDATE | [NULL] | N/A |
MESSAGE | IDX_MESSAGE_ID | INDX | VALID |
SMS_SENT | IDX_SMS_SENT_CREATEDATE | [NULL] | N/A |
select p.INDEX_NAME,p.PARTITION_NAME,p.TABLESPACE_NAME,p.STATUS from user_ind_partitions p order by 1,2 |
INDEX_NAME | PARTITION_NAME | TABLESPACE_NAME | STATUS |
IDX_MESSAGE_CREATEDATE | PART0906 | TBS_INDX_0906 | USABLE |
IDX_MESSAGE_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
IDX_SMS_SENT_CREATEDATE | PART0906 | TBS_INDX_0906 | USABLE |
IDX_SMS_SENT_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
1、 MESSAGE表,一个全局索引,一个本地索引(global and local)
2、 SMS_SENT表,只有一个本地索引local
两个表均为两个分区,6、7月份的数据以分区的形式分别存放在tbs_data_0906和tbs_data_0907表空间中。本地索引也分别存放在tbs_indx_0906和tbs_indx_0907两个表空间中。全局索引存放在Indx表空间中。这两个表的默认表空间均为jing。
数据定义好了,接下来造数据:
begin for i in 1..100 loop insert into message values(i,i||'a'||i,decode(power(-1,i),1,to_date('20090704','yyyymmdd'),-1,to_date('20090608','yyyymmdd'))); end loop; end; / begin for i in 1..100 loop insert into sms_sent values(i,i||'a'||i,decode(power(-1,i),1,to_date('20090704','yyyymmdd'),-1,to_date('20090608','yyyymmdd'))); end loop; end; |
第三部分:历史数据分离(把6月份数据迁移到目标数据库HIS中)
采用传输表空间,虽然说tbs_data_0906和tbs_indx_0906两个表空间中只存有6月份的数据,但由于6月份的数据和其他月份的数据是保存在一个逻辑表中的,因此这两个表空间不是自包含的。需要特殊处理一下。
处理方法:先通过交换分区,把6月份的分区交换出去,成为一个独立的逻辑表。
一、准备中间表
create table message_0906 (id number(5),name varchar2(30),createdate date) tablespace jing; / create table sms_sent_0906 (id number(5),name varchar2(30),createdate date) tablespace jing;
create index idx_sms_sent0906_createdate on sms_sent_0906(createdate) tablespace indx; |
二、Exchange Partition
把6月份分区和中间表进行交换(交换操作是直接修改数据字典,因此会很快)
1、不带update indexes
alter table sms_sent exchange partition part0906 with table sms_sent_0906; alter table message exchange partition part0906 with table message_0906; |
TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | TABLESPACE_NAME |
MESSAGE | PART0906 | 1 | JING |
MESSAGE | PART0907 | 2 | TBS_DATA_0907 |
SMS_SENT | PART0906 | 1 | JING |
SMS_SENT | PART0907 | 2 | TBS_DATA_0907 |
|
|
|
|
TABLE_NAME | INDEX_NAME | TABLESPACE_NAME | STATUS |
MESSAGE | IDX_MESSAGE_CREATEDATE | [NULL] | N/A |
MESSAGE | IDX_MESSAGE_ID | INDX | UNUSABLE |
SMS_SENT | IDX_SMS_SENT_CREATEDATE | [NULL] | N/A |
SMS_SENT_0906 | IDX_SMS_SENT0906_CREATEDATE | INDX | UNUSABLE |
|
|
|
|
INDEX_NAME | PARTITION_NAME | TABLESPACE_NAME | STATUS |
IDX_MESSAGE_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_MESSAGE_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
IDX_SMS_SENT_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_SMS_SENT_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
2、带update indexes
alter table sms_sent exchange partition part0906 with table sms_sent_0906 update indexes;; alter table message exchange partition part0906 with table message_0906 update indexes;; |
TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | TABLESPACE_NAME |
MESSAGE | PART0906 | 1 | JING |
MESSAGE | PART0907 | 2 | TBS_DATA_0907 |
SMS_SENT | PART0906 | 1 | JING |
SMS_SENT | PART0907 | 2 | TBS_DATA_0907 |
|
|
|
|
TABLE_NAME | INDEX_NAME | TABLESPACE_NAME | STATUS |
MESSAGE | IDX_MESSAGE_CREATEDATE | [NULL] | N/A |
MESSAGE | IDX_MESSAGE_ID | INDX | VALID |
SMS_SENT | IDX_SMS_SENT_CREATEDATE | [NULL] | N/A |
SMS_SENT_0906 | IDX_SMS_SENT0906_CREATEDATE | INDX | UNUSABLE |
|
|
|
|
INDEX_NAME | PARTITION_NAME | TABLESPACE_NAME | STATUS |
IDX_MESSAGE_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_MESSAGE_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
IDX_SMS_SENT_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_SMS_SENT_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
结论:
1、 加不加update indexes都会使交换分区上的本地索引失效,但奇怪的是dml操作却正常(很疑惑)。
2、 加update indexes可使分区表上的global indexes保持正常。
3、 交换分区的时候,索引并不会跟着交换。
根据以上测试情况,我们可以把数据按月分区,并按月存储在不同的表空间中(索引无需分表空间)。这样,通过传输表空间技术可大大提高历史数据的分离和加载效率。另外,在对历史数据进行备份的时候,我们可以按月份分表空间进行备份。
三、Transport Tablespace
(由于索引表空间不进行传输,先把刚才在中间表上创建的索引删除)
drop index idx_sms_sent0906_createdate |
目前状态如下:
TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | TABLESPACE_NAME |
MESSAGE | PART0906 | 1 | JING |
MESSAGE | PART0907 | 2 | TBS_DATA_0907 |
SMS_SENT | PART0906 | 1 | JING |
SMS_SENT | PART0907 | 2 | TBS_DATA_0907 |
|
|
|
|
TABLE_NAME | INDEX_NAME | TABLESPACE_NAME | STATUS |
MESSAGE | IDX_MESSAGE_ID | INDX | VALID |
MESSAGE | IDX_MESSAGE_CREATEDATE | [NULL] | N/A |
SMS_SENT | IDX_SMS_SENT_CREATEDATE | [NULL] | N/A |
|
|
|
|
INDEX_NAME | PARTITION_NAME | TABLESPACE_NAME | STATUS |
IDX_MESSAGE_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_MESSAGE_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
IDX_SMS_SENT_CREATEDATE | PART0906 | TBS_INDX_0906 | UNUSABLE |
IDX_SMS_SENT_CREATEDATE | PART0907 | TBS_INDX_0907 | USABLE |
1、检查表空间是否自包含(6月份数据表空间tbs_data_0906)
begin dbms_tts.TRANSPORT_SET_CHECK('tbs_data_0906',true); end; select *from transport_set_violations; |
查询临时表结果为空,说明表空间自包含,可以进行传输表空间操作。
2、把tbs_data_0906表空间设为只读
alter tablespace tbs_data_0906 read only; |
3、在目标库创建公共数据库链接
目的是为了直接把元数据导入到目标库。
create public database link system_jcr10g connect to system identified by sys using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.180)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = JCR10G) ) )' |
4、copy表空间数据文件到目标数据库目标位置
SQL> host copy f:\oracle\oradata\jcr10g\tbs_data_0906.dbf f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf 已复制 1 个文件。 |
5、使用impdp数据泵导入元数据
C:\Jcr>impdp system/sys directory=jcr network_link=system_jcr10g transport_tablespaces=tbs_data_0906 transport_datafiles=f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf transport_full_check=y remap_schema=edu:his
Import: Release 10.2.0.4.0 - Production on 星期二, 21 7月, 2009 16:21:08
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=jcr network_link=system_jcr10g transport_tablespaces=tbs_data_0906 transport_datafiles=f:\oracle\product\10.2.0\oradata\his\tbs_data_0906.dbf transport_full_check=y remap_schema=edu:his 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK 处理对象类型 TRANSPORTABLE_EXPORT/TABLE 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 16:21:26 成功完成 |
6、更改目标表空间为读写状态
alter tablespace tbs_data_0906 read write; |
7、The End
SQL> conn his/his@his 已连接。 SQL> select table_name from user_tables;
TABLE_NAME ------------------------------ MESSAGE_0906 SMS_SENT_0906
SQL> select count(*) from message_0906;
COUNT(*) ---------- 51
SQL> select count(*) from sms_sent_0906;
COUNT(*) ---------- 50
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13890753/viewspace-610588/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13890753/viewspace-610588/