因为需要将一张上亿我们要记录的表修改为分区表,所以尝试使用联机重定义来给表增加新列与分区的方法来实现,下面是一个测试的例子,操作系统是 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 就操作完成。
来源: http://www.linuxidc.com/Linux/2017-06/145083.htm