Oracle: Select the state of table from a specific moment on time

select_state_table_in_time.sql

-- To know what time limit in time to get the state of table on Oracle
-- In this case, 60000 seconds, ~ 18 hrs
show parameter undo;

-- NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
-- undo_management                      string      AUTO
-- undo_retention                       integer     60000

SELECT * FROM OWNER.TABLE_NAME
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)

-- This way show the deletions too, bether than make a full join on tables
SELECT versions_xid XID,
       versions_startscn START_SCN,
       versions_endscn END_SCN,
       VERSIONS_STARTTIME STARTTIME,
       VERSIONS_ENDTIME ENDTIME,
       versions_operation OPERATION,
       SYS.SCN_TO_TIMESTAMP(versions_startscn) MODIFIED_DATE,
       OWNER.TABLE_NAME.*
       FROM OWNER.TABLE_NAME
       VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
       where versions_operation in ('U','I','D')
       AND SYS.SCN_TO_TIMESTAMP(versions_startscn) > (SYSTIMESTAMP - INTERVAL '30' MINUTE)
       ORDER BY SYS.SCN_TO_TIMESTAMP(versions_startscn) DESC;

-- Another way, but it will list all records in the same block of real modified record.
select FIELD_NAME,ora_rowscn
from OWNER.TABLE_NAME 
WHERE ora_rowscn >= (select min(SCN) min_scn from sys.smon_scn_time)