oracle 9i在线重定义,Oracle在线重定义修改字段脚本_在线修改字段类型_dbms_redefinition...

落日映苍穹つ 2022-10-21 01:25 122阅读 0赞

– 创建测试数据

– 创建一张临时表

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;

– /

oracle 9i在线重定义,Oracle在线重定义修改字段脚本_在线修改字段类型_dbms_redefinition...

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ORACLE 添加、删除、修改字段

相关 oracle 11g 在线重定义（online redefinition）介绍

相关 Oracle由Varchar2字段类型修改为Clob字段类型

相关 oracle表在线重定义,Oracle的在线重定义表功能（二）

相关 oracle 9i在线重定义,Oracle在线重定义修改字段脚本_在线修改字段类型_dbms_redefinition...

相关 oracle 语句修改字段类型

相关 rowid方式在线重定义

相关 Oracle 增加修改删除字段

相关 oracle在线重定义

相关 Oracle修改字段类型Sql --ORA-01439