更改表列类型 oracle,如何通过在线重定义修改表的字段类型

最新推荐文章于 2024-02-01 16:19:17 发布
最新推荐文章于 2024-02-01 16:19:17 发布
阅读量254 收藏
点赞数
文章标签： 更改表列类型 oracle

最近有个需求，需要把一张表的一个字段由number型改为varchar2型，由于表里已经存在数据，直接改会报错。而且开发那边说，这个表要一直可以访问，不能停应用。

我想到了在线重定义，之前我每次用它都是来把堆表转换为分区表的，没有做过这种字段类型转换。我在测试库上完整的测试了两次，很成功。重定义的过程中，表一直都可以

访问，不影响DML操作。而且整个过程中索引、约束、包都为可用状态。

需求：把表SUR_SURVEY的TARGET_ID字段，类型由number型改为varchar2(256);

以下是我的脚本，大家看看可不可以这样做。

一、        ---------------创建中间表SUR_SURVEY_TBD，注意此处的建表语句要通过dbms_metadata.get_ddl来获得后，做一些修改，表名改为SUR_SURVEY_TBD，TARGET_ID字段类

型改为varchar2(256)。这种方式的建表语句比较全，默认值啦，主键啦都包含在内。

CREATE TABLE SUR_SURVEY_TBD

(    "ID" NUMBER(19,0) NOT NULL ENABLE,

"GMT_CREATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,

"CREATOR" VARCHAR2(32) DEFAULT '[SYS]' NOT NULL ENABLE,

"GMT_MODIFIED" DATE DEFAULT SYSDATE NOT NULL ENABLE,

"MODIFIER" VARCHAR2(32) DEFAULT '[SYS]' NOT NULL ENABLE,

"IS_DELETED" CHAR(1) DEFAULT 'n' NOT NULL ENABLE,

"QT_SET_ID" NUMBER(12,0) NOT NULL ENABLE,

"TARGET_ID" VARCHAR2(256) NOT NULL ENABLE,

"TARGET_TYPE" VARCHAR2(128) NOT NULL ENABLE,

"TARGET_NAME" VARCHAR2(512),

"GMT_SURVEY" DATE,

"SUBMITTER" VARCHAR2(128),

"IS_FINISHED" CHAR(1) DEFAULT 0 NOT NULL ENABLE,

"CUR_PAGE_NO" NUMBER(4,0) NOT NULL ENABLE,

"ADMIN_ROLE" VARCHAR2(128),

"CUSTOMER_ID" VARCHAR2(128),

"NOTE_ID" VARCHAR2(128),

"SRV_ID" VARCHAR2(128),

"SERVICE_ID" VARCHAR2(128),

"COL1" VARCHAR2(255),

"COL2" VARCHAR2(255),

"COL3" NUMBER(8,0),

"COL4" DATE,

"MEMBER_ID" VARCHAR2(128),

CONSTRAINT "SUR_SURVEY_PK_TBD" PRIMARY KEY ("ID"));

二、        ------调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程，如果表不满足重定义的条件，将会报错并给出原因。

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'SUR_SURVEY', DBMS_REDEFINITION.CONS_USE_PK);

三、        ------调用DBMS_REDEFINITION.START_REDEF_TABLE()过程，并提供下列参数：被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。注意红色字体部分是重点。

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'SUR_SURVEY', 'SUR_SURVEY_TBD','ID ID, GMT_CREATE GMT_CREATE,CREATOR CREATOR,GMT_MODIFIED GMT_MODIFIED,MODIFIER MODIFIER,IS_DELETED IS_DELETED,QT_SET_ID QT_SET_ID,to_char(TARGET_ID) TARGET_ID,TARGET_TYPE TARGET_TYPE,TARGET_NAME TARGET_NAME,GMT_SURVEY GMT_SURVEY,SUBMITTER SUBMITTER,IS_FINISHED IS_FINISHED,CUR_PAGE_NO CUR_PAGE_NO,ADMIN_ROLE ADMIN_ROLE,CUSTOMER_ID CUSTOMER_ID,NOTE_ID NOTE_ID,SRV_ID SRV_ID,SERVICE_ID SERVICE_ID,COL1 COL1,COL2 COL2,COL3 COL3,COL4 COL4,MEMBER_ID MEMBER_ID', DBMS_REDEFINITION.CONS_USE_PK);

四、        -------在中间表上创建索引，也可以在步骤一来做，不过这样做的效率高点。因为步骤一创建了索引，步骤三的插入操作会变慢。

CREATE INDEX SUR_SURVEY_TID_IND_TBD ON  SUR_SURVEY_TBD(TARGET_ID) TABLESPACE APOLLO_IND;

CREATE INDEX SUR_SURVEY_QSID_IND_TBD ON  SUR_SURVEY_TBD(QT_SET_ID) TABLESPACE APOLLO_IND;

CREATE INDEX SUR_SURVEY_G_IND_TBD ON  SUR_SURVEY_TBD(GMT_SURVEY) TABLESPACE APOLLO_IND;

CREATE INDEX SUR_SURVEY_CID_IND_TBD ON  SUR_SURVEY_TBD(CUSTOMER_ID) TABLESPACE APOLLO_IND;

五、        -------如果操作期间对表T有大量DML操作，可以执行SYNC_INTERIM_TABLE来减少finsh_redef_table的锁表时间。

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(user,'SUR_SURVEY','SUR_SURVEY_TBD');

六、        -------执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'SUR_SURVEY', 'SUR_SURVEY_TBD');

七、        ------确认转换成功，红色字体部分。

weixh@CRMG>desc SUR_SURVEY

Name                                                                                Null?    Type

----------------------------------------------------------------------------------- -------- --------------------------------------------------------

ID                                                                                  NOT NULL NUMBER(19)

GMT_CREATE                                                                          NOT NULL DATE

CREATOR                                                                             NOT NULL VARCHAR2(32)

GMT_MODIFIED                                                                        NOT NULL DATE

MODIFIER                                                                            NOT NULL VARCHAR2(32)

IS_DELETED                                                                          NOT NULL CHAR(1)

QT_SET_ID                                                                           NOT NULL NUMBER(12)

TARGET_ID                                                                          NOT NULL VARCHAR2(256)

weixh@CRMG>SELECT COUNT(*) FROM SUR_SURVEY;

COUNT(*)

----------

5557401

八、        ------重新编译依赖对象

alter PROCEDURE SURVEY.PRO_SURVEY_REPORT compile;

alter SYNONYM SURVEY.SUR_SURVEY compile;

九、        -----删除中间表

drop table SUR_SURVEY_TBD;

十、        -------对索引进行重命名，这个步骤根据需要可以省略，不过这个过程非常快，一两秒就可以完事，提前把脚本准备好。

ALTER INDEX  SUR_SURVEY_TID_IND_TBD  RENAME TO   SUR_SURVEY_TID_IND ;

ALTER INDEX  SUR_SURVEY_QSID_IND_TBD RENAME TO   SUR_SURVEY_QSID_IND;

ALTER INDEX  SUR_SURVEY_G_IND_TBD    RENAME TO   SUR_SURVEY_GMTSURVEY_IND;

ALTER INDEX  SUR_SURVEY_CID_IND_TBD  RENAME TO   SUR_SURVEY_CID_IND;

ALTER INDEX  SUR_SURVEY_PK_TBD       RENAME TO   SUR_SURVEY_PK;

alter table SUR_SURVEY drop constraint SUR_SURVEY_PK_TBD cascade;

alter table SUR_SURVEY add constraint SUR_SURVEY_PK primary key (ID) using index;

重定义过程中如果有报错，采取如下操作取消：

weixh@CRMG>EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE(user,'SUR_SURVEY','SUR_SURVEY_TBD');

PL/SQL procedure successfully completed.

整个过程中，索引，约束都为可用状态，以下是我在各个阶段的查询输出。：

weixh@CRMG>select constraint_TYPE,constraint_NAME, STATUS from user_constraints where table_name='SUR_SURVEY' and owner='WEIXH';------------步骤三

CO CONSTRAINT_NAME                                              STATUS

-- ------------------------------------------------------------ ----------------

C  SYS_C0085198                                                 ENABLED

C  SYS_C0085199                                                 ENABLED

C  SYS_C0085200                                                 ENABLED

C  SYS_C0085201                                                 ENABLED

C  SYS_C0085202                                                 ENABLED

C  SYS_C0085203                                                 ENABLED

C  SYS_C0085204                                                 ENABLED

C  SYS_C0085205                                                 ENABLED

C  SYS_C0085206                                                 ENABLED

C  SYS_C0085207                                                 ENABLED

C  SYS_C0085208                                                 ENABLED

P  SUR_SURVEY_PK                                                ENABLED

12 rows selected.

weixh@CRMG>select index_name ,status from user_indexes where table_name='SUR_SURVEY'; ------------步骤三

INDEX_NAME                                                   STATUS

------------------------------------------------------------ ----------------

SUR_SURVEY_PK                                                VALID

SUR_SURVEY_QSID_IND                                          VALID

SUR_SURVEY_TID_IND                                           VALID

SUR_SURVEY_GMTSURVEY_IND                                     VALID

SUR_SURVEY_CID_IND                                           VALID

weixh@CRMG>select index_name ,status from user_indexes where table_name='SUR_SURVEY_TBD';-------步骤四

INDEX_NAME                                                   STATUS

------------------------------------------------------------ ----------------

SUR_SURVEY_PK_TBD                                            VALID

SUR_SURVEY_TID_IND_TBD                                       VALID

SUR_SURVEY_QSID_IND_TBD                                      VALID

SUR_SURVEY_G_IND_TBD                                         VALID

SUR_SURVEY_CID_IND_TBD                                       VALID

weixh@CRMG>select constraint_TYPE,constraint_NAME, STATUS from user_constraints where table_name='SUR_SURVEY_TBD' and owner='WEIXH'; -------步骤四

CO CONSTRAINT_NAME                                              STATUS

-- ------------------------------------------------------------ ----------------

C  SYS_C0085210                                                 ENABLED

C  SYS_C0085211                                                 ENABLED

C  SYS_C0085212                                                 ENABLED

C  SYS_C0085213                                                 ENABLED

C  SYS_C0085214                                                 ENABLED

C  SYS_C0085215                                                 ENABLED

C  SYS_C0085216                                                 ENABLED

C  SYS_C0085217                                                 ENABLED

C  SYS_C0085218                                                 ENABLED

C  SYS_C0085219                                                 ENABLED

C  SYS_C0085220                                                 ENABLED

P  SUR_SURVEY_PK_TBD                                            ENABLED

12 rows selected.

weixh@CRMG>select index_name ,status from user_indexes where table_name='SUR_SURVEY'; ------------步骤五

INDEX_NAME                                                   STATUS

------------------------------------------------------------ ----------------

SUR_SURVEY_PK                                                VALID

SUR_SURVEY_QSID_IND                                          VALID

SUR_SURVEY_TID_IND                                           VALID

SUR_SURVEY_GMTSURVEY_IND                                     VALID

SUR_SURVEY_CID_IND                                           VALID

weixh@CRMG>select constraint_TYPE,constraint_NAME, STATUS from user_constraints where table_name='SUR_SURVEY' and owner='WEIXH'; ------------步骤五

CO CONSTRAINT_NAME                                              STATUS

-- ------------------------------------------------------------ ----------------

C  SYS_C0085198                                                 ENABLED

C  SYS_C0085199                                                 ENABLED

C  SYS_C0085200                                                 ENABLED

C  SYS_C0085201                                                 ENABLED

C  SYS_C0085202                                                 ENABLED

C  SYS_C0085203                                                 ENABLED

C  SYS_C0085204                                                 ENABLED

C  SYS_C0085205                                                 ENABLED

C  SYS_C0085206                                                 ENABLED

C  SYS_C0085207                                                 ENABLED

C  SYS_C0085208                                                 ENABLED

P  SUR_SURVEY_PK                                                ENABLED

12 rows selected.

weixh@CRMG>select index_name ,status from user_indexes where table_name='SUR_SURVEY'; ------------步骤六

INDEX_NAME                                                   STATUS

------------------------------------------------------------ ----------------

SUR_SURVEY_PK_TBD                                            VALID

SUR_SURVEY_TID_IND_TBD                                       VALID

SUR_SURVEY_QSID_IND_TBD                                      VALID

SUR_SURVEY_G_IND_TBD                                         VALID

SUR_SURVEY_CID_IND_TBD                                       VALID

weixh@CRMG>select constraint_TYPE,constraint_NAME, STATUS from user_constraints where table_name='SUR_SURVEY' and owner='WEIXH'; ------------步骤六

CO CONSTRAINT_NAME                                              STATUS

-- ------------------------------------------------------------ ----------------

C  SYS_C0085210                                                 ENABLED

C  SYS_C0085211                                                 ENABLED

C  SYS_C0085212                                                 ENABLED

C  SYS_C0085213                                                 ENABLED

C  SYS_C0085214                                                 ENABLED

C  SYS_C0085215                                                 ENABLED

C  SYS_C0085216                                                 ENABLED

C  SYS_C0085217                                                 ENABLED

C  SYS_C0085218                                                 ENABLED

C  SYS_C0085219                                                 ENABLED

C  SYS_C0085220                                                 ENABLED

P  SUR_SURVEY_PK_TBD                                            ENABLED

12 rows selected.

weixin_42501711
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle数据库经典题目
02-17
5. 说明数据库表空间的种类，以及不同类型表空间的作用。 答案： 数据库表空间分为系统表空间和非系统表空间两类，其中非系统表空间包括撤销表空间、临时表空间和用户表空间等。 SYSTEM表空间主要用于存储数据库的...
Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码
02-04
作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：...
Oracle在线重定义修改字段脚本_在线修改字段类型_dbms_redefinition
BIKE AND DB
01-14 688
-- 创建测试数据 -- 创建一张临时表 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...
Oracle修改字段类型方法总结
热门推荐
gdjlc的专栏
04-15 21万+
Oracle修改字段类型 有一个表名为tb，字段段名为name，数据类型nchar(20)。 1、假设字段数据为空，则不管改为什么字段类型，可以直接执行： alter table tb modify (name nvarchar2(20)); 2、假设字段有数据，则改为nvarchar2(20)可以直接执行： alter table tb modify (name n
oracle 修改字段类型
最新发布
qq_42601385的博客
02-01 322
【代码】oracle 修改字段类型。
Oracle 修改字段类型
dragon4917的博客
03-09 6658
修改 Oracle 表中字段类型，在该字段存在值的情况下，会提示：ORA-01439:要更改数据类型,则要修改的列必须为空 这时就需要区分情况处理。 数据量比较小的时候，可以直接操作目标表。 重命名字段名，添加新字段，复制原始字段数据，删除重命名字段。 /* 表字段重命名 */ ALTER TABLE table_name RENAME COLUMN column_name TO column_name_tmp; /* 新增原始字段同名字段 */ ALTER TABLE table_name ADD
oracle 修改某个字段的数据类型三种方式
jihuanliang的专栏
04-21 5916
1.将该列设置为null,再修改其类型(这样会丢失数据) 2.最简单的方法，新增加一张表。(但这样可能造成新表与原来的表约束不一致) 假设你的表名为 tab_target create table test  as select * from tab_target where 1=2; alter table test modify (col_name number(5));
AppFramework_V1.0
12-13
而IBatisNet若要返回不同的字段就要定义多套ResultMap，否则就定义一套所有字段的ResultMap，任何查询都返回所有字段。这样无疑浪费了数据库服务器与应用服务器之间的网络带宽，在进行海量数据访问时性能将严重降低...
AppFramework_V1.0_New
12-13
而IBatisNet若要返回不同的字段就要定义多套ResultMap，否则就定义一套所有字段的ResultMap，任何查询都返回所有字段。这样无疑浪费了数据库服务器与应用服务器之间的网络带宽，在进行海量数据访问时性能将严重降低...
oracle 如何修改表字段的类型或者长度
qq_43077362的博客
11-01 5517
分两种情况： 1.表中没有数据的情况，直接一条sql语句就能解决 alter table 表名 modify(字段名 字段类型 ) 例: alter table A modify(name varchar2(4000)) 2.在表中有数据的情况下，不能直接使用modify，否则会报错，如图 思路分4段，先将原字段改个名字，然后新增一个列，这个新增的列名跟原字段列...
Oracle修改字段类型的方法
苦逼的程序猿☺
11-07 934
转自：Oracle修改字段类型方法总结需求有一个表名为tb，字段段名为name，数据类型nchar(20)，现需要将其修改为其他类型！方法1、假设字段数据为空，则不管改为什么字段类型，可以直接执行：alter table tb modify (name nvarchar2(20));2、假设字段有数据，则改为nvarchar2(20)可以直接执行：alter table tb modify (nam
Oracle 解决表中已有数据，我们需要修改字段的数据类型问题
z2742431760的博客
09-20 1365
直接修改会报错：ALTER TABLE T_HIS_PAYMENTS MODIFY ENTITYID varchar2(32)ORA-01439: 要更改数据类型, 则要修改的列必须为空¶。
oracle中number和varchar2的自动转换的情况
一个逻辑混乱的猴子
03-10 1万+
（转） 今天测试分区索引，发现oracle智能化的一方面：若列为number类型，用其作为where条件时，变量为varchar2类型时会自动将变量转换为number类型，而不是将列转换为number类型。 例如，表testn存在number类型的列a，使用该列做为查询条件时： 若变量为varchar2类型，且可合法地转换成number类型，则自动将变量转换为number类型，而不会转换a列
表中有数据如何修改表字段类型
weixin_42261726的博客
04-27 2657
这是我在网上百度的： 更改字段类型 ： alter table 表名 alter column 字段名 更改后的类型 例：把城市表的城市名字段有原来的varchar类型改为int类型 alter table city alter column cityname int; 然后因为我表里有数据报错：ORA-01735: 无效的 ALTER TABLE 选项 百度原因：注意这里如果是小改大，没有问题...
Oracle 11g 在线重定义
weixin_34409822的博客
07-24 234
在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表的重定义，因此表重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线重定义...
在线修改表中列的类型
阿飞
07-06 1011
在线上修改有数据的列的类型。 首先建立表结构，在建表时把对应列的属性改了 --------------------------------------- Insert Into T_HP_ROOM_TYPE_2 Select * From T_HP_ROOM_TYPE Rename T_HP_ROOM_TYPE To T_HP_ROOM_TYPE_1; Rename T_HP_RO
oracle修改表的字段类型
04-21
在Oracle中修改表的字段类型可以使用ALTER TABLE语句，例如： ALTER TABLE table_name MODIFY column_name new_data_type; 其中，table_name是要修改字段类型的表名，column_name是要修改类型的字段名，new_data_...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值