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:
- With every release study the New Features Guide carefully. Oracle’s not always waving a big red flag when “minor” things change.
- 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.
- What funny side effects we will encounter?
- 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