公司内部
Oracle分区管理
主 题：Oracle分区管理
项目名称：公司内部
撰写单位：Bsoft
作 者：屈毅
版 本 号：0.1

日 期：2020-03-10

审核

文档历史

参考文档

**

概述

一、分区表理论知识

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。<br />在oracle 10g中最多支持：1024k-1个分区。<br /> <br />分区提供以下优点：<br />       （1）由于将数据分散到各个分区中，减少了数据损坏的可能性；<br />       （2）可以对单独的分区进行备份和恢复；<br />       （3）可以将分区映射到不同的物理磁盘上，来分散IO；<br />       （4）提高可管理性、可用性和性能。<br /> <br />Oracle 10g提供了以下几种分区类型：<br />       （1）范围分区（range）；<br />       （2）哈希分区（hash）；<br />       （3）列表分区（list）；<br />       （4）范围－哈希复合分区（range-hash）；<br />       （5）范围－列表复合分区（range-list）。<br /> <br />这里主要介绍range分区和list分区：<br />**Range分区：**<br />以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。<br />    如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。<br />    在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区（不是必须创建的），所有不在指定范围内的记录都会被存储到maxvalue所在分区中。<br />同时可以为每个分区指定表空间（不指定则为当前用户的默认表空间），各分区的表空间可以相同也可以不同。<br /> <br />如：<br />create table pdba (id number, time date) partition by range (time)<br />(<br />partition p201010 values less than (to_date('2010-10-1', 'yyyy-mm-dd')) tablespace tablespace01,<br />partition p201011 values less than (to_date('2010-11-1', 'yyyy-mm-dd')) tablespace tablespace01,<br />partition p201012 values less than (to_date('2010-12-1', 'yyyy-mm-dd')) tablespace tablespace02,<br />partition pother  values less than (maxvalue) tablespace tablespace03<br />)<br />**List<br />　　List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。<br />　　在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。<br /> <br />在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。<br />如：<br />create table custaddr<br />(<br />  id         varchar2(15)   not null,<br />  areacode   varchar2(4)<br /> )<br />partition  by  list (areacode)<br />( partition t_list025 values ('025'), <br /> partition t_list372 values ('372'),<br /> partition t_list510 values ('510'),<br />partition p_other values (default)<br />)<br /> <br /> 

二、普通表转分区表方法

将普通表转换成分区表有4种方法：
1. Export/import method
导入导出方法
2. Insert with a subquery method
插入法
3. Partition exchange method
交换分区法
4. DBMS_REDEFINITION
在线重定义

由于我们需要改造的表都是数据量非常大的，且为了便于操作和将业务影响减小到最低，所以建议采用导入导出法或在线重定义。下面重点介绍这两种方法：

Export/import method：

1) 导出表:
exp user/password@server tables=tablename file=exp.dmp

2) 删除原表:
drop table tablename;

3) 重建分区表:
CREATE TABLE tablename (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(PARTITION p200501 VALUES LESS THAN (TO_DATE(‘01/01/2005’, ‘DD/MM/YYYY’)),
PARTITION p200502 VALUES LESS THAN (TO_DATE(‘01/02/2005’, ‘DD/MM/YYYY’)),
PARTITION pother VALUES LESS THAN (MAXVALUE));

4) 导入表:
imp user/password@server file=exp.dmp ignore=y

（a.如果导入的用户相同，可以将原表先RENAME TO另一个表名，将分区表建好后导入，核查无误后再将原表删除；
b.如果导入的用户不同，则可先不删除原表，直接导入另一用户下：
imp user/password@server tables=tablename file=exp.dmp fromuser=hd40 touser=hdreport ignore=y
这样可以与原表数据核查无误后，再将原表删除，将新建表赋权给hd40用户。）

5) 最后检查下数据是否一致，检查约束和索引，如没有则需重建。
检查索引状态：
select index_name,table_name,status from user_indexes;

DBMS_REDEFINITION：

ORACLE 9i开始提供了在线重定义的功能，在线重定义可以保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

在线重定义需要注意的几个限制条件：
必须准备两倍表大小的空间；
必须在同一用户下操作，且不能用sys用户。

在线重定义的大致操作流程如下：
（1）创建基础表A，如果存在，就不需要操作。
（2）创建临时的分区表B。
（3）开始重定义，将基表A的数据导入临时分区表B。
（4）结束重定义，此时在数据库的数据字典里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。


示例：
1. 我们要改造的基本表：
CREATE TABLE unpar_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
);

ALTER TABLE unpar_table ADD (
CONSTRAINT unpar_table_pk PRIMARY KEY (id)
);

CREATE INDEX create_date_ind ON unpar_table(create_date);

2. 收集表的统计信息
EXEC DBMS_STATS.gather_table_stats(USER, ‘unpar_table’, cascade => TRUE);

3. 创建临时分区表
CREATE TABLE par_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(PARTITION p200501 VALUES LESS THAN (TO_DATE(‘01/01/2005’, ‘DD/MM/YYYY’)),
PARTITION p200502 VALUES LESS THAN (TO_DATE(‘01/02/2005’, ‘DD/MM/YYYY’)),
PARTITION pother VALUES LESS THAN (MAXVALUE));

4. 进行重定义操作
4.1 检查重定义的合理性
EXEC Dbms_Redefinition.can_redef_table(USER, ‘unpar_table’);
(
PS:默认是按主键的方式进行重定义，如果没有主键，则需要设定参数为2，按照rowid重定义
EXEC Dbms_Redefinition.can_redef_table(USER, ‘unpar_table’,2);
)

4.2 如果4.1 没有问题，开始重定义，这个过程可能需要比较长的时间。
EXEC DBMS_REDEFINITION.start_redef_table( USER, ‘unpar_table’, ‘par_table’);
(
ROWID方式：
EXEC DBMS_REDEFINITION.start_redef_table( USER, ‘unpar_table’, ‘par_table’,null,2);
)
这一步操作结束后，数据就已经同步到这个临时的分区表里来了。

4.3 同步新表，这是可选的操作
Exec dbms_redefinition.sync_interim_table( USER, ‘unpar_table’, ‘par_table’);

4.4 创建主键和索引。
ALTER TABLE par_table ADD (
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX create_date_ind2 ON par_table(create_date);

4.5 收集新表的统计信息
EXEC DBMS_STATS.gather_table_stats(USER, ‘par_table’, cascade => TRUE);

4.6 结束重定义
Exec dbms_redefinition.finish_redef_table( USER, ‘unpar_table’, ‘par_table’);
此时基表unpar_table 和临时分区表par_table 已经进行了交换。 此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。
我们在重定义的时候，基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

5. 删除临时表
DROP TABLE par_table;

6. 主键和索引重命名
ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

三、创建存储过程自动新建分区

为了不每次都手工去增加分区，所以可以将新建分区的动作写进存储过程，通过JOB每月自动调用。
（此过程仅指定但不维护表空间，需人工定期维护表空间避免表空间不足）

标准示例：

分区的命名规则为pyyyymm，例：p201108。
分区的范围条件为yyyymm01，例：values less than(TO_DATE(‘2011-08-1’, ‘YYYY-MM-DD’))
即：p201108 存放 2011-07的数据

每次提前两个月创建新分区和新数据文件，
即：2011-05创建p201108，里面存放2011-07的数据

create or replace procedure AUTO_ADD_PARTITION(pidate date)
IS

vmsg varchar2(1000);
vdate date;
vmax date; — 目前最大分区日期
vnext date; — 要创建的分区日期
vnum varchar2(10); —日期转分区号
vcmd varchar2(500);

CURSOR C IS select DISTINCT TABLE_NAME,TABLESPACE_NAME
from dba_tab_partitions
where table_owner <> ‘SYS’
and table_owner <> ‘SYSTEM’;
—此处要注意当前用户是否有表dba_tab_partitions的select权限
begin
vdate := pidate;
vnext := add_months(last_day(trunc(vdate)) + 1, 2);

FOR R IN C LOOP
select max(to_date(substr(partition_name, 2), ‘yyyymm’))
into vmax
from dba_tab_partitions
where table_name = R.TABLE_NAME;

if vnext <= vmax then
insert into hd_log
(time, oper, modulename, type, content)
values
(sysdate, ‘添加分区’, ‘添加’||R.TABLE_NAME||’分区’, 101, ‘未到添加时间，不需添加。’);
commit;
else
vnum := to_char(vnext, ‘yyyymm’);
vcmd := ‘alter table ‘||R.TABLE_NAME||’ add partition p’||vnum|| ‘ values less than
(to_date(‘’’||VNum||’’’,’’yyyymm’’)) tablespace ‘||R.TABLESPACE_NAME||’ ‘;
execute immediate vcmd;
insert into hd_log
(time, oper, modulename, type, content)
values
(sysdate,’添加分区’,’添加分区’,101,’添加’||R.TABLE_NAME||’分区结束’);
commit;
end if;
END LOOP;
exception
when others then
begin
rollback;
vmsg := ‘添加分区出错，错误号:’ || SQLCODE || ‘错误信息：’ || SQLERRM;
insert into hd_log
(time, oper, modulename, type, content)
values
(sysdate,’添加分区’,’添加分区出错’,304,substrb(vmsg, 1, 254));
commit;
end;
end;

四、分区表的其他操作

1.
添加新的分区有2中情况：
（1）原分区里边界是maxvalue或者default。 这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。 或者采用split，对边界分区进行拆分。
（2）没有边界分区的。 这种情况下，直接添加分区就可以了。

给SALES表添加一个P200306分区:
ALTER TABLE SALES ADD PARTITION P200306 VALUES LESS THAN(TO_DATE(‘2003-06-01’,’YYYY-MM-DD’));

2.
ALTER TABLE SALES DROP PARTITION P200306;

3.
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
ALTER TABLE SALES TRUNCATE PARTITION P200306;

4.
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区：
ALTER TABLE SALES MERGE PARTITIONS P200305 ,P200306 INTO PARTITION P200306;

5.
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P200306 AT (TO_DATE(‘2003-05-15’,’YYYY-MM-DD’)) INTO (PARTITION P20030601,PARTITION P20030602);

6.移动分区
alter table tablename move partition P200306 tablespace tablespacename;

7.
ALTER TABLE SALES RENAME PARTITION P200206 TO P200306;

注意，在对分区做了除重命名之外的动作后，都需要检查索引状态是否有效，如果失效则需要重建。
检查索引状态：
select index_name,table_name,status from user_indexes;

五、分区表的相关查询

如果不指定分区查询，oracle也会自动根据查询条件进行分区筛选。

指定分区查询
Select from table partition (P201108);

显示所有分区表的信息：
select from DBA_PART_TABLES
显示当前用户可访问的所有分区表信息:
select from ALL_PART_TABLES
显示当前用户所有分区表的信息：
select from USER_PART_TABLES

显示表分区信息 显示数据库所有分区表的详细分区信息：
select from DBA_TAB_PARTITIONS
显示当前用户可访问的所有分区表的详细分区信息：
select from ALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS