表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就操作完成。