共计 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
