Oracle在线重定义修改字段脚本_在线修改字段类型_dbms_redefinition

-- 创建测试数据
--  创建一张临时表
create table tbl_yy as 
select rownum as id,
               to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
               trunc(dbms_random.value(0, 10000)) as random_id,
               dbms_random.string('x', 20) random_string
          from dual
        connect by level <= 10000;
commit;

-- 如果觉得数据不够，可以再继续插入数据测试
insert into tbl_yy
  (ID, INC_DATETIME,RANDOM_ID,RANDOM_STRING)
  select rownum as id,
         to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
         trunc(dbms_random.value(0, 100)) as random_id,
         dbms_random.string('x', 20) random_string
    from dual
  connect by level <= 100000;
  commit;


-- 创建临时表
-- 临时表会占用主表同样的大小，要检查好表空间容量。
create table int_tbl_yy as select * from tb_yy where rownum = 0;
-- 修改字段类型，number改成varchar2
alter table int_tbl_yy modify RANDOM_ID varchar2(22);



-- 全局配置，变量替换，如下直接复制到sqlplus或工具执行。
-- 临时表会占用主表同样的大小，要检查好表空间容量。
-- 生产环境不要在业务高峰期做操作
-- 用户名
define USERNAME = 'yy';
-- 原表名
define SOURCE_TAB = 'tbl_yy';
-- 临时表名，需要手工提前创建
define INT_TAB = 'int_tbl_yy';
-- 映射表的字段名
-- 由于define最大长度只支持240个字符，映射要在第4点自己配置col_mapping这个参数,如下是生成的方法，注意手工去掉最后一个逗号,
-- 注意类型转换，如id(varchar2 要转成id(number) ，映射的时候需要保证字段类型一致，使用to_number解决，如：to_number(id) id,
-- select listagg(column_name || ' ' || column_name || ',' ) col_name  from user_tab_columns where table_name='&SOURCE_TAB';
-- 并行度
define PARALLELS = 4;


-----------分割线-执行步骤---------------------
-- 1.打开并行
alter session force parallel dml parallel &PARALLELS;
alter session force parallel query parallel &PARALLELS;

-- 2.重定义为分区，使用rowid，建议为临时表开启行迁移
alter table &INT_TAB enable row movement;

-- 3.检查dbms_redefinition是否在这个表上工作
--基于主键
-- exec  DBMS_REDEFINITION.CAN_REDEF_TABLE('custinfo','fm',DBMS_REDEFINITION.CONS_USE_PK);
--基于rowid
begin
    dbms_redefinition.can_redef_table(uname        => '&USERNAME',
                                        tname        => '&SOURCE_TAB',
                                        options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

-- 4.调用DBMS_REDEFINITION.START_REDEF_TABLE存储过程启动重定义进程
-- 接下来开始重新定义
-- BEGIN
-- DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>'custinfo', ORIG_TABLE=>'fm',INT_TABLE=>'int_fm',OPTIONS_FLAG=>dbms_redefinition.cons_use_pk);
-- END;
-- 基于rowid
-- number , varchar2 互转要用to_char 或to_number 做映射。
-- select listagg(column_name || ' ' || column_name || ',' )  from dba_tab_columns where table_name=upper('&SOURCE_TAB');
set timing on;
begin
  DBMS_REDEFINITION.START_REDEF_TABLE(uname        => '&USERNAME',
                                      orig_table   => '&SOURCE_TAB',
                                      int_table    => '&INT_TAB',
                                      col_mapping  => 'ID ID,INC_DATETIME INC_DATETIME,to_char(RANDOM_ID) RANDOM_ID,RANDOM_STRING RANDOM_STRING',
                                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

-- 5.复制依赖对象。(自动创建任何触发器、索引、物化视图日志、授予和对 custinfo.int_fm 的约束)。
-- copy_indexes     => 0 索引报错可以设置为0
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;
/

-- 6.利用sync_interim_table过程同步临时表的数据减少finish_redef_table的耗时:
begin
    dbms_redefinition.sync_interim_table(uname        => '&USERNAME',
                                           orig_table => '&SOURCE_TAB',
                                           int_table  => '&INT_TAB');
end;
/

-----------分割线-后面二步是结束操作，执行后会做最后阶段的切换------------
-- 7.执行finish_redef_table过程完成重定义：
begin
dbms_redefinition.finish_redef_table(uname      => '&USERNAME',
                                     orig_table => '&SOURCE_TAB',
                                     int_table  => '&INT_TAB');
end;
/

-- 关掉并行
-- 以上成功完成了对SALES表的Online Redefinition，由非分区表在线重定义为分区表且增加了一个字段。
-- 这里因为我们使用rowid方式，所以重定义完的表上会多出一个隐藏字段， 从10.2开始M_ROW$$的隐藏列会被命名为SYS_%DATE%的形式，且默认即为unused状态：
alter session force parallel dml parallel 1;
alter session force parallel query parallel 1;
alter table &SOURCE_TAB drop unused columns;
select * from dba_unused_col_tabs ;

-- 删除临时表
alter table &INT_TAB nologging;
drop table &INT_TAB;

-------------------------分割线-结束--------------------------------

-- 异常处理
-- 如果失败了可以取消
-- begin
-- dbms_redefinition.abort_redef_table(
-- uname      => '&USERNAME',
-- orig_table => '&SOURCE_TAB',
-- int_table  => '&INT_TAB');
-- end;
-- /