当前位置： 首页 > news >正文

Oracle 在线重定义

news 2025/8/5 11:33:04

Oracle 在线重定义（Online Redefinition） 是一种功能，通过DBMS_REDEFINITION 包提供，允许DBA在不需要停止或显著影响数据库正常操作的情况下，对数据库表进行结构化修改。

可以实现的功能

  • 将表移动到其它表空间

  • 增加、修改或者删除表的字段

  • 将非分区表转换为分区表

  • 修改表的分区结构

  • 高水位线回收

  • 将普通表转换为索引组织表

测试数据

五千万条数据，数据文件test_tbs01.dbf,表空间test_tbs

将表移动到其它表空间

#创建新表空间
CREATE TABLESPACE new_tbs DATAFILE '/datafile/new_tbs01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;#给用户授权
ALTER USER TEST_USER QUOTA UNLIMITED ON NEW_TBS;#检查选择的表是否可以执行在线重定义
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER', 'TEST_TABLE');
END;
/#创建中间表（结构与原表一致，但指定新表空间）
CREATE TABLE test_user.test_table_int (id NUMBER PRIMARY KEY,name VARCHAR2(100) NOT NULL,created_date DATE,value NUMBER(10),comments VARCHAR2(500)
) TABLESPACE new_tbs;# 开始在线重定义（使用主键方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',options_flag => DBMS_REDEFINITION.CONS_USE_PK  -- 使用主键);
END;
/# 复制依赖对象（自动复制索引、约束）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers => TRUE,copy_constraints => TRUE,copy_privileges => TRUE,ignore_errors => TRUE,num_errors => num_errors);
END;
/# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/# 完成重定义（短暂锁表）# DBMS_REDEFINITION.FINISH_REDEF_TABLE 会执行flush shard pool 需要规避
alter session set events '10995 trace name context forever, level 2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/

增加、修改或者删除表的字段

操作：添加字段new_column VARCHAR2(50)，删除字段comments。

#检查选择的表是否可以执行在线重定义
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER', 'TEST_TABLE');
END;
/


# 创建中间表（添加新字段，删除旧字段）
CREATE TABLE test_user.test_table_int (id NUMBER PRIMARY KEY,name VARCHAR2(100) NOT NULL,created_date DATE,value NUMBER(10),new_column VARCHAR2(50)
) TABLESPACE test_tbs;


# 开始在线重定义（使用主键方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'TEST_USER', orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT',col_mapping  => 'id id, name name, created_date created_date, value value', options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/


# 复制依赖对象（自动复制索引、约束）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers => TRUE,copy_constraints => TRUE,copy_privileges => TRUE,ignore_errors => TRUE,num_errors => num_errors);
END;
/

# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/


# 完成重定义
BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/

将非分区表转换为分区表

#检查选择的表是否可以执行在线重定义
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER', 'TEST_TABLE');
END;
/


#创建中间表
CREATE TABLE test_user.test_table_int (id            NUMBER,name          VARCHAR2(100) NOT NULL,created_date  DATE NOT NULL, value         NUMBER(10),new_column    VARCHAR2(50),CONSTRAINT pk_test_table_int PRIMARY KEY (id, created_date) 
)
PARTITION BY RANGE (created_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),PARTITION p3 VALUES LESS THAN (MAXVALUE)
) TABLESPACE test_tbs;

# 开始在线重定义（使用主键方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'TEST_USER',orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT',col_mapping  => 'id id, name name, created_date created_date, value value, new_column new_column',  options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/


-- 复制依赖对象（自动复制索引、约束）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers => TRUE,copy_constraints => TRUE,copy_privileges => TRUE,ignore_errors => TRUE,num_errors => num_errors);
END;
/


# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/


# 完成重定义# DBMS_REDEFINITION.FINISH_REDEF_TABLE 会执行flush shard pool 需要规避
alter session set events '10995 trace name context forever, level 2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/


#创建新表空间
CREATE TABLESPACE new_tbs DATAFILE '/datafile/new_tbs01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;#给用户授权
ALTER USER TEST_USER QUOTA UNLIMITED ON NEW_TBS;#检查选择的表是否可以执行在线重定义
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER', 'TEST_TABLE');
END;
/#创建中间表（结构与原表一致，但指定新表空间）
CREATE TABLE test_user.test_table_int (id NUMBER PRIMARY KEY,name VARCHAR2(100) NOT NULL,created_date DATE,value NUMBER(10),comments VARCHAR2(500)
) TABLESPACE new_tbs;# 开始在线重定义（使用主键方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',options_flag => DBMS_REDEFINITION.CONS_USE_PK  -- 使用主键);
END;
/-- 复制依赖对象（自动复制索引、约束）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers => TRUE,copy_constraints => TRUE,copy_privileges => TRUE,ignore_errors => TRUE,num_errors => num_errors);
END;
/# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/# 完成重定义（短暂锁表）# DBMS_REDEFINITION.FINISH_REDEF_TABLE 会执行flush shard pool 需要规避
alter session set events '10995 trace name context forever, level 2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/

修改表的分区结构

当前表为范围分区：

# 创建哈希分区中间表CREATE TABLE test_user.test_table_int (id            NUMBER,name          VARCHAR2(100) NOT NULL,created_date  DATE,value         NUMBER(10),new_column    VARCHAR2(50),CONSTRAINT pk_test_table_int PRIMARY KEY (id)
)
PARTITION BY HASH (id)
PARTITIONS 4
TABLESPACE test_tbs;


# 启动在线重定义
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'TEST_USER',orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT',col_mapping  => 'id id, name name, created_date created_date, value value, new_column new_column',options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/


# 复制依赖对象（自动复制索引、触发器等）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname          => 'TEST_USER',orig_table     => 'TEST_TABLE',int_table      => 'TEST_TABLE_INT',copy_indexes   => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers  => TRUE,ignore_errors  => TRUE,num_errors     => num_errors);DBMS_OUTPUT.PUT_LINE('依赖对象错误: ' || num_errors);
END;
/


# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname        => 'TEST_USER',orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT');
END;
/


# 完成重定义（短暂锁表）
BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname        => 'TEST_USER',orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT');
END;
/

高水位线回收（碎片整理）

# 记录当前高水位线
SELECT blocks, empty_blocks 
FROM dba_tables 
WHERE owner = 'TEST_USER' AND table_name = 'TEST_TABLE';


#检查选择的表是否可以执行在线重定义
BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_USER', 'TEST_TABLE');
END;
/


#创建中间表
CREATE TABLE test_user.test_table_int (id NUMBER PRIMARY KEY,name VARCHAR2(100) NOT NULL,created_date DATE,value NUMBER(10),new_column VARCHAR2(50)
) TABLESPACE test_tbs;


# 开始在线重定义（使用主键方式）
BEGINDBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'TEST_USER',orig_table   => 'TEST_TABLE',int_table    => 'TEST_TABLE_INT',col_mapping  => 'id id, name name, created_date created_date, value value, new_column new_column',  options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/


-- 复制依赖对象（自动复制索引、约束）
DECLAREnum_errors PLS_INTEGER;
BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT',copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,copy_triggers => TRUE,copy_constraints => TRUE,copy_privileges => TRUE,ignore_errors => TRUE,num_errors => num_errors);
END;
/


# 同步数据
BEGINDBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/


# 完成重定义# DBMS_REDEFINITION.FINISH_REDEF_TABLE 会执行flush shard pool 需要规避
alter session set events '10995 trace name context forever, level 2';BEGINDBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST_USER',orig_table => 'TEST_TABLE',int_table => 'TEST_TABLE_INT');
END;
/

检查高水位是否回收

SELECT blocks, empty_blocks 
FROM dba_tables 
WHERE owner = 'TEST_USER' AND table_name = 'TEST_TABLE';

转换为索引组织表（IOT）

总体步骤都与前面相同

http://www.lryc.cn/news/610137.html

相关文章：

  • [GYCTF2020]FlaskApp
  • 【编程实践】点云曲率计算与可视化
  • 八股——Kafka相关
  • 【Pytorch✨】LSTM04 l理解长期记忆和短期记忆
  • 第12届蓝桥杯Scratch_选拔赛_初级组_真题2020年8月23日
  • 神经网络---非线性激活
  • C++进阶-封装红黑树模拟实现map和set（难度较高）
  • 李沐写作笔记
  • 嵌入式 C 语言入门：函数指针基础笔记 —— 从计算器优化到指针本质
  • SurferCloud vs LightNode 海外云服务商详细对比
  • 【无标题】标准 I/O 中的一些函数，按功能分类说明其用法和特点
  • [特殊字符] 50 天 50 个项目 — 完结篇
  • 【Docker安装】Ubuntu 24.04.2 LTS系统下安装Docker环境——指定APT源安装方式
  • 基于MobileNet卷积神经网络和Xception神经网络算法的人脸表情识别系统的设计与实现
  • C语言的控制语句
  • 每日一leetcode:移动零
  • 【Java】HashMap线程安全吗？
  • allegro建库--1
  • 【云馨AI-大模型】2025年8月第一周AI浪潮席卷全球：创新与政策双轮驱动
  • MLS平滑滤波
  • 洛谷 P3373 【模板】线段树 2- 普及+/提高
  • 《Python 实用项目与工具制作指南》· 3.1 实战·开发题目数据生成器
  • 思科 UCS Fabric Interconnect 和 UCS Manager 简介
  • 比起登天，孙宇晨更需要安稳着陆
  • C语言编程中常用的预定义宏
  • 浅谈 Python 中的 next() 函数 —— 迭代器的驱动引擎
  • 【深度学习新浪潮】近三年城市级数字孪生的研究进展一览
  • push/pop字节对齐使用场景
  • Next Terminal 实战：内网无密码安全登录
  • cocos2 场景跳转传参