oracle 分区交换-归档数据

最新推荐文章于 2025-01-26 23:56:18 发布

转载最新推荐文章于 2025-01-26 23:56:18 发布 · 504 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/ocpyang/1194465

文章标签：

#数据库

本文详细介绍了数据库分区操作的步骤，包括创建分区表、基表及其数据导入，以及如何进行分区之间的数据交换，包括同一表内的分区与外部表、不同分区间的互换等。此外，还展示了通过中间表实现分区表间数据交换的过程，并强调了数据迁移前后对索引的影响及重建索引的重要性。

交换分区的操作步骤如下：
1. 创建分区表t1，假设有2个分区，P1，P2.
2. 创建基表t11存放P1规则的数据。
3. 创建基表t12 存放P2规则的数据。
4. 用基表t11和分区表T1的P1分区交换。把表t11的数据放到到P1分区
5. 用基表t12 和分区表T1p2 分区交换。把表t12的数据存放到P2分区。
----1.未分区表和分区表中一个分区交换
create table t1
(
sid int not null primary key,
sname varchar2(50)
)
PARTITION BY range(sid)
( PARTITION p1 VALUES LESS THAN (5000) tablespace test,
PARTITION p2 VALUES LESS THAN (10000) tablespace test,
PARTITION p3 VALUES LESS THAN (maxvalue) tablespace test
) tablespace test;
SQL> select count(*) from t1;
COUNT(*)
----------
0
create table t11
(
sid int not null primary key,
sname varchar2(50)
) tablespace test;
create table t12
(
sid int not null primary key,
sname varchar2(50)
) tablespace test;
create table t13
(
sid int not null primary key,
sname varchar2(50)
) tablespace test;
--循环导入数据
declare
maxrecords constant int:=4999;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t11 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
declare
maxrecords constant int:=9999;
i int :=5000;
begin
for i in 5000..maxrecords loop
insert into t12 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
declare
maxrecords constant int:=70000;
i int :=10000;
begin
for i in 10000..maxrecords loop
insert into t13 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
commit;
SQL> select count(*) from t11;
COUNT(*)
----------
4999
SQL> select count(*) from t12;
COUNT(*)
----------
5000
SQL> select count(*) from t13;
COUNT(*)
----------
60001
--交换分区
alter table t1 exchange partition p1 with table t11;
SQL> select count(*) from t11; --基表t11数据为0
COUNT(*)
----------
0
SQL> select count(*) from t1 partition (p1); --分区表的P1分区数据位基表t11的数据
COUNT(*)
----------
4999
alter table t1 exchange partition p2 with table t12;
select count(*) from t12;
select count(*) from t1 partition (p2);
alter table t1 exchange partition p3 with table t13;
select count(*) from t13;
select count(*) from t1 partition (p3);
-----2.分区表和分区表交换
/*
EXCHANGE PARTITION WITH TABLE的方式不支持分区表与分区表的交换，只能通过中间表中转.
*/
--2.1源表
create tablespace jinrilog
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilog01.DBF'
size 200M autoextend on next 20M maxsize unlimited
extent management local autoallocate
segment space management auto
;
create tablespace jinrilogindex
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\jinrilogindex01.DBF'
size 200M autoextend on next 20M maxsize unlimited
extent management local autoallocate
segment space management auto
;
create table t1
(
sid int not null ,
sname varchar2(50) not null,
createtime date default sysdate not null
)
PARTITION BY range(createtime)
(
PARTITION p1 VALUES LESS THAN ('2013-06-01 00:00:00') tablespace jinrilog,
PARTITION p2 VALUES LESS THAN ('2013-07-01 00:00:00') tablespace jinrilog,
PARTITION p3 VALUES LESS THAN ('2013-08-01 00:00:00') tablespace jinrilog,
PARTITION p4 VALUES LESS THAN (maxvalue) tablespace jinrilog
) tablespace jinrilog;
create unique index un_t1_01 on t1(sid,createtime)
tablespace jinrilogindex
local;
alter table t1 add constraint pk_t1 primary key(sid,createtime);
create index index_t1_01
on t1 (sname asc)
tablespace jinrilogindex
local
(
partition index_sname_01 tablespace jinrilogindex,
partition index_sname_02 tablespace jinrilogindex,
partition index_sname_03 tablespace jinrilogindex,
partition index_sname_04 tablespace jinrilogindex
);
--循环导入数据
declare
maxrecords constant int:=1000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang','2013-06-11 00:00:00');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
declare
maxrecords constant int:=2000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang','2013-07-11 00:00:00');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
declare
maxrecords constant int:=3000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang','2013-08-11 00:00:00');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/
SQL> select count(*) from t1;
COUNT(*)
----------
6000
SQL> select count(*) from t1 partition(p1) ;
COUNT(*)
----------
0
SQL>
SQL> select count(*) from t1 partition(p2) ;
COUNT(*)
----------
1000
SQL> select count(*) from t1 partition(p3) ;
COUNT(*)
----------
2000
SQL> select count(*) from t1 partition(p4) ;
COUNT(*)
----------
3000
---查看表数据分区情况
select utp.table_name,utp.partition_name,utp.tablespace_name from user_tab_partitions utp
where utp.table_name='T1';
--查看分区索引分布情况
col index_name for a20
col partition_name for a20
col tablespace_name for a20
col status for a10
select index_name,null partition_name,tablespace_name,status
from user_indexes
where table_name='T1'
and partitioned='NO'
union
select index_name,partition_name,tablespace_name,status from user_ind_partitions
where index_name in
(
select index_name from user_indexes
where table_name='T1'
)
order by 1,2,3
;
--2.2 和中间表交换数据
create table t11
(
sid int not null ,
sname varchar2(50) not null,
createtime date default sysdate not null
)tablespace jason;
select count(*) from t11;
alter table t1 exchange partition p2 with table t11;
--查看无效的索引并重建
col index_name for a20
col partition_name for a20
col tablespace_name for a20
col status for a10
select index_name,null partition_name,status
from user_indexes
where table_name='T1'
and partitioned='NO'
union
select index_name,partition_name,status from user_ind_partitions
where index_name in
(
select index_name from user_indexes
where table_name='T1'
)
order by 1,2,3
;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
INDEX_T1_01 INDEX_SNAME_01 USABLE
INDEX_T1_01 INDEX_SNAME_02 UNUSABLE
INDEX_T1_01 INDEX_SNAME_03 USABLE
INDEX_T1_01 INDEX_SNAME_04 USABLE
UN_T1_01 P1 USABLE
UN_T1_01 P2 UNUSABLE
UN_T1_01 P3 USABLE
UN_T1_01 P4 USABLE
alter index INDEX_T1_01 rebuild partition INDEX_SNAME_02;
alter index UN_T1_01 rebuild partition P2;
col index_name for a20
col partition_name for a20
col tablespace_name for a20
col status for a10
select index_name,null partition_name,status
from user_indexes
where table_name='T1'
and partitioned='NO'
union
select index_name,partition_name,status from user_ind_partitions
where index_name in
(
select index_name from user_indexes
where table_name='T1'
)
order by 1,2,3
;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
INDEX_T1_01 INDEX_SNAME_01 USABLE
INDEX_T1_01 INDEX_SNAME_02 USABLE
INDEX_T1_01 INDEX_SNAME_03 USABLE
INDEX_T1_01 INDEX_SNAME_04 USABLE
UN_T1_01 P1 USABLE
UN_T1_01 P2 USABLE
UN_T1_01 P3 USABLE
UN_T1_01 P4 USABLE
select count(*) from t1 partition (p2);
COUNT(*)
----------
0
select count(*) from t11;
COUNT(*)
---------
1000
--确定数据是否已经切换到新的表空间
SELECT TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='T1' AND PARTITION_NAME='P2';
TABLESPACE_NAME
------------------------------
JASON
---2.3中间表和归档表再次交换数据
create tablespace archive01
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive01.DBF'
size 200M autoextend on next 20M maxsize unlimited
extent management local autoallocate
segment space management auto
;
create tablespace archive02
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\archive02.DBF'
size 200M autoextend on next 20M maxsize unlimited
extent management local autoallocate
segment space management auto
;
create table t2
(
sid int not null ,
sname varchar2(50) not null,
createtime date default sysdate not null
)
PARTITION BY range(createtime)
(
PARTITION p1 VALUES LESS THAN ('2013-06-01 00:00:00') tablespace archive01,
PARTITION p2 VALUES LESS THAN ('2013-07-01 00:00:00') tablespace archive01,
PARTITION p3 VALUES LESS THAN ('2013-08-01 00:00:00') tablespace archive01,
PARTITION p4 VALUES LESS THAN (maxvalue) tablespace archive01
) tablespace archive01;
create unique index un_t2_01 on t2(sid,createtime)
tablespace archive02
local;
alter table t2 add constraint pk_t2 primary key(sid,createtime);
select up.table_name,up.partition_name,up.tablespace_name from user_tab_partitions up
where up.table_name='T2';
--查看分区索引分布情况
col index_name for a20
col partition_name for a20
col tablespace_name for a20
col status for a10
select index_name,null partition_name,tablespace_name,status
from user_indexes
where table_name='T2'
and partitioned='NO'
union
select index_name,partition_name,tablespace_name,status from user_ind_partitions
where index_name in
(
select index_name from user_indexes
where table_name='T2'
)
order by 1,2,3
;
INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS
-------------------- -------------------- -------------------- ----------
UN_T2_01 P1 ARCHIVE02 USABLE
UN_T2_01 P2 ARCHIVE02 USABLE
UN_T2_01 P3 ARCHIVE02 USABLE
UN_T2_01 P4 ARCHIVE02 USABLE
select count(*) from t2;
COUNT(*)
---------
0
--交换数据
alter table t2 exchange partition p2 with table t11 ;
select count(*) from t2;
select count(*) from t11;

转载于:https://blog.51cto.com/ocpyang/1194465