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

When moving to the Oracle 12c multitenant architecture one implication is that you have to switch to using service names in your connection strings for PDBs. Even SYSDBA connects to PDBs must use SERVICE_NAME.
Well, not quite necessarily. I guess customers adopting this new technology have not all managed to update their scripts. Or, software vendors and in-house development have hard-coded connection strings that cannot be changed quickly.
So, Oracle hastily added a new listener parameter USE_SID_AS_SERVICE_listener. This will tell the listener to treat the SID in the connect descriptor as SERVICE_NAME.
As long as the SID in the client connect descriptor matches the service name registered on the listener the connection is established.

$ cat /u01/app/oracle/network/admin/listener.ora 
LISTENER =
	(ADDRESS_LIST =
		(ADDRESS = 
			(PROTOCOL = TCP)
			(Host = oel6ora12cdb1.localdomain)
			(Port = 1521)
		)
	)

USE_SID_AS_SERVICE_LISTENER = ON

I have two PDBs which are registered on the listener as follows.

$ lsnrctl status
...
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb2" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...

My client tnsnames.ora holds entries for both PDBs, one with SERVICE_NAME (TESTDB1) and one with SID (TESTDB2).

$ cat /u01/app/oracle/network/admin/tnsnames.ora 
TESTDB1 =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SERVICE_NAME = TESTDB1)
	)
  )

TESTDB2 =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SID = TESTDB2)
	)
  )

Next, I connect to both PDBs using above TNS entries.

$ sqlplus /nolog

SQL> connect system/manager@PDB1
Connected.

SQL> connect system/manager@PDB2
Connected.

Well, that’s all fine.
But, what’s going to happen if we set the DB_DOMAIN, then the db domain is appended to the service name. Ultimately, the SID parameter you have in the client connect descriptor does not match the service name anymore.

Since you most likely use above described feature because you cannot change the client connect descriptor this solution does not work anymore.
Back to square one.

Just for fun let’s try it anyway…but of course, for it to work I have to adjust the SID in the TNS entry for TESTDB2.

SQL> show parameter db_domain

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_domain			     string	 localdomain

On the listener there are the two services from my pluggable databases, TESTDB1 and TESTDB2.

$ lsnrctl status
...
Services Summary...
Service "CDB1.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb1.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "testdb2.localdomain" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

Now, to match the service name I change the TNS entry for TESTDB2 to following:

TESTDB2.localdomain =
  (DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12cdb1.localdomain)(PORT = 1521))
	(CONNECT_DATA =
	  (SERVER = DEDICATED)
	  (SID = TESTDB2.localdomain)
	)
  )

At first sight it’s a bit strange to see the db domain in the SID parameter as this was never supposed to work this way. But at least we can connect again.

$ sqlplus system/manager@TESTDB2.localdomain
Connected.

I thought about creating a service using DBMS_SERVICE.CREATE_SERVICE but Oracle will always append the db domain to the service on the listener. And since a service in the format “PDB_NAME.DB_DOMAIN” already exists you will not be able to create it (ORA-44303: service name exists).

I file this under half-baked solution…(Not that I think this feature should by used anyway. Go get your scripts and applications fixed!)

Following up on Chris Antognini’s findings regarding deferred segment creation (here and here) I noticed another restriction that still finds no mention even in the latest Oracle documentation. I vaguely remember to have come across some issues in lower 11gR2 releases and some of them have been fixed in later patch sets. The problem went along the line of:

alter session set deferred_segment_creation = true;

create table t2 parallel 4
as 
  select /*+ parallel (t1, 4) */ *
  from t1 where 1 = 2
;

select segment_name, segment_type from user_segments where segment_name = 'T1';
SEGMENT_NAME SEGMENT_TYPE
------------ ------------
T1           TABLE

Note: This test war run against Oracle 11.2.0.1 as this particular issue is fixed in at least 11.2.0.4 and later

Now, why in gods name would you run CTAS in parallel when you know the query does not return any rows you might think.
Well, the problem becomes more subtle when we start combining features, namely partitioning and parallel DDL. Following I have prepared a few test cases to demonstrate the issue.

First let’s create the source table to select from:

create table t1
as
  select
      rownum id
    , round(dbms_random.value(1,4), 0) code
  from dual connect by level <= 100
;

The CODE column contains four distinct number values ranging from 1 to 4.

Next, we create a list partitioned table by using CTAS (create table as select) from our source table. All parts of this SQL run serially:

create table t3
segment creation deferred
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T3' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T3           TABLE PARTITION   CODE_1

This works as expected. Only the relevant partition was materialized as a segment to hold the data for CODE value 1.

This time, we create the same table structure using parallel DDL:

create table t4
segment creation deferred
parallel 4
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T4' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T4           TABLE PARTITION   CODE_1
T4           TABLE PARTITION   CODE_2
T4           TABLE PARTITION   CODE_3
T4           TABLE PARTITION   CODE_4

Oracle has materialized all four partitions even though only one of them contains data. The same also happens when the source query doesn’t return any rows, e.g. when CODE = 5.

Usually, there’s not much sense in running DDL in parallel while the SELECT providing the data runs serially. I have left out parallel processing of the SELECT part for simplicity as my tests have shown that it doesn’t affect the segment creation in any way.

Foot note: All test have been run on Oracle 12.1.0.2 and 11.2.0.4 except when where otherwise