11 g DBMS_REDEFINITION修改表数据类型
1。获取源表结构信息
,SYS@honor1 的在SET LONG 999999 SYS@honor1 祝辞,SELECT DBMS_METADATA.GET_DDL(& # 39;表# 39;& # 39;TEST_REDE& # 39;, & # 39;人力资源# 39;),得到双; CREATE TABLE “HR" !”TEST_REDE" (才能,,,“称,VARCHAR2 (30), ,,,,,,“OBJECT_NAME", VARCHAR2 (128), ,,,,,,“SUBOBJECT_NAME", VARCHAR2 (30), ,,,,,,“OBJECT_ID", VARCHAR2 (20), ,,,,,,“DATA_OBJECT_ID",, ,,,,,,“OBJECT_TYPE", VARCHAR2 (19), ,,,,,,“CREATED",日期, ,,,,,,“LAST_DDL_TIME",日期, ,,,,,,“TIMESTAMP", VARCHAR2 (19), ,,,,,,“STATUS", VARCHAR2 (7), ,,,,,,“TEMPORARY", VARCHAR2 (1), ,,,,,,“GENERATED", VARCHAR2 (1), ,,,,,,“SECONDARY", VARCHAR2 (1), ,,,,,,“NAMESPACE",, ,,,,,,“EDITION_NAME", VARCHAR2 (30) ),才能 ,TABLESPACE “USERS";
2。验证是否可以在线重定义
#,由于表没有主键,所以只能使用ROWID,如果表有主键,可以删掉CONS_USE_ROWID,默认使用PK 开始 DBMS_REDEFINITION.CAN_REDEF_TABLE(& # 39;人力资源# 39;,& # 39;TEST_REDE& # 39;, DBMS_REDEFINITION.CONS_USE_ROWID); 结束;/
3。创建中间表,修改需要修改的列数据类型
<>以前CREATE TABLE “HR"干净TEST_REDE_INTER"
(,,,“称,VARCHAR2 (30),
,,,,“OBJECT_NAME", VARCHAR2 (128),
,,,,“SUBOBJECT_NAME", VARCHAR2 (30),
,,,,“OBJECT_ID", VARCHAR2 (20),
,,,,“DATA_OBJECT_ID", VARCHAR2(10),,,,,,,,,,,需要修改后的数据类型
,,,,“OBJECT_TYPE", VARCHAR2 (19),
,,,,“CREATED",日期,
,,,,“LAST_DDL_TIME",日期,
,,,,“TIMESTAMP", VARCHAR2 (19),
,,,,“STATUS", VARCHAR2 (7),
,,,,“TEMPORARY", VARCHAR2 (1),
,,,,“GENERATED", VARCHAR2 (1),
,,,,“SECONDARY", VARCHAR2 (1),
,,,,“NAMESPACE",,
,,,,“EDITION_NAME", VARCHAR2 (30)
)
TABLESPACE “USERS";
4。开始在线重定义,
<>以前alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
开始
DBMS_REDEFINITION.START_REDEF_TABLE (
uname =祝辞,& # 39;人力资源# 39;
orig_table =祝辞,& # 39;TEST_REDE& # 39;
int_table =祝辞,& # 39;TEST_REDE_INTER& # 39;
时间=祝辞col_mapping
& # 39;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 创建,
LAST_DDL_TIME LAST_DDL_TIME,
TIMESTAMP 时间戳,
STATUS 状态,
TEMPORARY 暂时的,
GENERATED 生成的,
SECONDARY 二次,
NAMESPACE 名称空间,
EDITION_NAME EDITION_NAME& # 39;
options_flag =祝辞,DBMS_REDEFINITION.CONS_USE_ROWID);
结束;/
5。同步中间数据,减少finish_redef_table时间
<>之前开始
,,,dbms_redefinition.sync_interim_table (uname ,,,,,,,=祝辞,& # 39;和用户名,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,orig_table =祝辞,& # 39;及,SOURCE_TAB& # 39;
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,int_table ,=祝辞,& # 39;,INT_TAB& # 39;);
结束;/
6。复制相关约束,依赖对象
#,如果更改了列名,或者增加列,必要时,手工创建相关索引等对象 声明 ,,,num_errors PLS_INTEGER; 开始 null null null null null null null null null null null null null null null null null null null null null null null null nullOracle 11 g DBMS_REDEFINITION修改表数据类型
