Online Redefinition在线重定义对象是Oracle中很酷的一种特性，它可以帮助我们在7*24在线的系统中从容地做出数据对象的在线定义修改，是Oracle数据库保证其高可用性的重要技术。
在线重定义Online Redefinition特性在许多场景中都是十分有用的，例如：
- 修改表的Storage存储参数
- 在同一Schema下将表移动到不同的表空间
- 转换非分区表为分区表
- 添加或删除分区
- 重新创建表以减少碎片,降低高水位
- 将堆组织的表改变为索引组织表
- 添加或删除列
使用Online Redefinition在线重定义需要用到DBMS_REDEFINITION程序包，EXECUTE_CATALOG_ROLE角色默认被赋予该PL/SQL Package的执行权限。除了执行该程序包的权限外，用户还需要拥有以下权限:
- CREATE ANY TABLE
- ALTER ANY TABLE
- DROP ANY TABLE
- LOCK ANY TABLE
- SELECT ANY TABLE
若要执行COPY_TABLE_DEPENDENTS存储过程则还需要以下权限：
- CREATE ANY TRIGGER
- CREATE ANY INDEX
在早期版本中在线重定义(Online Redefinition)对于某些具有特殊数据类型的表存在诸多限制，从Oracle 10g开始拥有以下数据类型的表也支持在线重定义(Online Redefinition)了：
- 存在LONG类型column字段的表可以被在线重定义(redefined online)；但是LONG类型字段只能被转换为character large object即CLOB
- 存在LONG RAW类型column字段的表可以被在线重定义(redefined online)；但是LONG RAW类型字段只能被转换为binary large object 即BLOB
此外从10g开始支持对replication table的在线重定义：
- 包含在master-master replications中的表
- 在n-way master复制环境中没有horizontal或vertical subsetting，或者允许 column transformations的表
我们可以通过以下步骤一步一步地完成对一张普通堆表的在线重定义(Online Redefinition)：
步骤1：决定我们所要使用的重定义方式(redefinition method)，存在2种方式：
- 第一种是我们较为推荐的方式，采用Primary Key主键或者pseudoprimary key伪主键实施重定义(从Oracle 10g开始支持pseudoprimary key伪主键)。 这里pseudoprimary key伪主键要求是唯一键且所有的成员列均是非空NOT NULL。使用该种方式，重定义前和重定义后版本的表均必须有相同的Primary Key主键或者pseudoprimary key伪主键列。不管是从性能角度，还是从操作的复杂度考虑，常规场景中都推荐尽可能使用此种方式
- 第二种方式是使用rowid进行redefinition。首先索引组织表index-organized table (IOT)不支持使用rowid的重定义方式。此外，若使用该种redefinition方式，最终会有一个隐藏的字段M_ROW$$被加入到重定义后版本的表上，Oracle官方推荐在重定义完成后将该M_ROW$$字段drop掉或者标记为unused。
步骤2：通过调用DBMS_REDEFINITION.CAN_REDEF_TABLE存储过程并以OPTIONS_FLAG(flag indicating user options to use)参数指定所要使用的重定义方式，来验证原表是否可以以这样的方式来在线重定义。若原表不符合指定重定义方式的要求，那么该过程会报出一个错误，说明该表不能在线重定义的具体原因。
若指定OPTIONS_FLAG为常数DBMS_REDEFINITION.CONS_USE_PK(cons_use_pk CONSTANT PLS_INTEGER := 1;)，意为使用primary keys 或 pseudoprimary keys的重定义方式。
若指定OPTIONS_FLAG为常数DBMS_REDEFINITION.CONS_USE_ROWID(cons_use_rowid CONSTANT PLS_INTEGER := 2;)，意为使用rowid的重定义方式。
DBMS_REDEFINITION.CAN_REDEF_TABLE过程的详细定义如下:
-- NAME: can_redef_table - check if given table can be re-defined -- INPUTS: uname - table owner name -- tname - table name -- options_flag - flag indicating user options to use -- part_name - partition name PROCEDURE can_redef_table(uname IN VARCHAR2, tname IN VARCHAR2, options_flag IN PLS_INTEGER := 1, part_name IN VARCHAR2 := NULL);
步骤3：在原表的同一Schema下创建一张空的临时表(interim table)， 该表具有所有我们想要的属性。若有column字段需要通过重定义drop掉，那么就在这张临时表的定义中去掉该column。同理若有column字段需要通过重定义加入到表上，那么就在临时表上加入该column的定义。
理论上我们可以并行地实施表的在线重定义；若已经同时指定了原表和临时表的并发度，那么也请确保实施操作的本会话(session)已经启用了会话级别的并行执行， 这样Oracle服务进程会在实施重定义的过程中尽可能地使用并行执行( parallel execution )。
可以采用以下ALTER SESSION命令启用并行的DML和查询:
alter session force parallel dml parallel 4; alter session force parallel query parallel 4;
如原表的结构为HR.JOBS:
SQL> desc hr.jobs; Name Null? Type ----------------------------------------- -------- ---------------------------- JOB_ID NOT NULL VARCHAR2(10) JOB_TITLE NOT NULL VARCHAR2(35) MIN_SALARY NUMBER(8) MAX_SALARY NUMBER(8) EXEMPT_STATUS VARCHAR2(3)
希望在此原表结构基础上加入默认为188的Number类型名为Maclean的字段column，那么我们创建临时表如下：
SQL> create table hr.jobs_hist_int 2 ( job_id varchar2(10) primary key, 3 job_title varchar2(35) NOT NULL, 4 min_salary number(8), 5 max_salary number(8), 6 exempt_status varchar2(3), 7 maclean number(8) default 188); Table created.
步骤4： 调用DBMS_REDEFINITION.START_REDEF_TABLE存储过程启动重定义进程，使用该过程需要指定以下的参数：
- 将要重定义的表名
- 临时表的名字(interim table name)
- 字段的映射信息(column mapping)
- 重定义使用的方式(primary key or rowid)
- 此外还可以指定用以排序的字段
若字段映射参数column mapping未指定，那么Oracle假设所有原表上的列(字段名不变)都被包含在中间表上了。 若指定了column mapping信息，那么只有那些在字段映射中明确指定的原表字段被考虑重定义。若没有指定使用何种重定义方式，那么Oracle默认会采用primary keys或pseudoprimary keys的方式。
从10g开始出现了ORDERBY_COLS参数可以用于指定在临时表初始化过程中数据行按照字段排序。
DBMS_REDEFINITION.START_REDEF_TABLE过程的具体定义如下:
-- NAME: start_redef_table - start the online re-organization -- INPUTS: uname - schema name -- orig_table - name of table to be re-organized -- int_table - name of interim table -- col_mapping - select list col mapping -- options_flag - flag indicating user options to use -- orderby_cols - comma separated list of order by columns -- followed by the optional ascending/descending -- keyword -- part_name - name of the partition to be redefined PROCEDURE start_redef_table(uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, col_mapping IN VARCHAR2 := NULL, options_flag IN BINARY_INTEGER := 1, orderby_cols IN VARCHAR2 := NULL, part_name IN VARCHAR2 := NULL);
步骤5： 使用10g以后出现的COPY_TABLE_DEPENDENTS存储过程在临时表上自动创建如constraints, triggers, indexes,privileges类型的依赖对象(dependent object)。该COPY_TABLE_DEPENDENTS过程同时也会注册这些依赖对象。
使用COPY_TABLE_DEPENDENTS克隆依赖对象要比后面介绍REGISTER_DEPENDENT_OBJECTS过程来得简单方便。
该存储过程的NUM_ERRORS(number of errors that occurred while cloning ddl)输出参数,显示了其运行过程中所产生的错误数量。若IGNORE_ERRORS(TRUE implies continue after errors, FALSE otherwise)参数设置为TRUE，那么该过程会忽略错误信息并不输出，继续其工作。若设置为FALSE，那么错误会在错误堆栈中显性输出。
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS过程的定义如下：
-- NAME: copy_table_dependents -- -- INPUTS: uname - schema name -- orig_table - name of table to be re-organized -- int_table - name of interim table -- copy_indexes - integer value indicating whether to -- copy indexes -- 0 - don't copy -- 1 - copy using storage params/tablespace -- of original index -- copy_triggers - TRUE implies copy triggers, FALSE otherwise -- copy_constraints - TRUE implies copy constraints, FALSE -- otherwise -- copy_privileges - TRUE implies copy priviliges, FALSE -- otherwise -- ignore errors - TRUE implies continue after errors, FALSE -- otherwise -- num_errors - number of errors that occurred while -- cloning ddl -- copy_statistics - TURE implies copy table statistics, FALSE -- otherwise. -- If copy_indexes is 1, copy index -- related statistics, 0 otherwise. PROCEDURE copy_table_dependents(uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, copy_indexes IN PLS_INTEGER := 1, copy_triggers IN BOOLEAN := TRUE, copy_constraints IN BOOLEAN := TRUE, copy_privileges IN BOOLEAN := TRUE, ignore_errors IN BOOLEAN := FALSE, num_errors OUT PLS_INTEGER, copy_statistics IN BOOLEAN := FALSE);
我们可以通过查询10g以后出现的DBA_REDEFINITION_ERRORS视图(DBA_REDEFINITION_ERRORS is an online redefinition view and displays the dependent objects for which errors were raised while attempting to create similar objects on the interim table of the redefinition.)来判断在使用COPY_TABLE_DEPENDENTS存储过程克隆依赖对象过程中是否产生了错误。该视图记录了重定义过程中在克隆依赖对象时产生的错误。 克隆对象可能因缺少系统资源或原表的一个逻辑结构变化而失败。
在我们成功克隆这些依赖对象后，相关错误将会从该视图中被移除。 我们可以反复执行COPY_TABLE_DEPENDENTS或后面介绍的REGISTER_DEPENDENT_OBJECTS过程尝试重新克隆依赖对象。
示例错误如下：
SQL> select * from DBA_REDEFINITION_ERRORS;
OBJECT_TYP OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
BASE_TABLE_OWNER BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
INDEX HR JOB_ID_PK
HR JOBS
CREATE UNIQUE INDEX "HR"."TMP$$_JOB_ID_PK0" ON "HR"."INT_JOBS_HIST" ("JOB_ID")
步骤6： 这不是必需的步骤。我们也可以使用10g以后出现的REGISTER_DEPENDENT_OBJECT将正要重定义的表上的依赖对象注册到临时表上对应的依赖对象。 换句话说COPY_TABLE_DEPENDENTS的功能，REGISTER_DEPENDENT_OBJECT也是可以做到的，但是没有COPY_TABLE_DEPENDENTS来得简单方便。若我们想在原表的基础上建立额外的依赖对象，那么也可以用该过程来手动建立。若之前的COPY_TABLE_DEPENDENTS运行失败了，那么也可以通过REGISTER_DEPENDENT_OBJECT来手工补救，注册那些没有克隆成功的依赖对象。
注意REGISTER_DEPENDENT_OBJECT过程也是10g以后出现的，在早期版本中我们是要手动重命名依赖对象的。
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT的详细定义如下：
-- NAME: register_dependent_object - register dependent object -- -- INPUTS: uname - schema name -- orig_table - name of table to be re-organized -- int_table - name of interim table -- dep_type - type of the dependent object -- dep_owner - name of the dependent object owner -- dep_orig_name- name of the dependent object defined on table -- being re-organized -- dep_int_name - name of the corressponding dependent object on -- the interim table PROCEDURE register_dependent_object(uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, dep_type IN PLS_INTEGER, dep_owner IN VARCHAR2, dep_orig_name IN VARCHAR2, dep_int_name IN VARCHAR2);
与REGISTER_DEPENDENT_OBJECT相反， unregister_dependent_object过程用以注销依赖对象(unregister dependent object)。
通过查询10g以后出现的DBA_REDEFINITION_OBJECTS(an online redefinition view and displays the objects involved in the current redefinitions.)可以确认是否所有需要的依赖的对象都已被注册。该视图记录显示地被REGISTER_DEPENDENT_OBJECT注册的或隐式地被COPY_TABLE_DEPENDENTS注册的依赖对象。注意该视图仅包含当前重定义的信息。
步骤7：执行DBMS_REDEFINITION.FINISH_REDEF_TABLE 存储过程完成表的在线重定义。在此procedure运行过程中，原表会被以Exclusive lock mode(TM lmode=6)排他模式锁住极为短暂的一段时间(秒级)，具体这段时间的长短受到原表上数据量的影响。 同时在此过程中，会发生以下事件:
- 原表被真正意义上重定义，拥有临时表的所有属性、索引、约束、授权和触发器。
- 已注册的依赖对象会被自动重命名
- 临时表上的参考约束(referential constraint)会牵涉到重定义后的表上，且这些约束会被自动启用。
若重定义以rowid 方式完成，那么重定义后的表上会出现一个隐藏字段叫做M_ROW$$，我们推荐将该隐藏字段设置为unused:
ALTER TABLE table_name SET UNUSED (M_ROW$$)
DBMS_REDEFINITION.FINISH_REDEF_TABLE过程的详细定义如下：
-- NAME: finish_redef_table - complete the online re-organization -- INPUTS: uname - schema name -- orig_table - name of table to be re-organized -- int_table - name of interim table -- part_name - name of the partition being redefined PROCEDURE finish_redef_table(uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, part_name IN VARCHAR2 := NULL);
以上我们了解了一个在线重定义的主要步骤，以及10g中引入的一些新的procedure和有用视图，接下来我们实际操作一个在线重定义示范：
原表的定义和数据量如下:
create table SH.SALES
(
PROD_ID NUMBER not null,
CUST_ID NUMBER not null,
TIME_ID DATE not null,
CHANNEL_ID NUMBER not null,
PROMO_ID NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD NUMBER(10,2) not null
)
alter table SH.SALES
add constraint SALES_CHANNEL_FK foreign key (CHANNEL_ID)
references SH.CHANNELS (CHANNEL_ID);
alter table SH.SALES
add constraint SALES_CUSTOMER_FK foreign key (CUST_ID)
references SH.CUSTOMERS (CUST_ID);
alter table SH.SALES
add constraint SALES_PRODUCT_FK foreign key (PROD_ID)
references SH.PRODUCTS (PROD_ID);
alter table SH.SALES
add constraint SALES_PROMO_FK foreign key (PROMO_ID)
references SH.PROMOTIONS (PROMO_ID);
alter table SH.SALES
add constraint SALES_TIME_FK foreign key (TIME_ID)
references SH.TIMES (TIME_ID);
-- Create/Recreate indexes
create bitmap index SH.SALES_CHANNEL_BIX on SH.SALES (CHANNEL_ID);
create bitmap index SH.SALES_CUST_BIX on SH.SALES (CUST_ID);
create bitmap index SH.SALES_PROD_BIX on SH.SALES (PROD_ID);
create bitmap index SH.SALES_PROMO_BIX on SH.SALES (PROMO_ID);
create bitmap index SH.SALES_TIME_BIX on SH.SALES (TIME_ID);
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
现在希望在原表的基础上增加默认为188的number类型maclean字段，且将该表转换为按照range (TIME_ID)范围分区的分区表。
因为该表上有7*24的更新业务如下，所以只能使用在线重定义方式，且因为该表上没有Primary key，所以只能使用rowid的重定义方式:
begin
loop
insert into sh.sales
values
(42, 938, to_date('1998-01-01', 'YYYY-MM-DD'), 2, 999, 1, 800);
insert into sh.sales
values
(42, 938, to_date('1998-01-01', 'YYYY-MM-DD'), 2, 999, 1, 800);
delete sh.sales where rownum = 1;
commit;
dbms_lock.sleep(0.5);
end loop;
end;
1. 利用can_redef_table存储过程验证原表是否可以以rowid方式重定义：
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmac.cn & www.askmac.cn SQL> conn sh/sh Connected. SQL> begin 2 dbms_redefinition.can_redef_table(uname => 'SH', 3 tname => 'SALES', 4 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); 5 end; 6 / begin * ERROR at line 1: ORA-12091: cannot online redefine table "SH"."SALES" with materialized views ORA-06512: at "SYS.DBMS_REDEFINITION", line 137 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478 ORA-06512: at line 2
发现SALES表上有物化视图，这回导致online redefine无法进行，找出物化视图并drop掉，完成重定义后可以重建这些materialized view:
SQL> select mview_name from dba_mviews where owner = 'SH'; MVIEW_NAME ------------------------------ FWEEK_PSCAT_SALES_MV CAL_MONTH_SALES_MV SQL> drop materialized view CAL_MONTH_SALES_MV; Materialized view dropped. SQL> drop materialized view FWEEK_PSCAT_SALES_MV; Materialized view dropped. SQL> begin 2 dbms_redefinition.can_redef_table(uname => 'SH', 3 tname => 'SALES', 4 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); 5 end; 6 / PL/SQL procedure successfully completed.
再次验证成功。
2. 创建空的临时表，在原表的基础上加入MACLEAN字段以及分区定义:
create table SH.INT_SALES
(
PROD_ID NUMBER not null,
CUST_ID NUMBER not null,
TIME_ID DATE not null,
CHANNEL_ID NUMBER not null,
PROMO_ID NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD NUMBER(10,2) not null,
MACLEAN NUMBER(10,2) default 188 not null
)
partition by range (TIME_ID)
(
partition SALES_1995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition SALES_1996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
partition SALES_H1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace EXAMPLE
pctfree 0
initrans 1
maxtrans 255,
...................................
建表DDL过长，以上节选主要部分
并在会话级别启用FORCE PARALLEL:
alter session force parallel dml parallel 4; alter session force parallel query parallel 4;
3.调用DBMS_REDEFINITION.START_REDEF_TABLE存储过程启动重定义进程
SQL> set timing on; begin DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SH', orig_table => 'SALES', int_table => 'INT_SALES', col_mapping => 'PROD_ID PROD_ID,CUST_ID CUST_ID,TIME_ID TIME_ID,CHANNEL_ID CHANNEL_ID,PROMO_ID PROMO_ID,QUANTITY_SOLD QUANTITY_SOLD,AMOUNT_SOLD AMOUNT_SOLD', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID); end; PL/SQL procedure successfully completed. Elapsed: 00:00:04.23 SQL> select count(*) from int_sales; COUNT(*) ---------- 921539 Elapsed: 00:00:00.23
4. 调用COPY_TABLE_DEPENDENTS过程克隆依赖对象：
SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'SH',
5 orig_table => 'SALES',
6 int_table => 'INT_SALES',
7 copy_indexes => DBMS_REDEFINITION.cons_orig_params,
8 copy_triggers => TRUE,
9 copy_constraints => FALSE,
10 copy_privileges => TRUE,
11 ignore_errors => FALSE,
12 num_errors => num_errors,
13 copy_statistics => TRUE);
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1712
ORA-06512: at line 4
Elapsed: 00:00:00.06
SQL> select * from DBA_REDEFINITION_ERRORS;
OBJECT_TYP OBJECT_OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
BASE_TABLE_OWNER BASE_TABLE_NAME
------------------------------ ------------------------------
DDL_TXT
--------------------------------------------------------------------------------
INDEX SH SALES_CHANNEL_BIX
SH SALES
CREATE BITMAP INDEX "SH"."TMP$$_SALES_CHANNEL_BIX0" ON "SH"."INT_SALES" ("CHANNE
因为原表上有bitmap indexes，而目标的partitioned tables(分区表)仅支持LOCAL bitmap indexes， 这里可以通过REGISTER_DEPENDENT_OBJECT来注册LOCAL bitmap indexes依赖对象，作为教学示例我们不这样做，而选择不克隆索引类型的依赖对象，指定copy_indexes参数为0：
SQL> DECLARE 2 num_errors PLS_INTEGER; 3 BEGIN 4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'SH', 5 orig_table => 'SALES', 6 int_table => 'INT_SALES', 7 copy_indexes => 0, 8 copy_triggers => TRUE, 9 copy_constraints => FALSE, 10 copy_privileges => TRUE, 11 ignore_errors => FALSE, 12 num_errors => num_errors, 13 copy_statistics => TRUE); 14 END; 15 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.03
5. 利用sync_interim_table过程同步临时表的数据减少finish_redef_table的耗时:
SQL> select count(*) from sales; COUNT(*) ---------- 923046 Elapsed: 00:00:00.01 SQL> select count(*) from int_sales; COUNT(*) ---------- 921539 Elapsed: 00:00:00.24 SQL> begin 2 dbms_redefinition.sync_interim_table(uname => 'SH', 3 orig_table => 'SALES', 4 int_table => 'INT_SALES'); 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.87 SQL> select count(*) from int_sales; COUNT(*) ---------- 923135
6.执行finish_redef_table过程完成重定义：
begin
dbms_redefinition.finish_redef_table(uname => 'SH',
orig_table => 'SALES',
int_table => 'INT_SALES');
end;
/
SQL> desc sales;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
MACLEAN NOT NULL NUMBER(10,2)
SQL> select count(*) from sales partition (SALES_Q2_2000);
COUNT(*)
----------
55515
Elapsed: 00:00:00.02
SQL> select distinct maclean from sales;
MACLEAN
----------
188
Elapsed: 00:00:00.32
以上成功完成了对SALES表的Online Redefinition，由非分区表在线重定义为分区表且增加了一个字段。
这里因为我们使用rowid方式，所以重定义完的表上会多出一个隐藏字段， 从10.2开始M_ROW$$的隐藏列会被命名为SYS_%DATE%的形式，且默认即为unused状态：
SQL> set linesize 90 pagesize 1400 SQL> select * 2 from dba_tab_cols 3 where owner = 'SH' 4 and column_name like 'SYS%' 5 and table_name='SALES'; OWNER TABLE_NAME ------------------------------ ------------------------------ COLUMN_NAME ------------------------------ DATA_TYPE ------------------------------------------------------------------------------------------ DAT DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID --- ------------------------------ ----------- -------------- ---------- - ---------- DEFAULT_LENGTH -------------- DATA_DEFAULT -------------------------------------------------------------------------------- NUM_DISTINCT LOW_VALUE ------------ ---------------------------------------------------------------- HIGH_VALUE DENSITY NUM_NULLS ---------------------------------------------------------------- ---------- ---------- NUM_BUCKETS LAST_ANAL SAMPLE_SIZE CHARACTER_SET_NAME ----------- --------- ----------- -------------------------------------------- CHAR_COL_DECL_LENGTH GLO USE AVG_COL_LEN CHAR_LENGTH C V80 DAT HID VIR SEGMENT_COLUMN_ID -------------------- --- --- ----------- ----------- - --- --- --- --- ----------------- INTERNAL_COLUMN_ID HISTOGRAM ------------------ --------------- QUALIFIED_COL_NAME ------------------------------------------------------------------------------------------ SH SALES SYS_C00009_11120703:40:57$ VARCHAR2 255 Y CHAR_CS 255 NO NO 255 B NO YES YES NO 9 9 NONE SYS_C00009_11120703:40:57$ ========================================================================================== SQL> select * from dba_unused_col_tabs ; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- SH SALES 1 SQL> alter table sales drop unused columns; Table altered. Elapsed: 00:00:13.36 SQL> select * from dba_unused_col_tabs ; no rows selected
若在完成重定义(执行finish_redef_table)之前希望中断在线重定义表，则需要使用DBMS_REDEFINITION.ABORT_REDEF_TABLE明确手动中断abort，如:
begin
dbms_redefinition.abort_redef_table(uname => 'SH',
orig_table => 'SALES',
int_table => 'INT_SALES');
end;
/
该abort_redef_table过程的详细定义如下：
-- NAME: abort_redef_table - clean up after errors or abort the
-- online re-organization
-- INPUTS: uname - schema name
-- orig_table - name of table to be re-organized
-- int_table - name of interim table
-- part_name - name of the partition being redefined
PROCEDURE abort_redef_table(uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);
online redefinition Step
Process ------- ------ 5' Table INT_EMP becomes EMP ------- | and vice-versa | 1 2 Source Table 3 Create interim Table EMP ---> Start redefinition ---> INT_EMP 3' data Source to Target ^ ^ ^ / | | / | | / 4 | / Create constraints / Create indexes 3'' / Create triggers DML on source table / updates/deletes/inserts 5 /Finish redefinition stored into MLOG$_EMP ------ Step 1: Verify that the source table is able to undergo an ONLINE redefinition using the dbms_redefinition.can_redef_table procedure. Executing the following should let you know if the table is eligible for online redefinition: BEGIN Dbms_Redefinition.Can_Redef_Table('USER','TABLE NAME'); END; If the table is not eligible it will fail with with errors. Step 2: Create an empty interim table reflecting the final structure Step 3: Start the redefinition of the source table using the dbms_redefinition.start_redef_table procedure, defining: -> the source table to be reorganized -> the interim table -> the columns mapping for each column undergoing a change The start_redef_table procedure automatically: -> inserts all rows from the source table into the interim table -> creates a snapshot table MLOG$_EMP and a snapshot log to store DML changes temporarily until the final step of the redefinition Step 4: Create the constraints, indexes and triggers on the interim table as they are defined on the source table. Any referential constraint involving the interim table should be created disabled. Step 5: Finish the redefinition of the source table using the dbms_redefinition.finish_redef_table procedure. The finish_redef_table procedure automatically: -> applies all DML changes stored in the snapshot table into the interim table -> exchanges the names of the 2 tables: the interim table becomes the source table and vice versa Be aware that the names of the constraints, indexes, and triggers do not have the names they had on the source table. Step 6: Drop the interim table Step 7: From RDBMS version 9.2, the constraints and indexes can be renamed with the ALTER TABLE ... RENAME CONSTRAINT ... statement. In RDBMS version 8.x, and 9.0.x, if you want the restructured table to have the initial names for the constraints and indexes, you have to rebuild them, and for the triggers, you have to recreate them.