Is my table temporal?

Posted by

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.

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.