修改字段数据类型的方法

在日常维护的过程中可能碰到这样的问题，需要修改表的字段类型。

对于绝大部分正常的情况，都是将表的字段类型的长度扩大，但是有的时候是需要缩小表的字段长度的，甚至有的时候是要修改表的数据类型的。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> DROP TABLE T PURGE;

表已删除。

SQL> CREATE TABLE T ASITPUB个人空间0_R6r2C,B
  2  SELECT OBJECT_ID, OWNER, OBJECT_NAME
%_1i.V%w-y&|tv0  3  FROM DBA_OBJECTS;

表已创建。

SQL> DESC TITPUB个人空间{a]+b$hz)dw
 名称                                     是否为空?类型
;We!S+irJ&~0 ----------------------------------------- -------- -------------------------
*N._j4P4F`a0 OBJECT_ID                                          NUMBERITPUB个人空间(J)_3[(D9kJ~5Uoc\
 OWNER                                              VARCHAR2(30)ITPUB个人空间(N"{:N8lS{/Q$e6b e
 OBJECT_NAME                                        VARCHAR2(128)

SQL> SELECT MAX(LENGTH(TO_CHAR(OBJECT_ID))) FROM T;

MAX(LENGTH(TO_CHAR(OBJECT_ID)))ITPUB个人空间v9`9W4~x/eT
-------------------------------ITPUB个人空间z:ola/i
                              5

SQL> ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10);
nC(US{x0ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10)
i L%@(V5|VU#]~0                     *ITPUB个人空间9||Y6h.B}C,j
第1行出现错误:
iRDb,SsC3E;{X'a0ORA-01439:要更改数据类型,则要修改的列必须为空

对于这种情况，Oracle要求表的字段为空，才能进行修改。因此对应的方法一般有两类，一类是在表上添加一列，在表内根据原列的值更新目标列。另一类方法是建立一张新表，根据源表的值更新目标表的值。

其中第一类方法最省事，由于只是表的列发生变化，因此对数据库的对象的影响相对比较小，但是这种方法锁表时间可能会比较长，需要尽可能在比较空闲的时间内进行，对操作的运行时间有一定的要求。最后这种方式相对容易产生行迁移，影响后续表访问的性能。

而第二类方法中对系统影响最小的就是在线重定义方式。这种方式锁表时间最短，基本不影响业务的在线访问。可是这种方法也并不是没有缺点，首先这种方法相对比较复杂，第一类方法可能仅仅两、三个SQL就搞定了，而这种方法就需要很多的SQL语句，还要调用很多Oracle的包，复杂度比第一种情况高很多；第二类方法还需要考虑很多的东西，由于表被替换掉了，索引、约束、触发器、过程、权限、统计信息等等这些都是需要考虑和处理的，否则就很容易造成问题；还有就是这类操作和第一种操作相比，会产生更多的REDO和UNDO信息；更重要的一点是，这种方式有一定的限制条件，在11g以前，物化视图的基表是无法进行在线重定义的。而如果采用这类方法的其他方式，则前面提到的那些优点就不存在了，而缺点确仍然无法避免。

因此，对于比较繁忙、数据库可用性要求比较高的系统，对于数据量很大，直接更新要花费大量时间的表推荐采用在线重定义方式，而对于数据量不大的表，对于系统有充足维护时间的系统，可以考虑上面的第一类方式。

上面已经提到了，由于只是列的修改，而没有对删除原有的表，因此对系统的其他对象影响比较小。除非采用增加新列删除旧列的方式，否则不会影响系统中索引、约束、触发器、过程、权限和统计信息等对象，如果采用删除旧列，那么也只需要关注这个列相关的约束、索引和统计信息。

如果可以确保维护时间，那么第一类方法最大的问题就是行迁移，由于增加了新列，且给新列赋值，记录的长度增加，会造成行迁移的产生，从而影响表的访问性能。

SQL> ALTER TABLE T ADD NEW_OBJECT_ID VARCHAR2(10);

表已更改。

SQL> UPDATE T SET NEW_OBJECT_ID = ID;ITPUB个人空间|:IY(HfO7Q]l
UPDATE T SET NEW_OBJECT_ID = ID
c5`u8N\"N)sd0                             *ITPUB个人空间[b!o0A*D&cP*z
第1行出现错误:ITPUB个人空间%Z*OO#p
}*^f(O
ORA-00904: "ID":标识符无效

ITPUB个人空间:^r1r q"p2u'[4s jSR
SQL> UPDATE T SET NEW_OBJECT_ID = OBJECT_ID;

已更新50599行。

SQL> ALTER TABLE T DROP COLUMN OBJECT_ID;

表已更改。

SQL> ALTER TABLE T RENAME COLUMN NEW_OBJECT_ID TO OBJECT_ID;

表已更改。

虽然这种方式完成了操作，但是留下两个问题，一个是列的位置发生了变化，这样可能会对影响一些编码风格不好的程序：

SQL> DESC TITPUB个人空间;xbE#Hr_1u
 名称                                     是否为空?类型ITPUB个人空间AZj]:]
 ----------------------------------------- -------- ----------------------------ITPUB个人空间'V
~xv&a3d
 OWNER                                              VARCHAR2(30)ITPUB个人空间sUH|o)GZg
 OBJECT_NAME                                        VARCHAR2(128)
4z_%KO)gSn6x
y4dW0 OBJECT_ID                                          VARCHAR2(10)

SQL> INSERT INTO T (OBJECT_ID, OWNER, OBJECT_NAME) VALUES ('60000', 'YANGTK', 'T');

已创建1行。

SQL> INSERT INTO T VALUES ('60001', 'YANGTK', 'T');

已创建1行。

对于上面的标准写法，列的顺序没有关系，但是如果采用类似下面的写法，就会导致错误的出现。

另外一个问题，就是前面提到多次的行迁移：

SQL> @?/RDBMS/ADMIN/UTLCHAIN.SQL

表已创建。

SQL> ANALYZE TABLE T LIST CHAINED ROWS;

表已分析。

SQL> SELECT COUNT(*) FROM CHAINED_ROWS;

  COUNT(*)
4o
ia6a E#e0----------ITPUB个人空间9H5A!}X/IC*\D$a6mx
      2107

其实如果采用下面的方法就可以基本上避免上面的这两个问题：

SQL> CREATE TABLE T ASITPUB个人空间e\8}h&S3` |'e
  2  SELECT OBJECT_ID, OWNER, OBJECT_NAME
eT3h,|*O*oT:c9F,G$y0  3  FROM DBA_OBJECTS;

表已创建。

SQL> ALTER TABLE T ADD COL_TEMP NUMBER;

表已更改。

SQL> UPDATE T SET COL_TEMP = OBJECT_ID, OBJECT_ID = NULL;

已更新50600行。

SQL> ALTER TABLE T MODIFY OBJECT_ID VARCHAR2(10);

表已更改。

SQL> UPDATE T SET OBJECT_ID = COL_TEMP, COL_TEMP = NULL;

已更新50600行。

SQL> ALTER TABLE T DROP COLUMN COL_TEMP;

表已更改。

SQL> DESC TITPUB个人空间fo p7Cy'Wx
 名称                                     是否为空?类型ITPUB个人空间5px`0g'Nax0A'b!`
 ----------------------------------------- -------- ---------------------------ITPUB个人空间w#y b3B.l,g
 OBJECT_ID                                          VARCHAR2(10)
@#A;_N"a6R0 OWNER                                              VARCHAR2(30)
cKvL[$T1I0 OBJECT_NAME                                        VARCHAR2(128)

SQL> ANALYZE TABLE T LIST CHAINED ROWS;

表已分析。

SQL> SELECT COUNT(*) FROM CHAINED_ROWS;

  COUNT(*)
R%{9yW4u/])F0----------
2PI"N3ffD9T ^0         0

采用同时更新表中两个列的方式，可以有效的避免行迁移的产生，因为在更新的完成后，表记录的长度增加十分有限，只是由于OBJECT_ID的NULL出现在中间的位置，而使得每条记录的长度增加了1，而再执行第二次操作的时候，这个长度1的代价又被消除掉了，因此这种方式更新基本上不会产生行迁移。由于列的顺序没有发生变化，也不会对应用构成很大的影响。而且原始列没有被删除，索引、约束等都不需要改变。

这种方法的缺点在于需要更新两次，更新数据量比较大，而且每次更新产生的REDO和UNDO都比直接更新一个字段要多。