Oracle 如何修改列的数据类型

链接：http://www.cnblogs.com/david-zhang-index/archive/2012/04/10/2441015.html

对字段操作	操作方法
更新字段名	alter table TABLE_NAME rename column column_old to column_new;
添加字段	alter table TABLE_NAME add COLUMN_NAME varchar(10);
删除字段	alter table TABLE_NAME drop column COLUMN_NAME;
添加字段并附值	alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1;
修改字段值	update TABLE_NAME set filedname=value where filedname=value;
修改字段数据类型	alter table tablename modify filedname varchar2(20);

这个东西搞得我焦头烂额的！！！

最先的需求是修改表中主键的数据类型
一般的方式：
alter table students modify(sid number(10));
肯定是不行的，因为有主从关系嘛
另一种方式是创建一个临时的列(列类型为number(10))，将SID更名(更名我觉得没有实质的作用)，然后将SID的数据全部导入到SID_TEMP中去update zyt set ID_TEMP = cast(ID_BAK as varchar2(10)); ，然后删除SID(这里我是删除不了的，因为是主键)，将SID_TEMP更名为SID
还有一种方式是，创建临时的列(列类型为varchar2(10))，这里的列是临时存数据用的，与上面一致，当SID_TEMP中的数据与一致时候，用的语句不一样：update zyt set ID_TEMP=sid,sid=null;这里出错,sid不能置为空，然后alter table zyt modify id number(10);alter table zyt drop column ID_TEMP;就可以了。

*****************************************

 查看约束信息：user_constraints数据字典
 select constraint_name,constraint_type,table_name
  from user_constraints
  where table_name='students';

查看约束建立在那个表的哪个列上：user_cons_columns数据字典

select owner,constraint_name,table_name,column_name
from user_cons_columns
where table_name='students';

 上面的students应该写为STUDENTS，这是我犯的错误！！！！

******************

**********************************

时隔三天这个问题再回来的时候终于被解决了，谢谢csdn上的高手！

犯了上面的小写问题后，应该引以为戒！！！

一层一层的删除约束：

我表的结构式scores(sid r)-->enrolls(sid p)--->students(sid p)

用下面查出约束名（建立约束的时候自己美命名，这里系统的自动名也有规律啊）：

  select constraint_name,constraint_type,table_name
  from user_constraints
  where table_name='students'

删除约束，一层层的删除吧：

alter table enrolls drop constraint  SYS_C005446;

直到与sid的约束都被我删除了，然后进行

alter table students drop column sid ;时候还是提示不能删除主键，于是将主键约束也给删除了

再删除的时候就没问题了，然后再自己建一个主键慢慢加约束!!

SQL> alter table students
  2  add constraint pk
  3  primary key(sid);

表已更改。

SQL> desc students
  名称                                                                          是否为空? 类型
  ----------------------------------------- -------- ----------------------------
  FNAME                                                                                          VARCHAR2(20)
  LNAME                                                                        NOT NULL VARCHAR2(20)
  MINIT                                                                                          CHAR(1)
  PHONES                                                                                        CHAR(12)
  ADDRESS                                                                                      VARCHAR2(24)
  SID                                                                            NOT NULL NUMBER(10)

这个过程我觉得复杂了，不知道有否简单方式！！！

*************************************************************

重建表时,遇到ORA-02449: 表中的唯一/主键被外键引用的处理办法

1.检查哪些表的外键引用了要删除的表的唯一/主键.

      select A.*
       from user_constraints A, user_constraints B
       WHERE b.table_name = 'MYTEST'
        and a.constraint_type = 'R'
        and a.r_constraint_name = b.constraint_name;

2.生成所有引用要删除表的外键的创建语句.

      select 'select dbms_metadata.get_ddl(''REF_CONSTRAINT'',''' ||
          A.CONSTRAINT_NAME || ''') FROM DUAL;'
       from user_constraints A, user_constraints B
       WHERE b.table_name = 'MYTEST'
        and a.constraint_type = 'R'
        and a.r_constraint_name = b.constraint_name;
     
3.执行步骤2中产生的SQL语句,获取外键定义.

      set long 3000
      set linesize 3000
      set pagesize 300
      执行步骤2产生的语句.
     
4.删除表.

      drop table mytest cascade constraints;

5.删除并重建表.

6.建立因创建该表所删除的外键约束.

      执行步骤3中获取到的sql语句.

7.检查各外键是否得到恢复,系统是否正常.

*********************************************************

1 SQL> select * from v$version;
2  
3 BANNER
4 - -------------------------------------------------------------------------------
5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
6 PL/SQL Release 11.2.0.1.0 - Production
7 CORE    11.2.0.1.0      Production
8 TNS for Linux: Version 11.2.0.1.0 - Production
9 NLSRTL Version 11.2.0.1.0 - Production

1.修改字段数据类型时，如果该列有数据则报ORA-01439: column to be modified must be empty to change datatype，此时需要通过另外一种方法修改：

  1 SQL> alter table zyt add id_temp varchar2(10);
  2    --------------------------
  3 Table altered.
  4    --------------------------------
  5 SQL> commit;
  6  ---------------   
  7 Commit complete.
  8    -------------------------------                                 
  9 SQL> select * from zyt;
10 ---------------------
11 NAME                            ID ID_TEMP
12 ---------- ---------- ----------
13 zyt1                              1
14 david                            2
15 -----------------------------------------------
16 SQL> alter table zyt rename column id to id_bak;
17 -----------------------------------
18 Table altered.
19 -----------------------------
20 SQL> select * from zyt;
21
22 NAME                    ID_BAK ID_TEMP
23 ---------- ---------- ----------
24 zyt1                              1
25 david                            2
26 ------------------------------------
27 SQL> desc zyt;
28  Name                                                                          Null?      Type
29  ----------------------------------------- -------- ----------------------------
30  NAME                                                                                            VARCHAR2(10)
31  ID_BAK                                                                      NOT NULL NUMBER(2)
32  ID_TEMP                                                                                      VARCHAR2(10)
33 ----------------------------------------------------------------------------------
34 SQL> update zyt set ID_TEMP = cast(ID_BAK as varchar2(10));
35 -----------------
36 2 rows updated.
37 ----------------------------
38 SQL> commit;
39 ---------------
40 Commit complete.
41 -----------------
42 SQL> select * from zyt;
43 --------------------------
44 NAME                    ID_BAK ID_TEMP
45 ---------- ---------- ----------
46 zyt1                              1 1
47 david                            2 2
48 -----------------------------------
49 SQL>  alter table zyt drop column ID_BAK;
50 ---------------------------------
51 Table altered.
52 ----------------------------------------
53 SQL> commit;
54 --------------
55 Commit complete.
56 ------------------
57 SQL> select * from zyt;
58 ------------------------------------------------
59 NAME            ID_TEMP
60 ---------- ----------
61 zyt1            1
62 david          2
63 ----------------------------------
64 SQL> desc zyt;
65  Name                                                                          Null?      Type
66  ----------------------------------------- -------- ----------------------------
67  NAME                                                                                            VARCHAR2(10)
68  ID_TEMP                                                                                      VARCHAR2(10)

备注：这种方法能满足需求，因新增字段默认添加到表末尾，有可能发生行迁移,对应用程序会产生影响，同时也涉及复杂数据，不算最好的方法

2.建立一个中间跳板，临时存储数据

  1 SQL> desc zyt;
  2  Name                                                                          Null?      Type
  3  ----------------------------------------- -------- ----------------------------
  4  NAME                                                                                            VARCHAR2(10)
  5  ID                                                                                                VARCHAR2(10)
  6 --------------------------------------------------
  7 SQL> select * from zyt;
  8 ----------------------
  9 NAME            ID
10 ---------- ----------
11 zyt1            1
12 david          2
13 ------------------------------------------------------------------
14 SQL> alter table zyt add id_temp VARCHAR2(10)
15 -----------------------------------------
16 Table altered.
17 -------------------------------
18 SQL> select * from zyt;
19 ------------------------------
20 NAME            ID                      ID_TEMP
21 ---------- ---------- ----------
22 zyt1            1
23 david          2
24 -----------------------------------------------
25 SQL> update zyt set ID_TEMP=id,id=null;
26 -----------------------------------------------------------------
27 2 rows updated.
28 -------------------------------
29 SQL> select * from zyt;
30 ---------------------
31 NAME            ID                      ID_TEMP
32 ---------- ---------- ----------
33 zyt1                                                    1
34 david                                                  2
35 ------------------------------------
36 SQL> alter table zyt modify id number(10);
37 -----------------
38 Table altered.
39 ------------------------
40 SQL> desc zyt;
41  Name                                                                          Null?      Type
42  ----------------------------------------- -------- ----------------------------
43  NAME                                                                                            VARCHAR2(10)
44  ID                                                                                                NUMBER(10)
45  ID_TEMP                                                                                      VARCHAR2(10)
46 ---------------------------
47 SQL> update zyt set id=ID_TEMP,ID_TEMP=null;
48 ----------------------------
49 2 rows updated.
50 ---------------------------------------
51 SQL> select * from zyt;
52 -----------------------------
53 NAME                            ID      ID_TEMP
54 ---------- ---------- ----------
55 zyt1                              1
56 david                            2
57 ---------------------------
58 SQL> alter table zyt drop column ID_TEMP;
59 ----------------------------
60 Table altered.
61 --------------------
62 SQL> commit;
63 ----------------------------------------
64 Commit complete.
65 --------------------------------
66 SQL> select * from zyt;
67 -------------------------------------
68 NAME                            ID
69 ---------- ----------
70 zyt1                              1
71 david                            2
72 ------------------------------------------
73 SQL> desc zyt;
74  Name                                                                          Null?      Type
75  ----------------------------------------- -------- ----------------------------
76  NAME                                                                                            VARCHAR2(10)
77  ID                                                                                                NUMBER(10)

备注：第二种方法，是增加一个与被修改的列类型一样的列，之后将要修改列的数据复制到新增的列并置空要修改的列，之后修改数据类型，再从新增列将数据拷贝回来，该过程涉及两次数据复制，如果是数据量很多，会比较慢同时也会产生很多undo和redo；优点是数据不会发生行迁移。