11G DBMS_REDEFINITION修改表数据类型
1.获取源表结构信息
SYS@honor1 > SET LONG 999999
SYS@honor1 > SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_REDE','HR') FROM DUAL;
CREATE TABLE "HR"."TEST_REDE"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" VARCHAR2(20),
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
)
TABLESPACE "USERS";
2.验证是否可以在线重定义
# 由于表没有主键,所以只能使用ROWID,如果表有主键,可以删掉CONS_USE_ROWID,默认使用PK
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('HR','TEST_REDE',
DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
3.创建中间表,修改需要修改的列数据类型
CREATE TABLE "HR"."TEST_REDE_INTER"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" VARCHAR2(20),
"DATA_OBJECT_ID" VARCHAR2(10), --需要修改后的数据类型
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
)
TABLESPACE "USERS";
4.开始在线重定义
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'HR',
orig_table => 'TEST_REDE',
int_table => 'TEST_REDE_INTER',
col_mapping =>
'OWNER OWNER,
OBJECT_NAME OBJECT_NAME,
SUBOBJECT_NAME SUBOBJECT_NAME,
OBJECT_ID OBJECT_ID,
to_char(DATA_OBJECT_ID) DATA_OBJECT_ID, --由于更改数据类型,需要手工转换
OBJECT_TYPE OBJECT_TYPE,
CREATED CREATED,
LAST_DDL_TIME LAST_DDL_TIME,
TIMESTAMP TIMESTAMP,
STATUS STATUS,
TEMPORARY TEMPORARY,
GENERATED GENERATED,
SECONDARY SECONDARY,
NAMESPACE NAMESPACE,
EDITION_NAME EDITION_NAME',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
5.同步中间数据,减少finish_redef_table时间
begin
dbms_redefinition.sync_interim_table(uname => '&USERNAME',
orig_table => '&SOURCE_TAB',
int_table => '&INT_TAB');
end;
/
6.复制相关约束,依赖对象
# 如果更改了列名,或者增加列,必要时,手工创建相关索引等对象
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => '&USERNAME',
orig_table => '&SOURCE_TAB',
int_table => '&INT_TAB',
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => num_errors,
copy_statistics => TRUE);
END;
/
7.完成在线重定义
begin
dbms_redefinition.finish_redef_table(uname => '&USERNAME',
orig_table => '&SOURCE_TAB',
int_table => '&INT_TAB');
end;
/
参考: