lbx@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
lbx@ORCL> create table t4(id number(3));
Table created.
lbx@ORCL> insert into t4 select level from dual connect by level<=10;
lbx@ORCL> commit;
lbx@ORCL> select * from t4;
ID
----------
1
2
3
4
5
6
7
8
9
10
lbx@ORCL> alter table t4 add(flag varchar2(10) default 'Y');
Table altered.
lbx@ORCL> select * from t4;
ID FLAG
---------- ----------
1 Y
2 Y
3 Y
4 Y
5 Y
6 Y
7 Y
8 Y
9 Y
10 Y
10 rows selected.
11G会加默认值哦,小心操作。。。
如果你本来有那字段,修改为增加默认值,原先为空的仍为空,后续增加的数据才会有默认值
lbx@ORCL> alter table t4 add (flag2 varchar2(10));
Table altered.
lbx@ORCL> select * from t4;
ID FLAG FLAG2
---------- ---------- ----------
1 Y
2 Y
3 Y
4 Y
5 Y
lbx@ORCL> alter table t4 modify flag2 default 'N';
lbx@ORCL> insert into t4 (id) values(11);
1 row created.
lbx@ORCL> commit;
Commit complete.
lbx@ORCL> select * from t4;
ID FLAG FLAG2
---------- ---------- ----------
1 Y
2 Y
3 Y
4 Y
5 Y
6 Y
7 Y
8 Y
9 Y
10 Y
11 Y N