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;
#检查选择的表是否可以执行在线重定义
BEGIN
DBMS_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;
# 开始在线重定义（使用主键方式）
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT',
options_flag => DBMS_REDEFINITION.CONS_USE_PK -- 使用主键
);
END;
/
# 复制依赖对象（自动复制索引、约束）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_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';
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT'
);
END;
/
增加、修改或者删除表的字段
操作：添加字段new_column VARCHAR2(50)，删除字段comments。
#检查选择的表是否可以执行在线重定义
BEGIN
DBMS_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;
# 开始在线重定义（使用主键方式）
BEGIN
DBMS_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;
/
# 复制依赖对象（自动复制索引、约束）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT'
);
END;
/
# 完成重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT'
);
END;
/
将非分区表转换为分区表
#检查选择的表是否可以执行在线重定义
BEGIN
DBMS_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;
# 开始在线重定义（使用主键方式）
BEGIN
DBMS_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;
/
-- 复制依赖对象（自动复制索引、约束）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_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';
BEGIN
DBMS_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;
#检查选择的表是否可以执行在线重定义
BEGIN
DBMS_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;
# 开始在线重定义（使用主键方式）
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT',
options_flag => DBMS_REDEFINITION.CONS_USE_PK -- 使用主键
);
END;
/
-- 复制依赖对象（自动复制索引、约束）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_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';
BEGIN
DBMS_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;
# 启动在线重定义
BEGIN
DBMS_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;
/
# 复制依赖对象（自动复制索引、触发器等）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'TEST_USER',
orig_table => 'TEST_TABLE',
int_table => 'TEST_TABLE_INT'
);
END;
/
# 完成重定义（短暂锁表）
BEGIN
DBMS_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';
#检查选择的表是否可以执行在线重定义
BEGIN
DBMS_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;
# 开始在线重定义（使用主键方式）
BEGIN
DBMS_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;
/
-- 复制依赖对象（自动复制索引、约束）
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_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;
/
# 同步数据
BEGIN
DBMS_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';
BEGIN
DBMS_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）
总体步骤都与前面相同
