Never hurry up for telling "It is a BUG in Oracle"!

Today I was upgrading(software of all of components, except DB and GI) Exadata X2 to new version. First I did standby site, so we had to simulate some tests to be sure everything is OK as expected. One of them was to create a new tablespace at primary site (which is in older version) and check if media recovery adds a new datafile at standby site. We are using Oracle Managed Files. Statement was as below:

SQL> CREATE TABLESPACE TEST_DG_1 DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
  2  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  3  EXTENT MANAGEMENT LOCAL DEFAULT  
  4  NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL
  5  /

Tablespace created.

In alert log of standby, we see:

Successfully added datafile 27 to media recovery
Datafile #27: '+DATA_EXA2/xlst/datafile/test_dg_1.624.845671089'
Successfully added datafile 28 to media recovery
Datafile #28: '+DATA_EXA2/xlst/datafile/test_dg_1.633.845671089'

Oops? Why 2 datafiles instead of 1? Customer's 1st opinion: We hit a BUG! :) OK, it is expected reaction :) Lets test: On primary:

SQL> select file_name from dba_data_files where tablespace_name = 'TEST_DG_1';

FILE_NAME
--------------------------------------------------------------------------------
+DATA_EXA1/xldr/datafile/test_dg_1.596.845671087
+DATA_EXA1/xldr/datafile/test_dg_1.602.845671087

Interesting... Lets get Metadata of newly created tablespace:

SQL> set long 1000
SQL> /

DBMS_METADATA.GET_DDL('TABLESPACE','TEST_DG_1')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "TEST_DG_1" DATAFILE
  SIZE 10485760
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,
  SIZE 104857600
  AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL

2 AUTOEXTEND options... As we know, AUTOEXTEND is for datafile not for tablespace. Suddenly, I realized ',' symbol after 'UNLIMITED' keyword in statement. Removing it and creating again:

SQL> CREATE TABLESPACE TEST_DG_1 DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  2  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  3  EXTENT MANAGEMENT LOCAL DEFAULT  
  4  NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = 'TEST_DG_1';

FILE_NAME
--------------------------------------------------------------------------------
+DATA_EXA1/xldr/datafile/test_dg_1.602.845671459

Yes, now only 1 datafile is created as expected. For testing, adding many ',' symbols as below:

SQL> CREATE TABLESPACE TEST_DG_2 DATAFILE SIZE 10M , ,;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = 'TEST_DG_2';

FILE_NAME
--------------------------------------------------------------------------------
+DATA_EXA1/xldr/datafile/test_dg_2.596.845671511
+DATA_EXA1/xldr/datafile/test_dg_2.281.845671511
+DATA_EXA1/xldr/datafile/test_dg_2.465.845671511

As you see, What parser understands here: 'DATAFILE' clause is for the 1st datafile, if it is followed by ',' then it means another DATAFILE and so on. Checked Support and met with: Bug 11822439 - DBMS_METADATA.GET_DDL for tablespace shows multiple autoextend clauses (Doc ID 11822439.8) It is very similiar to our case, but in our case it is not BUG of DBMS_METADATA, because DBMS_METADATA told us what really we have (how many datafiles, options etc). Bug of statement parser? Lets execute the statement which is generated by DBMS_METADA:

SQL> CREATE TABLESPACE "TEST_DG_1" DATAFILE
  2  SIZE 10485760
  3  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,
  4  SIZE 104857600
  5  AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
  6  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  7  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
  8  NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL
  9  /

Tablespace created.

So, logically it means it is designed and working as expected :) But for to be 100% sure, I will raise a BUG! Good luck!