Just a quick post in response to a question that came from Oracle Ace Alex Nuijten recently. He asked if you define a table using the temporal validity feature in Oracle, for example,

 SQL> create table t (   2    id         number(10) not null,   3    blah       number(10) not null,   4    start_date date,   5    end_date   date,   6    period for per (start_date, end_date)   7  );  Table created.

then where in the dictionary can you observe this fact? There is no "IS_TEMPORAL" column on USER_TABLES, and besides resorting to generating the full DDL for the table...

 SQL> select dbms_metadata.get_ddl('TABLE','T') from dual  DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('T'),UPPER('')) --------------------------------------------------------------------------------------    CREATE TABLE "MCDONAC"."T"    (    "ID" NUMBER(10,0) NOT NULL ENABLE,         "BLAH" NUMBER(10,0) NOT NULL ENABLE,         "START_DATE" DATE,         "END_DATE" DATE    )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   TABLESPACE "USERS"   ALTER TABLE "MCDONAC"."T" ADD PERIOD FOR "PER"("START_DATE","END_DATE")

...there does not seem to be any mechanism to determine this attribute. You could possible make some educated guesses by looking at the dictionary view USER_TAB_COLS, which reveals hidden columns as well as visible ones.

 SQL> select column_name, hidden_column   2  from   user_tab_cols   3  where  table_name = 'T';  COLUMN_NAME                    HID ------------------------------ --- PER                            YES ID                             NO BLAH                           NO START_DATE                     NO END_DATE                       NO

But the issue there is that temporal validity can be defined using existing columns as per the example above, or defined simply with the period name and then the supporting columns will be automatically added to the table, but as hidden columns. Here's an example of this latter case where only the period name is provided, and notice that we now get three hidden columns instead of only 1.

 SQL> create table t (   2    id         number(10) not null,   3    blah       number(10) not null,   4    period for per   5  );  Table created.  SQL> select column_name, hidden_column   2  from   user_tab_cols   3  where  table_name = 'T';  COLUMN_NAME                    HID ------------------------------ --- PER_START                      YES PER_END                        YES PER                            YES ID                             NO BLAH                           NO

Making an assessment that this table is temporal just from the number of columns that are hidden and the datatypes is risky. Clearly the database must know somehow that a table has temporal periods defined, and with a little bit of digging, it appears that this is held in the internal view SYS_FBA_PERIOD which does not appear to have any DBA_, ALL_, or USER_ equivalents, thus making this knowledge inaccessible.

Querying this internal view with a join to USER_OBJECTS gives us enough information to determine the period information.

 SQL> select periodname, periodstart, periodend   2  from sys.SYS_FBA_PERIOD   3  where obj# =   4    ( select object_id   5      from user_objects   6      where object_name = 'T');  PERIODNAME      PERIODSTART     PERIODEND --------------- --------------- --------------- PER             START_DATE      END_DATE

Hence if you are actively using temporal tables, then you could create a dictionary view to assist with your database maintenance, and make this available to schema users.

 SQL> conn / as sysdba Connected. SQL> create or replace   2  view sys.user_fba_period as   3  select o.name table_name,   4         s.periodname period_name,   5         s.periodstart period_start,   6         s.periodend period_end   7  from sys.sys_fba_period s,   8       sys.obj$ o   9  where o.obj# = s.obj#  10  and   o.owner# = userenv('SCHEMAID');  View created.  SQL> grant select on sys.user_fba_period to public;  Grant succeeded.  SQL> create or replace public synonym user_fba_period for sys.user_fba_period;  Synonym created.

Note: This is an unsupported course of action, so this becomes your responsibility to manage this view between patches and releases.