关键词:
作者简介
- 作者:LuciferLiu,中国DBA联盟(ACDU)成员。
- 目前主要从事Oracle DBA工作,曾从事 Oracle 数据库开发工作,主要服务于生产制造,汽车金融等行业。
- 现拥有Oracle OCP,OceanBase OBCA认证,擅长Oracle数据库运维开发,备份恢复,安装迁移,Linux自动化运维脚本编写等。
前言
为什么要普通表转分区表?有哪些方式可以做?
- 分区表作为Oracle三大组件之一,在Oracle数据库中,起着至关重要的作用。
分区表有什么优点?
- 普通表转分区表:应用程序无感知,DML 语句无需修改即可访问分区表。
- 高可用性:部分分区不可用不影响整个分区表使用。
- 方便管理:可以单独对分区进行DDL操作,列入重建索引或扩展分区,不影响分区表的使用。
- 减少OLTP系统资源争用:因为DML分布在很多段上进行操作。
使用在线重定义的方式进行分区表的转换,优势在于可以在线进行,流程简单,可以快速进行转换。
一、介绍
DBMS_REDEFINITION(在线重定义):
- 支持的数据库版本:Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
- 在线重定义是通过 物化视图 实现的。
使用在线重定义的一些限制条件:
- 必须有足够的表空间来容纳表的两倍数据量。
- 主键列不能被修改。
- 表必须有主键。
- 必须在同一个用户下进行在线重定义。
- SYS和SYSTEM用户下的表无法进行在线重定义。
- 在线重定义无法采用nologging。
- 如果中间表有新增列,则不能有NOT NULL约束
DBMS_REDEFINITION包:
- ABSORT_REDEF_TABLE:清理重定义的错误和中止重定义;
- CAN_REDEF_TABLE:检查表是否可以进行重定义,存储过程执行成功代表可以进行重定义;
- COPY_TABLE_DEPENDENTS:同步索引和依赖的对象(包括索引、约束、触发器、权限等);
- FINISH_REDEF_TABLE:完成在线重定义;
- REGISTER_DEPENDENTS_OBJECTS:注册依赖的对象,如索引、约束、触发器等;
- START_REDEF_TABLE:开始在线重定义;
- SYNC_INTERIM_TABLE:增量同步数据;
- UNREGISTER_DEPENDENT_OBJECT:不注册依赖的对象,如索引、约束、触发器等;
二、实战
测试环境数据库安装:
- 11G:./OracleShellInstall.sh -i 10.211.55.111
更多更详细的脚本使用方式可以订阅专栏:Oracle一键安装脚本。
脚本获取方式:
1 构建测试数据
创建测试表空间和用户:
sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;
创建测试表:
sqlplus par/par
create table lucifer(
id number(8) PRIMARY KEY,
name varchar2(20) not null,
par_date date)
tablespace PAR;
comment on table lucifer is 'lucifer表';
comment on column lucifer.name is '姓名';
comment on column lucifer.par_date is '分区日期';
create index id_name on lucifer(name) tablespace par;
插入测试数据:
sqlplus par/par
begin
for i in 0 .. 24 loop
insert into lucifer values
(i,
'lcuifer_' || i,
add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
可以看到,测试数据已经构建完成,接下来开始实战操作。
2 查看是否能够重定义
需提前确认表是否有主键,表空间是否足够:
sqlplus / as sysdba
##查看主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'LUCIFER';
确认是否可以重定义,没有主键用rowid:
sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');
执行没有报错代表可以进行表的在线重定义。
3 创建中间表(分区表结构)
通过PL/SQL包一键生成分区表结构:
sqlplus par/par
BEGIN
ctas_par(p_tab => 'lucifer',
p_part_colum => 'par_date',
p_part_nums => 24,
p_tablespace => 'par');
END;
/
注意:PL/SQL包可参考:Oracle普通表按月转分区表,通过PLSQL包一键生成分区表
创建中间分区表lucifer_par:
create table lucifer_par
(
id NUMBER(8),
name VARCHAR2(20),
par_date DATE
)
partition BY RANGE(par_date)(
partition lucifer_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_MAX values less than (maxvalue) tablespace par)
enable row movement
tablespace par;
如上,唯一索引和约束不加,会自动复制,分区表结构的中间表已经生成。
4 检查中间表是否开启行迁移
select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR';
select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR';
5 收集表统计信息
为了确保数据准确,开始前进行统计信息收集:
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
6 开始在线重定义
sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');
7 复制表属性,排除索引
选择自动复制表属性,手动创建本地索引(local)
- 优点:只需要关注索引是否遗漏,无需关注触发器,权限,约束等依赖。
- 缺点:需要手动创建索引,并且结束后手动rename索引。
可参考:Oracle在线重定义之COPY_TABLE_DEPENDENTS
sqlplus par/par
SET SERVEROUTPUT ON
DECLARE
l_errors NUMBER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => USER,
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR',
copy_indexes => 0,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_errors,
copy_statistics => FALSE,
copy_mvlog => FALSE);
DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
执行过程没有任何报错,代表正常。
8 中间表创建本地索引
中间表LUCIFER_PAR创建索引:
create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;
注意:索引名称需要与原索引名称不一致。
9 取消索引并行度
如果创建索引时,开启并行创建,则需要取消索引并行度:
sqlplus / as sysdba
select 'alter index '||owner||'.'||index_name||' noparallel;'
from dba_indexes
where table_name = 'LUCIFER_PAR' and owner= 'PAR';
10 同步数据(可以减少结束重定义过程的锁表时间)
sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
注意:这一步操作是为了在结束重定义的时候,减少锁表的时间。
11 收集中间表统计信息
为了下面同步数据做准备,收集中间表统计信息:
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
12 结束重定义(结束重定义需要锁表,具体时间根据表的大小决定)
sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
13 查看分区表是否已转换
sqlplus par/par
select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR');
如上,LUCIFER表已经在线重定义为分区表结构。
14 手动修改重命名索引
1、此时,原表名的表已经转换为中间表,需要先将原表的索引,rename到其他名字,本次是BAK,需要注意索引名称长度不能过长
sqlplus / as sysdba
ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;
2、rename新分区表索引,由于新分区表的索引名称还是中间表的索引名称,所以需要手动rename
sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;
15 查看是否存在无效索引
sqlplus / as sysdba
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE';
16 检查切换后是否开启row_movement
sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR') and owner='PAR';
17 检查无效对象
##无效对象编译
sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects t
where t.status = 'INVALID' order by 1;
18 收集统计信息
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
19 插入测试数据
sqlplus par/par
begin
for i in 100 .. 124 loop
insert into lucifer values
(i,
'lcuifer_' || i,
add_months(to_date('2021-5-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
20 查询分区表数据分布
sqlplus par/par
SELECT COUNT(*) FROM LUCIFER;
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202101);
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202201);
SELECT * FROM LUCIFER PARTITION(LUCIFER_MAX);
可以发现,数据已经根据日期均匀分布在不同的子分区中。至此,在线重定义已经完成,分区表已成功转换。
参考MOS文档:
- How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)
- Oracle在线重定义之COPY_TABLE_DEPENDENTS
本次分享到此结束啦~
如果觉得文章对你有帮助,点赞、收藏、关注、评论,一键四连支持,你的支持就是我创作最大的动力。
mysql必知必会(初级篇)(代码片段)
mysql1.基本概念2.SQL语言2.1DCL(数据控制语言)2.1.1创建用户2.1.2使用grant命令给用户授权2.1.3使用revoke命令撤销权限2.2DDL(数据定义语言)2.2.1mysql常用约束类型2.2.2使用alter命令修改表结构2.2.3使用drop命令删除表2.3DML(数据操纵语言)2.3.1使... 查看详情
hive必知必会(代码片段)
hive: 基于hadoop,数据仓库软件,用作OLAPOLAP:onlineanalyzeprocess 在线分析处理OLTP:onlinetransactionprocess在线事务处理 事务: ACID A:atomic 原子性 C:consistent 一致性 I:isolation 隔离性 D:durability 持久性 1读未提交 脏读 //事务... 查看详情
scala必知必会(代码片段)
文章目录入门概述安装JavaVSScalaval和var基本数据类型lazy在Scala中的应用开发工具IDEAMaven函数方法定义默认参数命名参数可变参数条件语句循环语句面向对象概述类的定义和使用抽象类伴生类和伴生对象case和trait集合数组ListSetMapOpt... 查看详情
linux编程之信号篇:异常监控必知必会
文章目录为什么要了解信号什么是信号信号有哪些如何发送/捕获信号发送killraisekillpgsigqueue监听signalsigaction信号处理函数的注意事项信号阻塞总结Thanks为什么要了解信号信号是UNIX系统进程管理非常重要的一环,下面这些场景都... 查看详情
必知必会-使用kafka之前要掌握的知识(代码片段)
必知必会系列之kafka前记kafka特性kafka实现顺序写高速读概念介绍分区和分组队列还是分发消费方式API前记消息队列是分布式系统架构中不可或缺的基础组件,它主要负责服务间的消息通信和数据传输。市面上有很多的开源消... 查看详情
mysql学习--mysql必知必会(代码片段)
?上图为数据库操作分类:??下面的操作參考(mysql必知必会)创建数据库运行脚本建表:mysql>createdatabasemytest;QueryOK,1rowaffected(0.07sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|infor 查看详情
大数据必知必会的-linux命令(代码片段)
文件操作命令touch命令touch命令创建文件touch文件路径toucha.txt#在当前目录创建a.txt文件touch/root/a.txt#在/root目录创建a.txt文件mv命令通过mv命令可以用来移动文件或目录,也可以给文件或目录重命名1)将文件移动另一个目录中... 查看详情
customview必知必会——问题清单
目录第1章(绘图基础)一、基本图形绘制二、路径三、文字四、Region五、Canvas(画布)六、控件的使用方法第2章(视图动画)一、视图动画标签二、视图动画的代码实现三、插值器初探四、动画示例五、逐帧动画第... 查看详情
必知必会面试10多家中大厂后的两万字总结——❤️jvm篇❤️(建议收藏)(代码片段)
⭐欢迎订阅《技术专家修炼》专栏,带你搞技术,进大厂,聊人生⭐🌲往期回顾📚面试10多家中大厂后的万字总结——❤️Java基础篇❤️📚面试10多家中大厂后的万字总结——❤️JavaWeb篇❤️📚面试... 查看详情
es6必知必会——generator函数(代码片段)
Generator函数1.Generator函数是ES6提供的一种异步编程解决方案,语法行为与传统函数完全不同,通常有两个特征:function关键字与函数名之间有一个星号;函数体内部使用yield表达式,定义不同的内部状态//一个简单的Generator函数func... 查看详情
必知必会
1什么是MySQL MySQL是一个关系型数据库管理系统,属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件之一。在J... 查看详情
必知必会的设计原则——合成复用原则(代码片段)
设计原则系列文章 必知必会的设计原则——单一职责原则必知必会的设计原则——开放封闭原则必知必会的设计原则——依赖倒置原则必知必会的设计原则——里氏替换原则必知必会的设计原则——接口隔离原则必知必... 查看详情
hadoop必知必会——重要部分整理
上一篇的传送门:Hadoop必知必会——重要部分整理(一)一、HDFS在读文件时,如果一个块突然坏了客户端读取完DataNode上的块之后会进行checksum验证,就是把客户端读取到本地的块与HDFS上的原始块进行校验ÿ... 查看详情
mysql必知必会(代码片段)
姊妹篇——Hive必知必会(数据仓库):https://hiszm.blog.csdn.net/article/details/119907136文章目录第一章:数据库基础基本概念什么是SQL第二章:MySQL简介第三章:了解数据库和表第四章:检索数据SELECT语句第五章:... 查看详情
crypto必知必会(代码片段)
crypto必知必会最近参加了个ctf比赛,在i春秋,南邮方面刷了一些crypto密码学题目,从中也增长了不少知识,在此关于常见的密码学知识做个小总结!Base编码Base编码中用的比较多的是base64,首先就说一下Base64编码方式将字符串以... 查看详情
泥瓦匠聊并发编程:线程与多线程必知必会(基础篇)
本文目录线程与多线程线程的运行与创建线程的状态1线程与多线程线程是什么?线程(Thread)是一个对象(Object)。用来干什么?Java线程(也称JVM线程)是Java进程内允许多个同时进行的任务。该进程内并发的任务成为线程(Thr... 查看详情
h5系列之history(必知必会)(代码片段)
H5系列之History(必知必会)目录概念兼容性属性方法H5方法概念理解HistoryApi的使用方式目的是为了解决哪些问题作用:ajax获取数据时,可以改变历史记录,从而可以使用浏览器的后退和前进。【】规范地址:http://www.w3.org/TR/html5... 查看详情
springmvc--必知必会(代码片段)
SpringMVC基于模型--视图--控制器(Model-View-Controller,MVC)模式实现,属于SpringFrameWork的后续产品,已经融合在SpringWebFlow里面。它通过一套注解,让一个简单的Java类成为处理请求的控制器,而无需实现任何接口。同时它还支持... 查看详情