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

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

表emp_redef将按以下规则来进行联机重定义:

联机重定义操作如下:

SQL> conn pm/pm@jypdbConnected.

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 8000SQL> set pages 8000SQL> column object_name heading 'object name' format a20SQL> column base_table_name heading 'base table name' format a10SQL> column ddl_txt heading 'ddl that caused error' format a40SQL> 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_redefName          Type         Nullable Default   Comments------------- ------------ -------- --------- --------EMPLOYEE_ID   NUMBER(6)FIRST_NAME    VARCHAR2(20) YLAST_NAME     VARCHAR2(25)JOB_ID        VARCHAR2(10)DEPARTMENT_ID NUMBER(4)MGR           NUMBER(5)    YHIREDATE      DATE         Y        (sysdate)SAL           NUMBER(7,2)  YBONUS         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_redefName          Type         Nullable Default Comments------------- ------------ -------- ------- --------EMPLOYEE_ID   NUMBER(6)    YFIRST_NAME    VARCHAR2(20) YLAST_NAME     VARCHAR2(25)JOB_ID        VARCHAR2(10)DEPARTMENT_ID NUMBER(4)    YSQL> drop table hr.int_emp_redef purge;Table dropped

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-2141105/，如需转载，请注明出处，否则将追究法律责任。