Temporal tables in Oracle

Question

Tom Kyte describes temporal tables here:

temporal tables are tables that can return the answer that existed at a point in time – you can ask the table to return the answer that existed at midnight last night, instead of the answer that exists right now

Do such tables exist in Oracle? I can't find documentation of them. How can I create one and use it? He says they exist in databases, although he doesn't say that they do in Oracle nor any other product. Is this a conceptual thing not implemented?

Answer 1

Well, I believe you mean to get the data state as it was some time earlier than now. In this case Oracle suggests FLASHBACK QUERIES:

SQL> select * from t where x in (1,2,3);

         X          Y                                                           
---------- ----------                                                           
         1          1                                                           
         2          2                                                           
         3          3                                                           

SQL> delete from t where x in (1,2,3);

SQL> commit;

SQL> select * from t where x in (1,2,3);

No rows selected

SQL> select * from t as of timestamp(systimestamp - interval '2' minute) where x in (1,2,3)
  2  /

         X          Y                                                           
---------- ----------                                                           
         1          1                                                           
         2          2                                                           
         3          3                                                           

Oracle describes this option there

http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_fl.htm#1008580

As of temporary tables - plese see this reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN01503

Answer 2

One option for how you can mimic this behavior of Temporal Tables from other databases into Oracle is by using the 'Valid Time Support' feature of 'Oracle Workspace Manager'. More details are in the below documentation from Oracle.

https://docs.oracle.com/en/database/oracle/oracle-database/19/adwsm/valid-time-support.html#GUID-967E7CE5-A328-4BA7-AA7B-7899B6C99C01