Oracle里default什么意思,如何删除列的default 属性 — oracle-tech

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 ， 去修改 有点画蛇添足