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

What’s obvious from reading the Oracle Concepts and PL/SQL Developers Guide is that Oracle sets an implicit savepoint before each single SQL statement and in case the statement fails (for whatever reason) Oracle rolls back to that savepoint. That’s what’s called “statement level rollback”. Apparently, for stored procedures that are called from a client the same rules apply. Before the procedure call a savepoint is set and if that procedure raises/propagates an exception Oracle rolls back to the implicit savepoint set just before the procedure call. It is fundamental to understand this behaviour as a Oracle database developer, especially when it comes to exception handling. Let’s examine the implications. First we create the table with index and constraint:

SQL> create table t1 (id number, text varchar2(50));
Table created.

SQL> create unique index t1_idx_01 on t1 (text);
Index created.

SQL> alter table t1 add constraint t1_uk_01 unique (text) using index t1_idx_01;
Table altered.

Then a stored procedure to insert some data into table T1:

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
end insert_t1;
/
Procedure created.

This procedure tries to insert three records into table T1 but due to the unique constraint on TEXT the second insert statement will fail and the procedure returns with an exception propagated to the caller and subsequently everything done within the procedure will be rolled back.
Let’s put that to a test:

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
BEGIN insert_t1; END;
*
ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.T1_UK_01) violated
ORA-06512: at "TESTUSER.INSERT_T1", line 8
ORA-06512: at line 1

SQL> select * from t1;
no rows selected

So far so good, now let’s see what happens when we trap ORA-00001 (PL/SQL named exception DUP_VAL_ON_INDEX) and swallow it, not propagating the exception to the top-level call.

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
exception
when dup_val_on_index then
  null;
end insert_t1;
/
Procedure created.

This time the procedures again tries to insert three records into table T1 but fails badly with the second record. The exception is trapped and silently swallowed so the procedure returns without an error, no exception propagation and therefore leaving one record in the table.

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
PL/SQL procedure successfully completed.

SQL> select * from t1;
ID                  TEXT
---------- --------------------------------------------------
1                   first

1 row selected.

Ooops, something went wrong and we didn’t notice, if we proceed with the program flow in the application and eventually commit the transaction we leave the database in a inconsistent state as we expect three records to be present and not only one.

Alright, next time just let’s log the error and propagate it:

SQL> create or replace procedure insert_t1
is
begin
  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (1, 'first');

  dbms_output.put_line('inserting first row');
  insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001

  dbms_output.put_line('inserting third row');
  insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception
exception
when dup_val_on_index then
  dbms_output.put_line('Error: duplicate records');
  raise_application_error(-20001, 'There can only be one "first"!');
end insert_t1;
/
Procedure created.

There we go, again whatever the application does we ensure the consistency of the data within the database.

SQL> truncate table t1;
Table truncated.

SQL> exec insert_t1
BEGIN insert_t1; END;
*
ERROR at line 1:
ORA-20001: There can only be one "first"!
ORA-06512: at "TESTUSER.INSERT_T1", line 15
ORA-06512: at line 1

SQL> select * from t1;
no rows selected

The propagation of exceptions can also be observed in a SQL trace where for a cursor “begin insert_t1; end;” with depth 0 a ERROR is following the EXEC call. If the error is trapped and ignored there is only a ERROR following the EXEC call of the the cursor “INSERT INTO T1(ID, TEXT) VALUES (2, ‘first’)” with depth 1 and no ERROR for the parent cursor.

Conclusion:
Be careful with stored procedures that trap exceptions and do not propagate them as this kind of breaks the statement level rollback that Oracle would perform when the exceptions are not caught at all or are being re-raised.

Foot note 1: in this text “stored procedure” or “procedure” represents any named PL/SQL construct (except triggers)