Monthly Archives: February 2015

Explicit Default – SecureFile parameter change

While introducing a new value for parameter DB_SECUREFILE in Oracle 12c the default value has changed as well (see doc).
With the new value “PREFERRED” all LOBs are created as SecureFile LOBs if not explicitly specified otherwise.

Just out of curiosity I created two databases, one with default “PREFERRED” the other with “PERMITTED” as it was the default in 11g.
Check out what happens to the Oracle data dictionary:

DB_SECUREFILE = PREFERRED

SQL> select owner, securefile, count(*) from dba_lobs group by owner, securefile order by owner;
OWNER                 SECUREFILE CNT
--------------------- ---------- ----------
CTXSYS                YES        4
GSMADMIN_INTERNAL     YES        1
OJVMSYS               NO         1
OUTLN                 NO         1
SYS                   NO         120
SYS                   YES        75
SYSTEM                NO         21
SYSTEM                YES        2
WMSYS                 YES        8
XDB                   NO         2
XDB                   YES        291

DB_SECUREFILE = PERMITTED

SQL> select owner, securefile, count(*) from dba_lobs group by owner, securefile order by owner;
OWNER                 SECUREFILE CNT
--------------------- ---------- ----------
CTXSYS                NO         4
GSMADMIN_INTERNAL     NO         1
OJVMSYS               NO         1
OUTLN                 NO         1
SYS                   NO         194
SYS                   YES        1
SYSTEM                NO         23
WMSYS                 NO         8
XDB                   NO         286
XDB                   YES        7

Quick thoughts:

  1. With every release study the New Features Guide carefully. Oracle’s not always waving a big red flag when “minor” things change.
  2. Do I like varying data dictionary definitions? No. I’m going to take great care when creating or migrating databases that they all look alike.
  3. What funny side effects we will encounter?
  4. I can see where Oracle is coming from, but if it is me I always try to be as explicit as possible and not relying on instance settings. When developing software and my customer base is, let’s say bigger than five I want to make sure all they’re schema deployments look the same.

Foot note: All test have been run on Oracle 12.1.0.2