Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.

The question was: "Is it possible to have multiple temporal valid periods for a table?"
According to the documentation definition above it should be possible to create multiple temporal valid periods. Let's try this.

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  ,period for b_valid (b_start, b_end)
  9 );
,period for b_valid (b_start, b_end)
            *
ERROR at line 8:
ORA-55603: invalid flashback archive or valid time period command

That didn't work.
But we're not done yet. Let's try something a little different:

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  );

Table created.

SQL> alter table t
  2 add period for b_valid (b_start, b_end);

Table altered.

That worked. There are now two valid period defined on table T. Maybe I got the syntax wrong, let's take a look at the Metadata and find out where I went wrong.

SQL> select dbms_metadata.get_ddl
  2     ('TABLE'
  3     ,'T')
  4    from dual;
ERROR:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1



no rows selected

Oh oh.. I think I found a little bug here.
I wonder what will happen if we add data and run some queries.

SQL> insert into t values
  2  (1, trunc (sysdate) -10, trunc (sysdate), trunc (sysdate), trunc (sysdate) +10) ;

1 row created.

SQL> insert into t values
  2  (2, trunc (sysdate), trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate))
  3  ;

1 row created.

SQL> insert into t values
  2  (3, trunc (sysdate) -10, trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate) +10)
  3  ;

1 row created.
 
SQL> commit;

Commit complete.

SQL> select *
  2    from t;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

3 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

It all seems to work fine.
So yes, you can add multiple temporal validity periods to a table but only with an alter table statement.

Links

Definition Temporal Validity