首先查找Oracle的错误代码文档，居然没有查找到，于是在网上找到原因，是由于表设置有审计策略的原因。
测试过程如下：
1、创建测试表test
点击(此处)折叠或打开
-
-
-
SQL> create table test (id int);
-
-
Table created.
-
-
SQL> insert into test values (1);
-
-
1 row created.
-
-
SQL> commit;
-
-
Commit complete.
-
-
SQL> select * from test;
-
-
ID
-
----------
- 1
2、对TEST表增加字段P_NAME
点击(此处)折叠或打开
-
SQL> alter table test add p_name varchar2(20) default 'aaa';
-
-
Table altered.
-
-
SQL> select * from test;
-
-
ID P_NAME
-
---------- --------------------
-
1 aaa
-
-
-
SQL> insert into test (id) values (2);
-
-
1 row created.
-
-
SQL> commit;
-
-
Commit complete.
-
-
SQL> select * from test;
-
-
ID P_NAME
-
---------- --------------------
-
1 aaa
- 2 aaa
3、对TEST表增加审计策略，再测试增加字段
点击(此处)折叠或打开
-
SQL> begin
-
2 Dbms_fga.add_policy(
-
3 object_schema=>'TEST',
-
4 object_name=>'TEST',
-
5 policy_name=>'TEST_AUDIT',
-
6 statement_types=>'update,delete'
-
7 );
-
8 end;
-
9 /
-
-
PL/SQL procedure successfully completed.
-
-
SQL> alter table test add address varchar2(200) default 'beijing';
-
alter table test add address varchar2(200) default 'beijing'
-
*
-
ERROR at line 1:
-
ORA-00604: error occurred at recursive SQL level 1
- ORA-28133: full table access is restricted by fine-grained security
4、禁用审计策略后，对表增加字段同时设置默认值
点击(此处)折叠或打开
-
SQL> begin
-
2 Dbms_fga.disable_policy(
-
3 object_schema=>'TEST',
-
4 object_name=>'TEST',
-
5 policy_name=>'TEST_AUDIT'
-
6 );
-
7 end;
-
8 /
-
-
PL/SQL procedure successfully completed.
-
-
SQL> alter table test add address varchar2(200) default 'beijing';
-
-
Table altered.
-
-
SQL> select * from test;
-
-
ID P_NAME ADDRESS
-
---------- -------------------- --------------------
-
1 aaa beijing
- 2 aaa beijing
结论，当表设置有审计策略时对于增加字段同时设置默认值时，操作会失败。如果只是增加字段并不设置默认值时，不受审计策略的影响。
上面无法执行的SQL语句，除了禁用审计策略外，还可以分成2步执行：
点击(此处)折叠或打开
- alter table test add address varchar2(200)；
-
alter table test modify address varchar2(200) default 'beijing';