oracle大表字段类型修改在线重定义脚本

袁长刚(Spanner)

2122

3页

26次

2020-03-14

5墨值下载

-- 创建测试数据

-- 创建一张临时表

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';

-- 并行度，建议设为逻辑 cpu 的一半，如果是分区表，cpu 够的话建议设为分区数

define PARALLELS = 4;

-----------分割线-执行步骤---------------------

-- 1.打开并行

ALTER SESSION ENABLE PARALLEL DML ;

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;

-- 创建测试数据

-- 创建一张临时表

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';

-- 并行度，建议设为逻辑 cpu 的一半，如果是分区表，cpu 够的话建议设为分区数

define PARALLELS = 4;

-----------分割线-执行步骤---------------------

-- 1.打开并行

ALTER SESSION ENABLE PARALLEL DML ;

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;

of 3

5墨值下载

oracle 大表字段类型修改日常运维

关注

评论