oracle 大表 改字段类型,oracle修改表字段的数据类型测试

oracle修改表字段的数据类型的测试

思路

将要更改类型的字段名改名新字段以备份；

根据需求，添加一个与要更改类型的字段名同名的字段(原字段已经改名)，

然后更新数据，

最后删除改名备份的字段

脚本如下：

/*修改原字段名*/

ALTER TABLE 表名 RENAME COLUMN 字段名 TO 字段名1;

/*添加一个和原字段同名的字段*/

ALTER TABLE 表名 ADD 字段名  VARCHAR2(30);

/*将原来的数据更新到新字段中一定要显示进行数据类型转换*/

UPDATE 表名 SET 字段名 = CAST(字段名1 AS VARCHAR2(30));

/*删除原来的备份字段*/

ALTER TABLE 表名 DROP COLUMN 字段名1;

测试如下：

1.备份表

SQL> create table tt_bak_01 as select *  from tt;

Table created.

2.想偷懒，现实直接修改字段如下： SQL> alter table tt modify (DATA_OBJECT_ID number);

alter table tt modify (DATA_OBJECT_ID number)

*

ERROR at line 1:

ORA-01439: column to be modified must be empty to change datatype

不行，只得转变方法

3.检查表结构

SQL> desc tt

Name                                                                 Null?    Type

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

OWNER                                                                         VARCHAR2(30)

OBJECT_NAME                                                                   VARCHAR2(128)

SUBOBJECT_NAME                                                                VARCHAR2(30)

OBJECT_ID                                                                     NUMBER

DATA_OBJECT_ID                                                                NUMBER

OBJECT_TYPE                                                                   VARCHAR2(19)

CREATED                                                                       DATE

LAST_DDL_TIME                                                                 DATE

TIMESTAMP                                                                     VARCHAR2(19)

STATUS                                                                        VARCHAR2(7)

TEMPORARY                                                                     VARCHAR2(1)

GENERATED                                                                     VARCHAR2(1)

SECONDARY                                                                     VARCHAR2(1)

4.备份需要修改的字段

SQL> ALTER TABLE tt RENAME COLUMN DATA_OBJECT_ID to DATA_OBJECT_ID01;

Table altered.

SQL> desc tt

Name                                                                 Null?    Type

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

OWNER                                                                         VARCHAR2(30)

OBJECT_NAME                                                                   VARCHAR2(128)

SUBOBJECT_NAME                                                                VARCHAR2(30)

OBJECT_ID                                                                     NUMBER

OBJECT_TYPE                                                                   VARCHAR2(19)

CREATED                                                                       DATE

LAST_DDL_TIME                                                                 DATE

TIMESTAMP                                                                     VARCHAR2(19)

STATUS                                                                        VARCHAR2(7)

TEMPORARY                                                                     VARCHAR2(1)

GENERATED                                                                     VARCHAR2(1)

SECONDARY                                                                     VARCHAR2(1)

DATA_OBJECT_ID                                                                VARCHAR2(100)

5.根据业务需求，添加合适的字段以及相应数据类型

SQL> ALTER TABLE tt ADD DATA_OBJECT_ID VARCHAR2(100);

Table altered.

SQL> desc tt

Name                                                                 Null?    Type

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

OWNER                                                                         VARCHAR2(30)

OBJECT_NAME                                                                   VARCHAR2(128)

SUBOBJECT_NAME                                                                VARCHAR2(30)

OBJECT_ID                                                                     NUMBER

DATA_OBJECT_ID01                                                              NUMBER

OBJECT_TYPE                                                                   VARCHAR2(19)

CREATED                                                                       DATE

LAST_DDL_TIME                                                                 DATE

TIMESTAMP                                                                     VARCHAR2(19)

STATUS                                                                        VARCHAR2(7)

TEMPORARY                                                                     VARCHAR2(1)

GENERATED                                                                     VARCHAR2(1)

SECONDARY                                                                     VARCHAR2(1)

DATA_OBJECT_ID                                                                VARCHAR2(100)

6.更新字段数据到新修改的字段中

SQL> UPDATE tt SET  DATA_OBJECT_ID = CAST(DATA_OBJECT_ID01 AS VARCHAR2(100));

390 rows updated.

SQL>

检查：

SQL> select DATA_OBJECT_ID01 from tt

minus

2    3  select to_number(DATA_OBJECT_ID) from tt;

no rows selected

7.删除备份的字段结构

SQL> ALTER TABLE tt DROP COLUMN  DATA_OBJECT_ID01;

Table altered.

SQL> desc tt;

Name                                                                 Null?    Type

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

OWNER                                                                         VARCHAR2(30)

OBJECT_NAME                                                                   VARCHAR2(128)

SUBOBJECT_NAME                                                                VARCHAR2(30)

OBJECT_ID                                                                     NUMBER

OBJECT_TYPE                                                                   VARCHAR2(19)

CREATED                                                                       DATE

LAST_DDL_TIME                                                                 DATE

TIMESTAMP                                                                     VARCHAR2(19)

STATUS                                                                        VARCHAR2(7)

TEMPORARY                                                                     VARCHAR2(1)

GENERATED                                                                     VARCHAR2(1)

SECONDARY                                                                     VARCHAR2(1)

DATA_OBJECT_ID                                                                VARCHAR2(100)

SQL> select count(*) from tt;

COUNT(*)

----------

390

By 老白菜

=========================================================================

-- The End --