Oracle使用联机重定义来给表增加新列与分区

232次阅读

共计 5555 个字符，预计需要花费 14 分钟才能阅读完成。

因为需要将一张上亿我们要记录的表修改为分区表，所以尝试使用联机重定义来给表增加新列与分区的方法来实现，下面是一个测试的例子，操作系统是 Oracle Linux 7.1, 数据库为 12.2.0.1, 原始表为 emp_redef，该表存储在 hr 方案中:

 SQL> desc hr.emp_redef
Name          Type         Nullable Default Comments
------------- ------------ -------- ------- --------
EMPLOYEE_ID   NUMBER(6)
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)    Y

表 emp_redef 将按以下规则来进行联机重定义:
. 增加新列 mgr,hiredate,sal 与 bonus
. 新列 bonus 被初始化为 0
. 列 department_id 的值由 10 开始增加
. 表将被重定义为范围分区表，分区键为 employee_id。

联机重定义操作如下:
1. 用要执行联机重定义操作的用户登录数据库

 SQL> conn pm/pm@jypdb
Connected.

2. 验证表 emp_redef 是否可以执行联机重定义。在这种情况下，可以使用主键或伪主键来来进行验证。

 SQL> exec dbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk);
 
PL/SQL procedure successfully completed.

3. 创建一个中间表 hr.int_emp_redef

 SQL> create table hr.int_emp_redef
  2  (3    employee_id   NUMBER(6) not null,
  4    first_name    VARCHAR2(20),
  5    last_name     VARCHAR2(25) not null,
  6    job_id        VARCHAR2(10) not null,
  7    department_id NUMBER(4) not null,
  8    mgr           NUMBER(5),
  9    hiredate      DATE DEFAULT(sysdate),
 10    sal           NUMBER(7,2),
 11    bonus         NUMBER(7,2) DEFAULT(0)
 12  )
 13  partition by range(employee_id)
 14  (15  partition emp200 values less than(200) tablespace users,
 16  partition emp400 values less than(400) tablespace users
 17  );
Table created

4. 开始重定义操作

 SQL> begin
  2  dbms_redefinition.start_redef_table(
  3    uname => 'hr',
  4    orig_table => 'emp_redef',
  5    int_table => 'int_emp_redef',
  6    col_mapping => 'employee_id employee_id, first_name first_name,last_name last_name, job_id job_id, department_id+10 department_id,0 bonus',
  7    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  8  end;
  9  /
 
PL/SQL procedure successfully completed.

5. 复制依赖对象 (自动对表 hr.int_emp_redef 创建任何触发器，索引，物化视图日志，授权与约束)

 SQL> declare
  2  num_errors pls_integer;
  3  begin
  4  dbms_redefinition.copy_table_dependents(
  5    uname => 'hr',
  6    orig_table => 'emp_redef',
  7    int_table => 'int_emp_redef',
  8    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
  9    copy_triggers => TRUE,
 10    copy_constraints => TRUE,
 11    copy_privileges => TRUE,
 12    ignore_errors => TRUE,
 13    num_errors => num_errors);
 14  end;
 15  /
 
PL/SQL procedure successfully completed.

注意，在调用这个过程时 ignore_errors 参数需要设置为 TRUE。原因是中间表创建了主键约束，并且当执行 copye_table_dependents 过程来试图从原始表复制主键约束与索引时会发生错误。可以忽略这些错误，但必须执行下一步操作中的查询来查看是否还存在其它错误。

6. 查询 dba_redefinition_errors 视图来查看错误信息

 SQL> set long 8000
SQL> set pages 8000
SQL> column object_name heading 'object name' format a20
SQL> column base_table_name heading 'base table name' format a10
SQL> column ddl_txt heading 'ddl that caused error' format a40
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;
 
object name          base table ddl that caused error
-------------------- ---------- ----------------------------------------
SYS_C0023200         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                ("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023
                                2000" NOT NULL ENABLE NOVALIDATE)
 
SYS_C0023201         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
                                ("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201
                                0" NOT NULL ENABLE NOVALIDATE)
 
 
2 rows selected.

上面的错误信息是说中间表的 last_name 与 job_id 列为 not null，而原因表为 null，这种错误可以忽略。

7. 同步中间表 hr.int_emp_redef

 SQL> begin
  2  dbms_redefinition.sync_interim_table(
  3    uname => 'hr',
  4    orig_table => 'emp_redef',
  5    int_table => 'int_emp_redef');
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

8. 完成重定义操作

 SQL> begin
  2  dbms_redefinition.finish_redef_table(
  3   uname => 'hr',
  4   orig_table => 'emp_redef',
  5   int_table => 'int_emp_redef');
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

表 hr.emp_redef 只会以排他模式被锁定很短的时间来结束重定义操作。在操作完成后，表 hr.emp_redef 将使用 hr.int_emp_redef 表的所有属性来重定义。

 SQL> desc hr.emp_redef
Name          Type         Nullable Default   Comments
------------- ------------ -------- --------- --------
EMPLOYEE_ID   NUMBER(6)
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
MGR           NUMBER(5)    Y
HIREDATE      DATE         Y        (sysdate)
SAL           NUMBER(7,2)  Y
BONUS         NUMBER(7,2)  Y        (0)

 SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'EMP_REDEF',schema => 'HR') from dual;
 
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR')
--------------------------------------------------------------------------------
 
  CREATE TABLE "HR"."EMP_REDEF"
   ("EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
        "DEPARTMENT_ID" NUMBER(4,0) NOT NULL ENABLE,
        "MGR" NUMBER(5,0),
        "HIREDATE" DATE DEFAULT (sysdate),
        "SAL" NUMBER(7,2),
        "BONUS" NUMBER(7,2) DEFAULT (0),
         CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("EMPLOYEE_ID")
 (PARTITION "EMP200"  VALUES LESS THAN (200) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "EMP400"  VALUES LESS THAN (400) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" )
 
 
1 row selected.

可以看到表 hr.emp_redef 已经成功能联机重定义

9. 等任何查询中间表的语句执行完成后将其删除，而且中间表在重定义后其结构就变成了原始表的表结构

 SQL> desc hr.int_emp_redef
Name          Type         Nullable Default Comments
------------- ------------ -------- ------- --------
EMPLOYEE_ID   NUMBER(6)    Y
FIRST_NAME    VARCHAR2(20) Y
LAST_NAME     VARCHAR2(25)
JOB_ID        VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)    Y
 
SQL> drop table hr.int_emp_redef purge;
Table dropped

到此，联机重定义表 hr.emp_redef 就操作完成。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-06/145083.htm

正文完

星哥玩云-微信公众号

发表至：数据库技术

2022-01-22

0

转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。

PL/SQL Developer连接本地Oracle 11g 64位数据库和快捷键设置

Ubuntu 16.04安装phpMyAdmin数据库管理工具

Navicat Premium 常用功能讲解

Python3操作MySQL数据库

Oracle使用联机重定义来给表增加新列与分区

开源堡垒机JumpServer配置教程：使用步骤与配置

申请腾讯混元的API Key并且使用LobeChat调用混元AI

系统加固-Linux不允许用户使用密码登录，只能使用密钥登录

【开源安全保护】如何安装JumpServer堡垒机

基于Docker快速搭建一个开源的IT人员在线工具箱-it-tools

2025腾讯云服务器租赁多少钱1年？轻量+CVM+GPU优惠价格表

大神教你在Linux中使用tcpdump命令

CentOS 7 简单搭建OpenLDAP服务

讲述下：Linux运维工程师入门的10个基本技能点

Mariadb学习总结（七）：单表查询

	SQL> desc hr.emp_redef
	Name Type Nullable Default Comments
	------------- ------------ -------- ------- --------
	EMPLOYEE_ID NUMBER(6)
	FIRST_NAME VARCHAR2(20) Y
	LAST_NAME VARCHAR2(25)
	JOB_ID VARCHAR2(10)
	DEPARTMENT_ID NUMBER(4) Y

	SQL> exec dbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk);

	PL/SQL procedure successfully completed.

	SQL> create table hr.int_emp_redef
	2 (3 employee_id NUMBER(6) not null,
	4 first_name VARCHAR2(20),
	5 last_name VARCHAR2(25) not null,
	6 job_id VARCHAR2(10) not null,
	7 department_id NUMBER(4) not null,
	8 mgr NUMBER(5),
	9 hiredate DATE DEFAULT(sysdate),
	10 sal NUMBER(7,2),
	11 bonus NUMBER(7,2) DEFAULT(0)
	12 )
	13 partition by range(employee_id)
	14 (15 partition emp200 values less than(200) tablespace users,
	16 partition emp400 values less than(400) tablespace users
	17 );
	Table created

	SQL> begin
	2 dbms_redefinition.start_redef_table(
	3 uname => 'hr',
	4 orig_table => 'emp_redef',
	5 int_table => 'int_emp_redef',
	6 col_mapping => 'employee_id employee_id, first_name first_name,last_name last_name, job_id job_id, department_id+10 department_id,0 bonus',
	7 options_flag => DBMS_REDEFINITION.CONS_USE_PK);
	8 end;
	9 /

	PL/SQL procedure successfully completed.

	SQL> declare
	2 num_errors pls_integer;
	3 begin
	4 dbms_redefinition.copy_table_dependents(
	5 uname => 'hr',
	6 orig_table => 'emp_redef',
	7 int_table => 'int_emp_redef',
	8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
	9 copy_triggers => TRUE,
	10 copy_constraints => TRUE,
	11 copy_privileges => TRUE,
	12 ignore_errors => TRUE,
	13 num_errors => num_errors);
	14 end;
	15 /

	PL/SQL procedure successfully completed.

	SQL> set long 8000
	SQL> set pages 8000
	SQL> column object_name heading 'object name' format a20
	SQL> column base_table_name heading 'base table name' format a10
	SQL> column ddl_txt heading 'ddl that caused error' format a40
	SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors;

	object name base table ddl that caused error
	-------------------- ---------- ----------------------------------------
	SYS_C0023200 EMP_REDEF ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
	("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023
	2000" NOT NULL ENABLE NOVALIDATE)

	SYS_C0023201 EMP_REDEF ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
	("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201
	0" NOT NULL ENABLE NOVALIDATE)


	2 rows selected.

	SQL> begin
	2 dbms_redefinition.sync_interim_table(
	3 uname => 'hr',
	4 orig_table => 'emp_redef',
	5 int_table => 'int_emp_redef');
	6 end;
	7 /

	PL/SQL procedure successfully completed.

	SQL> begin
	2 dbms_redefinition.finish_redef_table(
	3 uname => 'hr',
	4 orig_table => 'emp_redef',
	5 int_table => 'int_emp_redef');
	6 end;
	7 /

	PL/SQL procedure successfully completed.

	SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'EMP_REDEF',schema => 'HR') from dual;

	DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR')
	--------------------------------------------------------------------------------

	CREATE TABLE "HR"."EMP_REDEF"
	("EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
	"FIRST_NAME" VARCHAR2(20),
	"LAST_NAME" VARCHAR2(25) NOT NULL ENABLE,
	"JOB_ID" VARCHAR2(10) NOT NULL ENABLE,
	"DEPARTMENT_ID" NUMBER(4,0) NOT NULL ENABLE,
	"MGR" NUMBER(5,0),
	"HIREDATE" DATE DEFAULT (sysdate),
	"SAL" NUMBER(7,2),
	"BONUS" NUMBER(7,2) DEFAULT (0),
	CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "USERS" ENABLE
	) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "USERS"
	PARTITION BY RANGE ("EMPLOYEE_ID")
	(PARTITION "EMP200" VALUES LESS THAN (200) SEGMENT CREATION IMMEDIATE
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "USERS" ,
	PARTITION "EMP400" VALUES LESS THAN (400) SEGMENT CREATION IMMEDIATE
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "USERS" )


	1 row selected.

	SQL> desc hr.int_emp_redef
	Name Type Nullable Default Comments
	------------- ------------ -------- ------- --------
	EMPLOYEE_ID NUMBER(6) Y
	FIRST_NAME VARCHAR2(20) Y
	LAST_NAME VARCHAR2(25)
	JOB_ID VARCHAR2(10)
	DEPARTMENT_ID NUMBER(4) Y

	SQL> drop table hr.int_emp_redef purge;
	Table dropped