ALTER TABLE ADD column with DEFAULT value and Virtual Private Database

I recently came over a real strange behaviour with ALTER TABLE ADD column with DEFAULT value on one of my customers.

They are using a Software Product based on WebLogic and Oracle Database, from time to time we receive some new version of this Software Product which includes Database related changes, e.g. add new tables, add columns to an existing table and so on.

Within the latest Software Update, we could observe that the overall update process for the Database related part was increasing to around 12 hours !!!! Before it was quite shorter.

Mainly the time was taken for ALTER TABLE ADD column with DEFAULT values. After some investigations, I could see, that a simple ALTER TABLE ADD column with DEFAULT values was resulting into single updates with the provided DEFAULT value instead of updating the metadata for the given DEFAULT value. According to the Oracle Documentation in such a case the provided DEFAULT value should only be metadata and not updated for each row.

It took me some while to identify the reason (thanks to SQLHC, see My Oracle Support Note 1366133.1 https://support.oracle.com/epmos/faces/DocContentDisplay?id=1366133.1 ), I could see that on my target table for the ALTER TABLE ADD column with DEFAULT value was a Virtual Private Database policy attached with statement_types = insert, update, delete. But this policy was not enabled!
So in my opinion when I got a policy on a table which is not enabled, it should affect my ALTER TABLE ADD column command?

So, I simple dropped the complete policy and rerun my ALTER TABLE ADD column with DEFAULT value, and è voila my ALTER TABLE ADD column runs in milliseconds instead of serveral minutes.

Here a simple testcase to reproduce the problem:

sqlplus / as sysdba
# Create a Testuser
SQl> create user hr identified by "Oracle12c"
        default tablespace users quota unlimited on users;
SQL> grant connect, resource, create table, 
        create view, create procedure, alter session to hr;

# Connect with the above create testuser
# and create a test table with 10000000 rows
sqlplus hr/Oracle12c

# Set workarea_size_policy temporary to manual
# to avoid memory problems while creating huge test table
SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=1000000000;

Session altered.

SQL> create table t1 as
     select rownum as id,
     'Just some text' as textcol,
     mod(rownum,5) as numcol1,
     mod(rownum,1000) as numcol2,
     5000 as numcol3,
     to_date ('01.'|| lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018', 'dd.mm.yyyy') as time_id
     from dual connect by level<=1e7;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
  10000000

# Connect as sysdba
# create a demo function for VPD policy use
# and create the VPD policy including the statement_type update
# and set enable=FALSE for the policy
sqlplus / as sysdba

SQL> create or replace function no_toad_access
       (schema in varchar2, object in varchar2)
  return varchar2
  as
begin
  return 'upper(substr(sys_context(''userenv'',''module''),1,4))<>''TOAD''';
end;
/

# Create VPD policy including statement_type update
SQL> begin
  dbms_rls.add_policy (object_schema => 'HR',
  object_name => 'T1',
  policy_name => 'BAN_TOAD',
  function_schema => 'SYS',
  policy_function => 'NO_TOAD_ACCESS',
  statement_types => 'select,delete,update',
  enable => TRUE);
end;
/

# Set the VPD policy to false
SQL> begin 
   dbms_rls.enable_policy(object_schema => 'HR',
                                 object_name => 'T1',
                                 policy_name => 'BAN_TOAD',
                                 enable => FALSE);
end;
/

# Connect as testuser
# enable 10046 trace event to see that
# the ALTER TABLE ADD column with DEFAULT value
# turns to row updates
sqlplus hr/Oracle12
SQL> set timing on
SQL> alter session set tracefile_identifier='VPD_Problem_1';
SQL> alter session set events '10046 trace name context forever, level 16';
SQL> alter table t1 add (col_with_disabled_fgac varchar2(50) default 'test_1' not null);
Table altered.

Elapsed: 00:06:15.78

# Connect as sysdba
# drop the VPD policy
sqlplus / as sysdba
SQL> begin
  dbms_rls.drop_policy(object_schema => 'HR',
                       object_name => 'T1',
                       policy_name => 'BAN_TOAD');
end;
/

# Connect as testuser
# execute an ALTER TABLE ADD column with DEFAULT values again
sqlplus hr/Oracle12c
SQL> set timing on
SQL> alter session set tracefile_identifier='VPD_Problem_2';
SQL> alter session set events '10046 trace name context forever, level 16';
SQL> alter table t1 add (col_with_dropped_fgac varchar2(50) default 'test_1' not null);
Table altered.

Elapsed: 00:00:00.04

As you can see, even a disable VPD policy with statement type = update can affect your ALTER TABLE ADD column statement.

I have currently a Support Request with My Oracle Support open, to clarify if this is an expected behaviour or not, as nowhere in the Documentation I could find anything for this behaviour.