Oracle19C将普通表转为分区表

当表数据量过大我们常常想到分库、分表但如果将一张普通的表转为分区表呢其实在数据库初始阶段就应该考虑到针对一些数据生成频繁容易产生大量数据的表进行存储结构上的处理后期对大数据表的变更也是具备大量工作评估与风险的本文以Oracle19为例简单分为以下几步。整体操作流程概览整个过程可以分为五个主要阶段评估与准备分析原表设计分区策略。创建中间表建立一个结构相同但已分区的临时表。启动重定义使用DBMS_REDEFINITION开始在线转换。同步与收尾同步增量数据完成对象切换。验证与清理确认数据一致性和业务正常后清理旧对象。步骤一评估与准备在开始之前充分的准备是成功的关键。分析原表确认表的主键、索引、约束和触发器。设计分区策略分区键选择一个高频用于查询的日期字段如create_time。分区类型对于日期通常使用范围分区RANGE。分区粒度根据数据量和访问模式决定按月、按季度还是按年分区。Oracle 19c 支持INTERVAL分区可以自动创建新分区非常方便。权限检查确保执行操作的数据库用户拥有EXECUTE权限在DBMS_REDEFINITION包上。数据备份至关重要在进行任何重大变更前务必对原表进行完整备份。可重定义性检查使用DBMS_REDEFINITION.CAN_REDEF_TABLE过程检查原表是否支持在线重定义。-- 如果没有报错则表示可以进行下一步 CALL DBMS_REDEFINITION.CAN_REDEF_TABLE(YOUR_SCHEMA, YOUR_ORIGINAL_TABLE);对于 Oracle 分区表业界并没有一个绝对的“黄金数字”但根据生产环境的最佳实践和硬件性能有一个公认的“舒适区间”。一般来说建议单个分区的数据量控制在1000 万 ~ 5000 万行之间或者物理大小控制在2GB ~ 10GB之间。以下是详细的评估维度和建议1. 核心建议指标你可以参考下表来决定你的分区粒度维度建议范围说明行数1000 万 ~ 5000 万行这是最通用的标准。低于 1000 万可能导致分区过多管理困难高于 5000 万则查询和维护性能开始下降。物理大小2GB ~ 10GB单个分区文件不宜过大否则在备份、恢复或移动表空间时会非常耗时。分区总数 1000 个一张表的分区总数建议不要超过 1000 个。如果数据量极大应增大单个分区的容量而不是无限增加分区数量。2. 为什么要控制在这个范围这个范围是查询性能与运维效率之间的平衡点如果分区太小例如 100万行管理噩梦分区数量过多会导致数据字典膨胀解析 SQL 时进行“分区裁剪”的计算成本变高。索引碎片过多的分区可能导致索引碎片化影响整体性能。如果分区太大例如 1亿行查询变慢如果查询无法精准定位例如全表扫描或范围查询跨越分区扫描 1 亿行数据的开销巨大。维护困难删除一个包含 1 亿行数据的分区或者重建该分区的索引可能会导致长时间锁表或 Undo 表空间溢出。3.如何监控查看进状态进度在 Oracle 19c 中监控DBMS_REDEFINITION进度最准确、最直接的方法是查询系统自带的V$SESSION_LONGOPS视图。这个视图专门用于跟踪各种长时间运行的操作在线重定义就包含在内。 如何监控在线重定义进度你可以使用以下 SQL 语句来实时查看重定义的进度它就像一个“进度条”会告诉你完成了多少还剩多少。SELECT OPNAME, SOFAR, TOTALWORK, UNITS, ROUND(SOFAR / TOTALWORK * 100, 2) AS PROGRESS_PERCENT, TIME_REMAINING, ELAPSED_SECONDS FROM V$SESSION_LONGOPS WHERE OPNAME LIKE %Table Redefinition% AND TOTALWORK 0 AND SOFAR TOTALWORK;关键字段解读OPNAME: 操作名称会显示为类似Table Redefinition: schema_name.table_name的信息。SOFAR: 到目前为止已完成的工作量。TOTALWORK: 总的预估工作量。UNITS: 工作量的单位通常是Blocks数据块。PROGRESS_PERCENT: 我们计算出的完成百分比这是最直观的指标。TIME_REMAINING: 预估的剩余时间秒Oracle 会根据当前速度动态计算。⚙️ 补充如何查看重定义状态除了监控进度你还可以查询DBA_REDEFINITION_OBJECTS来确认重定义任务的状态。SELECT OWNER, OBJECT_NAME, TYPE, STATUS FROM DBA_REDEFINITION_OBJECTS;这个视图不会告诉你完成了百分之几但可以告诉你当前重定义是处于ONGOING进行中、FINISHED已完成还是其他状态。这个视图告诉你重定义任务当前处于什么状态。SELECT OWNER, ORIGINAL_TABLE, INTERIM_TABLE, OPERATION_TYPE, STATUS, ERROR_MSG FROM DBA_REDEFINITION_STATUS;关键字段解读STATUS这是最重要的字段常见值包括ONGOING重定义正在进行中最常见状态。FINISHEDFINISH_REDEF_TABLE已成功执行重定义完成。UNFINISHEDSTART执行了但还没FINISH。OPERATION_TYPE显示当前正在执行的操作如START、SYNC或FINISH。ERROR_MSG如果操作失败这里会显示错误信息。建议在操作过程中同时打开两个窗口窗口 A查DBA_REDEFINITION_STATUS确认任务是否还在运行有没有报错。确认当前是在START阶段还是FINISH阶段。窗口 B查V$SESSION_LONGOPS盯着PROGRESS_PERCENT看进度例如从 10% 涨到 50%。盯着TIME_REMAINING估算还需要多久。步骤二创建中间分区表创建一个与原表结构完全相同但已定义为分区表的“中间表”。CREATE TABLE your_interim_table ( id NUMBER, create_time DATE, other_column VARCHAR2(100), -- ... 其他所有列 ... ) PARTITION BY RANGE (create_time) INTERVAL (NUMTOYMINTERVAL(1, MONTH)) -- 自动按月创建新分区 --INTERVAL (NUMTOYMINTERVAL(1, YEAR)) -- 自动按年创建新分区 ( PARTITION p_initial VALUES LESS THAN (TO_DATE(2024-01-01, YYYY-MM-DD))--初始化一个历史数据分区 ); ALTER TABLE your_interim_table ADD CONSTRAINT pk_interim PRIMARY KEY (id, create_time) -- 注意主键必须包含分区键注意分区表的主键和唯一索引必须包含分区键Oracle中全局唯一索引也可以但性能效率较低数据需要扫描表的所有分区。就像你开了多家分店分店各自管理货物不需要关心其它店的货物情况只需要关心自己的店里有没有肯定会更快更方便。如果原表主键不包含日期字段这是一个需要解决的难点可能需要修改应用逻辑或采用更复杂的方案。步骤三启动在线重定义这是核心步骤它将启动后台进程开始将原表数据复制到中间表并捕获期间的增量变化BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( uname YOUR_SCHEMA, -- schema名 orig_table YOUR_ORIGINAL_TABLE, -- 原表名 int_table YOUR_INTERIM_TABLE, -- 中间表名 options_flag DBMS_REDEFINITION.CONS_USE_ROWID -- 通常使用ROWID方式 ); END;当数据量巨大建议开启多线程处理操作执行之前开启多线程支持--多线程模式 -- 1. 设置合理的并行度关键防止内存溢出 -- 建议先设为 2 或 4观察系统负载最好不要超过CPU核心数防止过载 ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4; ALTER SESSION FORCE PARALLEL DML PARALLEL 4; ALTER SESSION ENABLE PARALLEL DML; -- 2. 执行 START_REDEF_TABLE -- 注意options_flag 参数如果是按主键重定义使用 dbms_redefinition.cons_use_pk BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( uname YOUR_SCHEMA, -- schema名 orig_table YOUR_ORIGINAL_TABLE, -- 原表名 int_table YOUR_INTERIM_TABLE, -- 中间表名 --options_flag DBMS_REDEFINITION.CONS_USE_PK, -- 使用主键 options_flag DBMS_REDEFINITION.CONS_USE_ROWID -- 通常使用ROWID方式 ); END;PARALLEL值并行度DOP的设定与服务器 CPU 核心数有非常直接且紧密的关系。在 Oracle 19c 中设置不当过高或过低都会导致性能问题甚至导致你之前遇到的“服务器死机”情况。以下是关于如何设置PARALLEL值的详细建议1. 核心关系计算公式Oracle 的默认并行度计算逻辑通常基于以下公式建议并行度CPU 核心数×每个 CPU 的线程数×节点数建议并行度CPU 核心数×每个 CPU 的线程数×节点数CPU 核心数物理核心数量。每个 CPU 的线程数由参数PARALLEL_THREADS_PER_CPU控制Oracle 19c 默认通常是2。节点数如果是 RAC 集群则是节点数量单机则为 1。举例如果你的服务器是单机拥有16 核CPU。默认并行度计算16×2×13216×2×132 。2. 针对场景在线重定义的建议值虽然理论计算值是 32但在执行DBMS_REDEFINITION这种高负载操作时不建议直接拉满。推荐策略保守设置对于在线重定义START_REDEF_TABLE建议将并行度设置为CPU 核心数的 1 到 2 倍或者直接指定一个固定的较小值。建议值4 或 8原因避免内存溢出PGA并行度越高启动的进程越多每个进程都会消耗 PGA 内存。之前服务器死机很可能就是因为并行度过高如默认的 32 或更高瞬间吃光了内存。预留资源给业务在线重定义期间业务还在运行。如果重定义占用了所有 CPU 资源业务查询会变慢甚至超时。IO 瓶颈过高的并行度会导致大量的随机 IO如果磁盘阵列扛不住系统会卡在 IO 等待上。如果你不确定服务器有多少核可以运行以下 SQL 查看-- 查看 CPU 核心数 SHOW PARAMETER cpu_count; -- 查看每个 CPU 的线程数 SHOW PARAMETER parallel_threads_per_cpu;步骤四同步数据并完成重定义START_REDEF_TABLE之后大部分数据已经开始复制。接下来需要手动同步在重定义过程中产生的增量数据并最终完成切换。可选同步增量数据如果数据量巨大可以在最终切换前手动同步一次以减少最终切换时的停机时间。CALL DBMS_REDEFINITION.SYNC_INTERIM_TABLE(YOUR_SCHEMA, YOUR_ORIGINAL_TABLE, YOUR_INTERIM_TABLE);--复制原表的相关定义对象未执行此步骤后续转表完成后手动单独根据所需进行调整亦可 DECLARE l_num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( uname YOUR_SCHEMA, orig_table YOUR_ORIGINAL_TABLE, int_table YOUR_INTERIM_TABLE, copy_indexes 1, -- 复制索引 copy_triggers TRUE, -- 复制触发器 copy_constraints TRUE, -- 复制约束 copy_privileges TRUE, -- 复制权限 ignore_errors FALSE, -- 遇到错误是否忽略 num_errors l_num_errors, -- 输出错误数量 copy_statistics FALSE -- 是否复制统计信息 ); DBMS_OUTPUT.PUT_LINE(复制依赖对象产生的错误数: || l_num_errors); END;完成重定义此步骤会短暂锁表将最后的增量数据同步过来并完成对象的重命名和切换。这是整个过程中对业务影响最大的时刻但通常非常短暂。(建议低业务期/暂停业务执行数据量越大时长越长)BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname YOUR_SCHEMA, orig_table YOUR_ORIGINAL_TABLE, int_table YOUR_INTERIM_TABLE, dml_lock_timeout 60--获取锁的等待时长秒避免无限期等待 ); END;执行后原表YOUR_ORIGINAL_TABLE与中间表YOUR_INTERIM_TABLE会进行相互置换对表名进行重命名原表重命名为中间表中间表重命名为原表重命名置换过程会对整个表对象上锁。虽然DBMS_REDEFINITION号称“在线重定义”但在最后执行FINISH_REDEF_TABLE的那一瞬间它并不是完全无锁的。针对你提到的上亿数据量且耗时几分钟的情况这里为你详细拆解锁表的具体行为和原因1. 锁表的机制它是如何阻塞的FINISH_REDEF_TABLE的过程可以理解为“最后的冲刺”它的执行逻辑如下最后一次同步将原表orig_table中剩余的、尚未同步到中间表int_table的增量数据DML操作应用到中间表。获取排他锁关键步骤为了完成表名的交换即把原表改名/删除把中间表改成原表名数据库必须保证在这一瞬间没有任何人在修改原表。元数据交换快速交换对象定义。释放锁。阻塞点在于第2步如果此时有事务正在对原表进行INSERT、UPDATE或DELETE操作且未提交FINISH_REDEF_TABLE就会进入等待状态直到这些事务结束。步骤五重建索引与验证DBMS_REDEFINITION不会自动迁移索引。你需要为新的分区表重建索引。重建索引本地索引 (Local Index)如果索引包含分区键强烈建议创建为本地索引性能更好维护更方便。全局索引 (Global Index)如果索引不包含分区键则只能创建为全局索引。-- 示例创建一个本地分区索引 CREATE INDEX idx_create_time ON your_original_table(create_time) LOCAL; -- 示例创建一个全局索引 CREATE INDEX idx_other_column ON your_original_table(other_column) GLOBAL;验证检查分区是否按预期创建SELECT * FROM user_tab_partitions WHERE table_name YOUR_ORIGINAL_TABLE;验证数据总量SELECT COUNT(*) FROM your_original_table;与原表记录数对比。进行业务功能测试确保应用访问正常。⚠️ 关键注意事项与避坑指南主键约束这是最常见的“坑”。分区表的主键或唯一键必须包含分区键。如果原表不满足需要提前规划好解决方案。索引策略务必在转换后重建索引。本地索引优于全局索引。资源消耗在线重定义会消耗额外的CPU、内存和I/O资源建议在业务低峰期执行。回滚方案在FINISH_REDEF_TABLE之前如果出现问题可以调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来中止操作原表不受影响。一旦完成回滚将非常困难因此前期的备份和测试至关重要。依赖对象检查是否有视图、存储过程等依赖该表确保它们不受影响。 补充知识DBMS_REDEFINITION.FINISH_REDEF_TABLE是如何获取同步增量数据的锁表是怎么样的简单来说它通过查询内部捕获的日志来获取增量数据并且确实会锁表但锁表的时间理论上应该很短。以下是深度的技术解析1. 它是如何获取同步增量数据的在START_REDEF_TABLE执行后Oracle 就已经开始“暗中”记录原表的变更了。FINISH_REDEF_TABLE并不是在这个时候才去扫描全表而是“读取”这段时间积累的变更日志。捕获机制幕后英雄物化视图日志 (MView Log)如果你的原表开启了物化视图日志推荐Oracle 会直接读取MLOG$_表来获取自START以来的所有INSERT、UPDATE、DELETE记录。内部触发器如果没有物化视图日志Oracle 会在原表上自动创建临时的触发器将变更行写入到内部的中间队列中。FINISH 阶段的工作当调用FINISH_REDEF_TABLE时Oracle 会读取这些累积的日志/队列将最后一次增量同步之后产生的所有新变更应用到中间表int_table上。2. 会锁表吗锁的是什么锁会锁表。这是FINISH阶段最核心的动作也是导致阻塞的根源。锁的类型排他锁 (Exclusive Lock / X 锁)。这是数据库中最严格的锁。在持有该锁期间其他任何事务都不能对原表进行SELECT ... FOR UPDATE、INSERT、UPDATE或DELETE操作。锁表的目的为了保证数据的一致性和原子性。Oracle 需要确保在“交换名字”把原表换成中间表的那一微秒原表的数据是完全静止的没有半截事务在运行。3. 为什么你会遇到“几分钟”的阻塞理论上FINISH阶段的锁表时间应该极短毫秒级因为它只是做个元数据交换。但如果你遇到了几分钟的阻塞通常是因为以下两个原因A. 等待锁的获取最常见原因FINISH_REDEF_TABLE试图获取排他锁但被阻塞了。场景此时有一个长事务比如一个跑了10分钟的大批量更新正在原表上运行且未提交。结果FINISH进程会一直挂起等待状态直到那个长事务 Commit 或 Rollback。这段时间里新的 DML 操作会被排队阻塞看起来就像是“锁表了好几分钟”。B. 应用最后的增量数据数据量大在真正尝试加锁之前FINISH必须先应用完所有剩余的增量日志。场景如果在上一次SYNC_INTERIM_TABLE之后原表发生了海量的 DML 操作比如几百万行更新。结果Oracle 需要时间把这些操作“重放”到中间表。虽然这期间还没加排他锁业务还能写入但会占用大量系统资源CPU/IO导致最后的切换阶段被迫延后。4. 解决方案与最佳实践为了避免“一直阻塞”导致业务事故强烈建议采取以下措施总结FINISH阶段必须锁表才能完成切换但它会等待未提交的事务。你遇到的几分钟延迟大概率是在等待某个长事务结束或者是最后积压的增量数据太多导致处理缓慢。使用dml_lock_timeout参数救命稻草在 Oracle 12c 及以上版本务必在FINISH时设置超时时间。如果在规定时间内拿不到锁直接报错退出而不是无限等待。BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname YOUR_SCHEMA, orig_table YOUR_BIG_TABLE, int_table YOUR_INTERIM_TABLE, dml_lock_timeout 60 -- 尝试获取锁的超时时间为60秒 ); END;频繁执行SYNC_INTERIM_TABLE在START之后FINISH之前尽可能多手动执行几次SYNC。这样可以把增量数据分批处理掉确保最后FINISH时需要处理的数据量最小。检查长事务在执行FINISH前查询V$SESSION或V$TRANSACTION确保没有长时间运行的事务在操作该表。