1 Reply Latest reply: Nov 29, 2012 3:38 AM by LiuMaclean(刘相兵) RSS

    如何删除列的default 属性

    967004
      请问在ORACLE中如何剔除列的default 属性?
      如:create table aa(c1 varchar2(10) default null)
      于是,在user_tab_cols中的data_default列中就有了个'null'值,我现在去掉这个null值,让它变成空白,相当于没有定义这个列的default属性,应该怎么弄?
        • 1. Re: 如何删除列的default 属性
          LiuMaclean(刘相兵)
          as test:

          SQL> drop table mac_tab;
          
          Table dropped.
          
          SQL> create table mac_tab (t1 int default null, t2 int);
          
          Table created.
          
          SQL> select COLUMN_NAME,data_default from dba_tab_cols where table_name='MAC_TAB';
          
          COLUMN_NAME          DATA_DEFAULT
          -------------------- ----------------------------------------
          T2
          T1                   null
          
          
          
          
          SQL> alter table mac_tab modify t1 default 100;
          
          Table altered.
          
          SQL> alter table mac_tab modify t1 ;
          
          Table altered.
          
          SQL> select COLUMN_NAME,data_default from dba_tab_cols where table_name='MAC_TAB';
          
          COLUMN_NAME          DATA_DEFAULT
          -------------------- ----------------------------------------
          T2
          T1                   100
          
          SQL> alter table mac_tab modify t1 default (NULL);
          
          Table altered.
          
          SQL> select COLUMN_NAME,data_default from dba_tab_cols where table_name='MAC_TAB';
          
          COLUMN_NAME          DATA_DEFAULT
          -------------------- ----------------------------------------
          T2
          T1                   (NULL)
          
          SQL> alter table mac_tab modify t1 default '';
          
          Table altered.
          
          SQL>  select COLUMN_NAME,data_default from dba_tab_cols where table_name='MAC_TAB';
          
          COLUMN_NAME          DATA_DEFAULT
          -------------------- ----------------------------------------
          T2
          T1                   ''
          
          SQL>  alter table mac_tab modify t1 default null;
          
          Table altered.
          
          SQL>  select COLUMN_NAME,data_default from dba_tab_cols where table_name='MAC_TAB';
          
          COLUMN_NAME          DATA_DEFAULT
          -------------------- ----------------------------------------
          T2
          T1                   null
          
          
          SQL> select object_id,data_object_Id from dba_objects where object_name='MAC_TAB';
          
           OBJECT_ID DATA_OBJECT_ID
          ---------- --------------
               64266          64266
          
                
          
          
          SQL> select COL#     , default$ from col$ where obj#=64266;
          
                COL# DEFAULT$
          ---------- --------------------
                   1 null
                   2
          
          方案1 重建该列 ,先add column 然后复制列数据 最后drop column 并rename 
          
          
          方案2 篡改数据字典,但是这有一点的危险性,只能在限制模式下实施:
          
                     
          
          SQL> update col$ set DEFAULT$=NULL where obj#=64266 and col#=1;
          
          1 row updated.
          
          SQL> select COL#     , default$ from col$ where obj#=64266;
          
                COL# DEFAULT$
          ---------- --------------------
                   1
                   2
          
          SQL> commit;
          
          Commit complete.
          
          SQL> select COL#     , default$ from col$ where obj#=64266;
          
                COL# DEFAULT$
          ---------- --------------------
                   1
                   2
          
          
          
                     
                     
                     
          实际根本没有必要修改该DEFAULT NULL,因为默认 也就是NULL , 去修改 有点画蛇添足