{"id":545,"date":"2013-01-03T03:34:39","date_gmt":"2013-01-03T02:34:39","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=545"},"modified":"2023-11-08T20:05:23","modified_gmt":"2023-11-08T19:05:23","slug":"loading-historical-data-into-flashback-archive-enabled-tables","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/","title":{"rendered":"Loading Historical Data Into Flashback Archive Enabled Tables"},"content":{"rendered":"\n

Oracle provides via OTN<\/a> an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period\/precision or ORA-1466 when using the AS OF TIMESTAMP clause are not addressed. I show in this post how to load historical data into flashback archive-enabled tables using the standard API. Unfortunately, there are still some experimental actions necessary to get a fully functional result, at least with version 11.2.0.3.4.<\/p>\n\n\n\n

Test Scenario<\/h3>\n\n\n\n

There are various reasons why you may want to load historical data into a FBA enable table. E.g. for testing purposes, to move FBA-enabled tables from one database instance to another or to migrate conventionally historized tables. This example is based on a migration scenario. Table T1 has the following content and shall be migrated to FBA. You find the script to create and populate the table T1 here<\/a>.<\/p>\n\n\n\n

Table T1<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t1;<\/span><\/span>\n<\/span><\/span>\nVID OID CREATED_AT          OUTDATED_AT         C1 C2<\/span><\/span>\n--- --- ------------------- ------------------- -- --<\/span><\/span>\n  1   1 2012-12-19 13:00:57 2012-12-21 08:31:01 A  A1<\/span><\/span>\n  2   1 2012-12-21 08:31:01 2012-12-23 20:58:05 A  A2<\/span><\/span>\n  3   1 2012-12-23 20:58:05 2012-12-27 11:40:41 A  A3<\/span><\/span>\n  4   1 2012-12-27 11:40:41 9999-12-31 23:59:59 A  A4<\/span><\/span>\n  5   2 2012-12-20 13:51:55 9999-12-31 23:59:59 B  B1<\/span><\/span>\n  6   4 2012-12-22 11:03:22 2012-12-23 19:36:08 C  C1<\/span><\/span>\n  7   4 2012-12-28 14:25:50 2012-12-30 17:10:39 C  C1<\/span><\/span>\n  8   4 2012-12-30 17:10:39 2012-12-31 12:05:40 C  C2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The column VID is the version identifier and the primary key. OID is the object identifier, which is unique at every point in time. CREATED_AT and OUTDATED_AT define the interval boundaries. Column C1 and C2 are the payload columns, which may change over time.<\/p>\n\n\n\n
The following queries return data valid at 2012-12-23 19:36:08 and now.<\/p>\n\n\n\n
Point in Time Queries for T1<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t1<\/span><\/span>\n  2   WHERE created_at <= TIMESTAMP '2012-12-23 19:36:08'<\/span><\/span>\n  3     AND outdated_at > TIMESTAMP '2012-12-23 19:36:08';<\/span><\/span>\n<\/span><\/span>\nVID OID CREATED_AT          OUTDATED_AT         C1 C2<\/span><\/span>\n--- --- ------------------- ------------------- -- --<\/span><\/span>\n  2   1 2012-12-21 08:31:01 2012-12-23 20:58:05 A  A2<\/span><\/span>\n  5   2 2012-12-20 13:51:55 9999-12-31 23:59:59 B  B1<\/span><\/span>\n<\/span><\/span>\nSQL> SELECT * FROM t1 WHERE outdated_at > SYSDATE;<\/span><\/span>\n<\/span><\/span>\nVID OID CREATED_AT          OUTDATED_AT         C1 C2<\/span><\/span>\n--- --- ------------------- ------------------- -- --<\/span><\/span>\n  4   1 2012-12-27 11:40:41 9999-12-31 23:59:59 A  A4<\/span><\/span>\n  5   2 2012-12-20 13:51:55 9999-12-31 23:59:59 B  B1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
It’s important to notice that OID 4 (see highlighted line above) is not part of the first query result, since OUTDATED_AT has an excluding semantic. I mention this fact because the column ENDSCN in the table SYS_FBA_HIST_<object_id> uses also an excluding semantic, which simplifies the migration process, at least in this area.<\/p>\n\n\n\n
From UTC<\/a> to SCN<\/a><\/h3>\n\n\n\n
Oracle uses its own time standard SCN for FBA. The SCN is initialized during database creation and is valid for a single Oracle instance, even if synchronization mechanisms among database instances exist (see also MOS note 1376995.1<\/a>). An SCN may not represent a date-time value before 1988-01-01 00:00:00. The time spent between two SCNs is varying, it may be shorter when the database instance is executing a lot of transactions and it may be longer in more idle times or when the database instance is shut down. Oracle uses the table SYS.SMON_SCN_TIME to map SCN to TIMESTAMPs and vice versa and provides the functions SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN for that purpose.<\/p>\n\n\n\n
So what is the first date-time value which may be converted into an SCN?<\/p>\n\n\n\n
Min. Values in SMON_SCN_TIME<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT MIN(time_dp) AS min_time_dp,<\/span><\/span>\n  2         MIN(scn) AS min_scn,<\/span><\/span>\n  3         CAST(scn_to_timestamp(MIN(scn)) AS DATE) AS min_scn_to_ts,<\/span><\/span>\n  4         MAX(dbtimezone) AS dbtimezone,<\/span><\/span>\n  5         sessiontimezone<\/span><\/span>\n  6    FROM sys.smon_scn_time;<\/span><\/span>\n<\/span><\/span>\nMIN_TIME_DP            MIN_SCN MIN_SCN_TO_TS       DBTIMEZONE SESSIONTIMEZONE<\/span><\/span>\n------------------- ---------- ------------------- ---------- ---------------<\/span><\/span>\n2010-09-05 22:40:05      18669 2010-09-06 00:40:05 +00:00     +01:00<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The first value is 2010-09-06 00:40:05. You may notice the two-hour difference to MIN_TIME_DP. Oracle stores the date values in this table in UTC (DBTIMEZONE) and my database server’s time zone is CET (Central European Time) which is UTC+01:00 at the point of query (see SESSIONTIMEZONE). However, in September daylight saving time was active and back then CET was UTC+02:00. That explains the two-hour difference.<\/p>\n\n\n\n
Let’s test the boundary values.<\/p>\n\n\n\n
TIMESTAMP_TO_SCN<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT timestamp_to_scn(TIMESTAMP '2010-09-06 00:40:05') AS SCN FROM dual;<\/span><\/span>\n<\/span><\/span>\n       SCN<\/span><\/span>\n----------<\/span><\/span>\n     18669<\/span><\/span>\n<\/span><\/span>\nSQL> SELECT timestamp_to_scn(TIMESTAMP '2010-09-06 00:40:04') AS SCN FROM dual;<\/span><\/span>\nSELECT timestamp_to_scn(TIMESTAMP '2010-09-06 00:40:04') AS SCN FROM dual<\/span><\/span>\n       *<\/span><\/span>\nERROR at line 1:<\/span><\/span>\nORA-08180: no snapshot found based on specified time<\/span><\/span>\nORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
As expected 2010-09-06 00:40:05 works and 2010-09-06 00:40:04 raises an ORA-8180 error.<\/p>\n\n\n\n
I expect that you get completely different values in your database since the SMON process deletes “old” values in SYS.SMON_SCN_TIME based on UNDO and FBA configuration. Since this table is solely used for timestamp-to-SCN conversion I assume it is safe to extend it manually. In fact, the PL\/SQL package DBMS_FDA_MAPPINGS provided by Oracle is exactly doing that but distributes the remaining SCNs between 1988-01-01 and the MIN(time_dp) in SYS.SMON_SCN_TIME uniformly. In my case there are only 18868 SCNs remaining to be assigned to timestamps before 2010-09-06 00:40:05. So if I know that I won’t need timestamps to be mapped to SCN let’s say before 2010-01-01 I may use the remaining values to improve the precision of timestamp to SCN mappings for this reduced period.<\/p>\n\n\n\n
Extend SMON_SCN_TIME (1)<\/span><\/path><\/path><\/svg><\/span>
SQL> INSERT INTO smon_scn_time (<\/span><\/span>\n  2     thread,<\/span><\/span>\n  3     orig_thread,<\/span><\/span>\n  4     time_mp,<\/span><\/span>\n  5     time_dp,<\/span><\/span>\n  6     scn_wrp,<\/span><\/span>\n  7     scn_bas,<\/span><\/span>\n  8     scn,<\/span><\/span>\n  9     num_mappings<\/span><\/span>\n 10  )<\/span><\/span>\n 11  SELECT 0 AS thread,<\/span><\/span>\n 12         0 AS orig_thread,<\/span><\/span>\n 13         (<\/span><\/span>\n 14            CAST(<\/span><\/span>\n 15               to_timestamp_tz(<\/span><\/span>\n 16                  '2010-09-06 00:40:04 CET',<\/span><\/span>\n 17                  'YYYY-MM-DD HH24:MI:SS TZR'<\/span><\/span>\n 18               ) AT TIME ZONE 'UTC' AS DATE<\/span><\/span>\n 19            ) - DATE '1970-01-01'<\/span><\/span>\n 20         ) * 60 * 60 * 24 AS time_mp,<\/span><\/span>\n 21         CAST(<\/span><\/span>\n 22            to_timestamp_tz(<\/span><\/span>\n 23               '2010-09-06 00:40:04 CET',<\/span><\/span>\n 24               'YYYY-MM-DD HH24:MI:SS TZR'<\/span><\/span>\n 25            ) at TIME ZONE 'UTC' AS DATE<\/span><\/span>\n 26         ) AS time_dp,<\/span><\/span>\n 27         FLOOR((MIN(scn) - 1) \/ POWER(2, 32)) AS scn_wrp,<\/span><\/span>\n 28         MOD(MIN(scn) - 1, POWER(2, 32)) AS scn_bas,<\/span><\/span>\n 29         MIN(scn) - 1 AS scn,<\/span><\/span>\n 30         0 AS num_mappings<\/span><\/span>\n 31    FROM sys.smon_scn_time;<\/span><\/span>\n<\/span><\/span>\n1 row created.<\/span><\/span>\n<\/span><\/span>\nSQL> SELECT timestamp_to_scn(TIMESTAMP '2010-09-06 00:40:04') AS scn FROM dual;<\/span><\/span>\n<\/span><\/span>\n       SCN<\/span><\/span>\n----------<\/span><\/span>\n     18668<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
TIME_MP is the number of seconds since 1970-01-01. TIME_DP is the date value of TIME_MP. The calculation of these columns includes time zone conversion from CET to UTC which makes it a bit verbose. SCN_WRAP counts the number of times SCN_BASE has reached its 32-bit value maximum of 4294967295.<\/p>\n\n\n\n
Extend SMON_SCN_TIME (2)<\/span><\/path><\/path><\/svg><\/span>
INSERT INTO smon_scn_time<\/span><\/span>\n   (thread,<\/span><\/span>\n    orig_thread,<\/span><\/span>\n    time_mp,<\/span><\/span>\n    time_dp,<\/span><\/span>\n    scn_wrp,<\/span><\/span>\n    scn_bas,<\/span><\/span>\n    scn,<\/span><\/span>\n    num_mappings)<\/span><\/span>\n   SELECT 0 AS thread,<\/span><\/span>\n          0 AS orig_thread,<\/span><\/span>\n          (first_time - DATE '1970-01-01') * 60 * 60 * 24 AS time_mp,<\/span><\/span>\n          first_time AS time_dp,<\/span><\/span>\n          floor(first_change# \/ power(2, 32)) AS scn_wrp,<\/span><\/span>\n          MOD(first_change#, power(2, 32)) AS scn_bas,<\/span><\/span>\n          first_change# AS scn,<\/span><\/span>\n          0 AS num_mappings<\/span><\/span>\n     FROM v$log_history<\/span><\/span>\n    WHERE first_time < (SELECT MIN(time_dp)<\/span><\/span>\n                          FROM smon_scn_time);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
BTW: The TIMESTAMP_TO_SCN function uses its own kind of result cache. You may need to restart the database if you undo changes in SYS.SMON_SCN_TIME.<\/p>\n\n\n\n
Next, we should check if the mapping from timestamp to SCN is precise enough, which means an SCN should not be used by multiple timestamps. Here’s the query for T1:<\/p>\n\n\n\n
Check TIMESTAMP_TO_SCN Precision<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT COUNT(DISTINCT ts) AS cnt_ts,<\/span><\/span>\n  2         COUNT(DISTINCT timestamp_to_scn(ts)) AS cnt_ts_to_scn<\/span><\/span>\n  3    FROM (SELECT created_at ts FROM t1<\/span><\/span>\n  4          UNION<\/span><\/span>\n  5          SELECT outdated_at FROM t1<\/span><\/span>\n  6          MINUS<\/span><\/span>\n  7          SELECT TIMESTAMP '9999-12-31 23:59:59' FROM dual);<\/span><\/span>\n<\/span><\/span>\n    CNT_TS CNT_TS_TO_SCN<\/span><\/span>\n---------- -------------<\/span><\/span>\n        10            10<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
T1 is ready to be migrated to a FBA-enabled table if CNT_TS and CNT_TS_TO_SCN are equal. If the values are different you have two options. a) amend T1 (e.g. change timestamps or merge intervals) or b) amend SYS.SMON_SCN_TIME as explained above.<\/p>\n\n\n\n
Migration<\/h3>\n\n\n\n
The following script creates a flashback archive and the FBA-enabled table T2. At the end, a small PL\/SQL block is executed to create views for the 3 SYS_FBA_…_<object_id> tables.<\/p>\n\n\n\n
Create Table T2<\/span><\/path><\/path><\/svg><\/span>
-- create FBA<\/span><\/span>\nCREATE<\/span> FLASHBACK ARCHIVE fba <\/span>TABLESPACE<\/span> USERS QUOTA 10M RETENTION <\/span>10<\/span> YEAR;<\/span><\/span>\n<\/span>\n-- create FBA enabled table t2<\/span><\/span>\nCREATE<\/span> <\/span>TABLE<\/span> t2 (<\/span><\/span>\n   oid         <\/span>NUMBER<\/span>(<\/span>4<\/span>,<\/span>0<\/span>)  <\/span>NOT<\/span> <\/span>NULL<\/span> <\/span>PRIMARY KEY<\/span>,<\/span><\/span>\n   c1          <\/span>VARCHAR2<\/span>(<\/span>10<\/span>) <\/span>NOT<\/span> <\/span>NULL<\/span>,<\/span><\/span>\n   c2          <\/span>VARCHAR2<\/span>(<\/span>10<\/span>) <\/span>NOT<\/span> <\/span>NULL<\/span><\/span>\n) FLASHBACK ARCHIVE fba;<\/span><\/span>\n<\/span>\n-- enforce visibility of SYS_FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>disassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'T2'<\/span>);<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>reassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\n-- create views on SYS_FBA tables<\/span><\/span>\nDECLARE<\/span><\/span>\n   <\/span>PROCEDURE<\/span> <\/span>create_view<\/span>(in_view_name  <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         in_table_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>EXECUTE IMMEDIATE<\/span> <\/span>'CREATE OR REPLACE VIEW '<\/span> || in_view_name ||<\/span><\/span>\n                        <\/span>' AS SELECT * FROM '<\/span> || in_table_name;<\/span><\/span>\n   <\/span>END<\/span> create_view;<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>FOR<\/span> <\/span>l_rec<\/span> <\/span>IN<\/span> (<\/span>SELECT<\/span> object_id<\/span><\/span>\n                   <\/span>FROM<\/span> user_objects<\/span><\/span>\n                  <\/span>WHERE<\/span> object_name = <\/span>'T2'<\/span>)<\/span><\/span>\n   <\/span>LOOP<\/span><\/span>\n      create_view(<\/span>'T2_DDL_COLMAP'<\/span>,<\/span>'SYS_FBA_DDL_COLMAP_'<\/span> || <\/span>l_rec<\/span>.object_id);<\/span><\/span>\n      create_view(<\/span>'T2_HIST'<\/span>, <\/span>'SYS_FBA_HIST_'<\/span> || <\/span>l_rec<\/span>.object_id);<\/span><\/span>\n      create_view(<\/span>'T2_TCRV'<\/span>, <\/span>'SYS_FBA_TCRV_'<\/span> || <\/span>l_rec<\/span>.object_id);<\/span><\/span>\n   <\/span>END LOOP<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Flashback Query (since Oracle9i) and Flashback Data Archive (since Oracle 11g) are tightly coupled. The SYS_FBA_…<object_id> tables are created and delayed by the FBDA background process. DML and querying table T2 is possible anyway with the help of UNDO and Flashback Query. The highlighted lines show the PL\/SQL block to enforce the creation of the SYS_FBA…<object_id> tables. This step is necessary to create the views T2_DDL_COLMAP, T2_HIST and T2_TCRV. These views simplify the access to the underlying tables in my SQL scripts.<\/p>\n\n\n\n
The next script copies data from table T1 to T2. Basically, that’s what the PL\/SQL package DBMS_FDA_IMPORT provided by Oracle does.<\/p>\n\n\n\n
Migration<\/span><\/path><\/path><\/svg><\/span>
-- migrate current rows<\/span><\/span>\nINSERT<\/span> <\/span>INTO<\/span> t2 (OID, c1, c2)<\/span><\/span>\n   <\/span>SELECT<\/span> OID, c1, c2<\/span><\/span>\n     <\/span>FROM<\/span> t1<\/span><\/span>\n    <\/span>WHERE<\/span> outdated_at > <\/span>SYSDATE<\/span>;<\/span><\/span>\nCOMMIT<\/span>;<\/span><\/span>\n<\/span>\n-- enable DML on FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>disassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\n-- migrate T1 rows into T2<\/span><\/span>\nINSERT<\/span> <\/span>INTO<\/span> t2_hist (RID, STARTSCN, ENDSCN, XID, OPERATION, OID, C1, C2)<\/span><\/span>\n-- outdated INSERTs (simulating INSERT\/DELETE logic)<\/span><\/span>\nSELECT<\/span> <\/span>NULL<\/span> <\/span>AS<\/span> rid,<\/span><\/span>\n       <\/span>timestamp_to_scn<\/span>(created_at) <\/span>AS<\/span> startscn,<\/span><\/span>\n       <\/span>timestamp_to_scn<\/span>(outdated_at) <\/span>AS<\/span> endscn,<\/span><\/span>\n       <\/span>NULL<\/span> <\/span>AS<\/span> XID,<\/span><\/span>\n       <\/span>'I'<\/span> <\/span>AS<\/span> operation,<\/span><\/span>\n       OID,<\/span><\/span>\n       c1,<\/span><\/span>\n       c2<\/span><\/span>\n  <\/span>FROM<\/span> t1<\/span><\/span>\n <\/span>WHERE<\/span> outdated_at < <\/span>SYSDATE<\/span><\/span>\n-- current INSERTs (workaround for ORA-55622 on insert into T2_TRCV)<\/span><\/span>\nUNION<\/span> <\/span>ALL<\/span><\/span>\nSELECT<\/span> t2.<\/span>rowid<\/span> <\/span>AS<\/span> rid,<\/span><\/span>\n       <\/span>timestamp_to_scn<\/span>(t1.created_at) <\/span>AS<\/span> startscn,<\/span><\/span>\n       h.startscn <\/span>AS<\/span> endscn,<\/span><\/span>\n       <\/span>NULL<\/span> <\/span>AS<\/span> XID,<\/span><\/span>\n       <\/span>'I'<\/span> <\/span>AS<\/span> operation,<\/span><\/span>\n       t2.OID,<\/span><\/span>\n       t2.c1,<\/span><\/span>\n       t2.c2<\/span><\/span>\n  <\/span>FROM<\/span> t1 t1<\/span><\/span>\n INNER <\/span>JOIN<\/span> t2<\/span><\/span>\n    <\/span>ON<\/span> t2.oid = t1.oid<\/span><\/span>\n INNER <\/span>JOIN<\/span> t2_tcrv h<\/span><\/span>\n    <\/span>ON<\/span> h.RID = t2.<\/span>rowid<\/span><\/span>\n <\/span>WHERE<\/span> t1.outdated_at > <\/span>SYSDATE<\/span>;<\/span><\/span>\n<\/span>\nCOMMIT<\/span>;<\/span><\/span>\n<\/span>\n-- disable DML on FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>reassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'t2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The following queries return data valid at 2012-12-23 19:36:08 and now (as for T1 above).<\/p>\n\n\n\n
Point in Time Queries for T2<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t2 AS OF SCN timestamp_to_scn(TIMESTAMP '2012-12-23 19:36:08');<\/span><\/span>\n<\/span><\/span>\nOID C1 C2<\/span><\/span>\n--- -- --<\/span><\/span>\n  1 A  A2<\/span><\/span>\n  2 B  B1<\/span><\/span>\n<\/span><\/span>\nSQL> SELECT * FROM t2;<\/span><\/span>\n<\/span><\/span>\nOID C1 C2<\/span><\/span>\n--- -- --<\/span><\/span>\n  1 A  A4<\/span><\/span>\n  2 B  B1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
As you see the results are identical with the ones of T1. The script here<\/a> compares the result for every single point in time in T1 with T2. I’ve run it without detecting differences.<\/p>\n\n\n\n
Migration Issue 1 – ORA-1466 When Using AS OF TIMESTAMP Clause<\/h3>\n\n\n\n
In the examples above I avoided the use of the AS OF TIMESTAMP clause and used the AS OF SCN clause instead. The reason becomes apparent when executing the following query:<\/p>\n\n\n\n
ORA-1466<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t2 AS OF TIMESTAMP TIMESTAMP '2012-12-23 19:36:08';<\/span><\/span>\nSELECT * FROM t2 AS OF TIMESTAMP TIMESTAMP '2012-12-23 19:36:08'<\/span><\/span>\n              *<\/span><\/span>\nERROR at line 1:<\/span><\/span>\nORA-01466: unable to read data - table definition has changed<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Ok, we have not changed the table definition, but was the table definition for T2 valid on 2012-12-23 19:36:08?<\/p>\n\n\n\n
Validity of DDL_COLMAP<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT column_name,<\/span><\/span>\n  2         startscn,<\/span><\/span>\n  3         endscn,<\/span><\/span>\n  4         CAST(scn_to_timestamp(startscn) AS DATE) AS start_ts<\/span><\/span>\n  5    FROM t2_ddl_colmap;<\/span><\/span>\n<\/span><\/span>\nCOLUMN_NAME       STARTSCN     ENDSCN START_TS<\/span><\/span>\n----------- -------------- ---------- -------------------<\/span><\/span>\nOID           161382016632            2013-01-03 00:17:42<\/span><\/span>\nC1            161382016632            2013-01-03 00:17:42<\/span><\/span>\nC2            161382016632            2013-01-03 00:17:42<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Since I created the table on 2013-01-03 00:17:42 Oracle assumes that no columns exist before this point in time. However, the AS OF SCN clause is not that picky.<\/p>\n\n\n\n
To fix the problem we need to update T2_DDL_COLMAP but unfortunately DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA does not allow us to change the content of the DDL_COLMAP directly (it is possible indirectly by altering the HIST table, but this is not helpful in this case).<\/p>\n\n\n\n
Here is the script to fix the validity of the DDL_COLMAP:<\/p>\n\n\n\n
Fix Validity of DDL_COLMAP<\/span><\/path><\/path><\/svg><\/span>
-- enable DML FBA table<\/span><\/span>\nBEGIN<\/span><\/span>\n   tvd_fba_helper.disassociate_col_map(in_owner_name => <\/span>USER<\/span>, in_table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\n-- enforce ddl colmap consistentcy (valid for first entries)<\/span><\/span>\nUPDATE<\/span> t2_ddl_colmap<\/span><\/span>\n   <\/span>SET<\/span> startscn =<\/span><\/span>\n       (<\/span>SELECT<\/span> <\/span>MIN<\/span>(startscn)<\/span><\/span>\n          <\/span>FROM<\/span> t2_hist);<\/span><\/span>\nCOMMIT<\/span>;<\/span><\/span>\n<\/span>\n-- disable DML an FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   tvd_fba_helper.reassociate_col_map(in_owner_name => <\/span>USER<\/span>, in_table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now the AS OF TIMESTAMP clause works as well:<\/p>\n\n\n\n
No ORA-1466 anymore<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT * FROM t2 AS OF TIMESTAMP TIMESTAMP '2012-12-23 19:36:08';<\/span><\/span>\n<\/span><\/span>\nOID C1 C2<\/span><\/span>\n--- -- --<\/span><\/span>\n  1 A  A2<\/span><\/span>\n  2 B  B1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Migration Issue 2 – Different Number of Intervals<\/h3>\n\n\n\n
If you query the full history of T2 you get 10 rows, but T1 contains 8 rows only.<\/p>\n\n\n\n
T2 Versions<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT ROWNUM AS vid, OID, created_at, outdated_at, c1, c2<\/span><\/span>\n  2    FROM (SELECT OID,<\/span><\/span>\n  3                 TO_DATE(TO_CHAR(versions_starttime, 'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  4                         'YYYY-MM-DD HH24:MI:SS') AS created_at,<\/span><\/span>\n  5                 NVL(TO_DATE(TO_CHAR(versions_endtime, 'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  6                             'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  7                     TIMESTAMP '9999-12-31 23:59:59') AS outdated_at,<\/span><\/span>\n  8                 c1,<\/span><\/span>\n  9                 c2<\/span><\/span>\n 10            FROM t2 VERSIONS BETWEEN TIMESTAMP TIMESTAMP '2012-12-19 13:00:57'<\/span><\/span>\n 11                                     AND SYSTIMESTAMP<\/span><\/span>\n 12           ORDER BY 1, 2);<\/span><\/span>\n<\/span><\/span>\nVID OID CREATED_AT          OUTDATED_AT         C1 C2<\/span><\/span>\n--- --- ------------------- ------------------- -- --<\/span><\/span>\n  1   1 2012-12-19 13:00:53 2012-12-21 08:20:19 A  A1<\/span><\/span>\n  2   1 2012-12-21 08:20:19 2012-12-23 20:58:03 A  A2<\/span><\/span>\n  3   1 2012-12-23 20:58:03 2012-12-27 11:40:40 A  A3<\/span><\/span>\n  4   1 2012-12-27 11:40:40 2013-01-03 00:50:19 A  A4<\/span><\/span>\n  5   1 2013-01-03 00:50:19 9999-12-31 23:59:59 A  A4<\/span><\/span>\n  6   2 2012-12-20 13:51:54 2013-01-03 00:50:19 B  B1<\/span><\/span>\n  7   2 2013-01-03 00:50:19 9999-12-31 23:59:59 B  B1<\/span><\/span>\n  8   4 2012-12-22 10:26:35 2012-12-23 19:36:07 C  C1<\/span><\/span>\n  9   4 2012-12-28 14:25:50 2012-12-30 17:10:38 C  C1<\/span><\/span>\n 10   4 2012-12-30 17:10:38 2012-12-31 12:05:39 C  C2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The highlighted rows for OID 1 and 2 could be merged. The content is not really wrong it’s just different to T1. The reason is, that DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA does not allow us to modify T2_TCRV (SYS_FBA_TCRV…<object_id> table). This table contains validity information for the current rows in T2.<\/p>\n\n\n\n
Validity of TCRV<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT rid,<\/span><\/span>\n  2         startscn,<\/span><\/span>\n  3         CAST(scn_to_timestamp(startscn) AS DATE) AS start_ts,<\/span><\/span>\n  4         endscn,<\/span><\/span>\n  5         op<\/span><\/span>\n  6    FROM t2_tcrv;<\/span><\/span>\n<\/span><\/span>\nRID                      STARTSCN START_TS                ENDSCN O<\/span><\/span>\n------------------ -------------- ------------------- ---------- -<\/span><\/span>\nAAAZ1PAAIAAAAb0AAA   161382018095 2013-01-03 00:50:19            I<\/span><\/span>\nAAAZ1PAAIAAAAb0AAB   161382018095 2013-01-03 00:50:19            I<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
That’s why I had to insert the actual rows in T2_HIST.<\/p>\n\n\n\n
But with the help of the PL\/SQL package TVD_FBA_HELPER<\/a> the data may be fixed as follows:<\/p>\n\n\n\n
Fix Validity of TCRV<\/span><\/path><\/path><\/svg><\/span>
-- enable DML on FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>disassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'T2'<\/span>);<\/span><\/span>\n   tvd_fba_helper.disassociate_tcrv(in_owner_name => <\/span>USER<\/span>, in_table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\n-- extend begin of validity in TCRV table and fix HIST table accordingly<\/span><\/span>\nMERGE<\/span> <\/span>INTO<\/span> t2_tcrv t<\/span><\/span>\nUSING<\/span> (<\/span>SELECT<\/span> rid, startscn <\/span>FROM<\/span> t2_hist b) s<\/span><\/span>\n   <\/span>ON<\/span> (s.rid = t.rid)<\/span><\/span>\n <\/span>WHEN<\/span> <\/span>MATCHED<\/span> <\/span>THEN<\/span><\/span>\n    <\/span>UPDATE<\/span> <\/span>SET<\/span> t.startscn = s.startscn;<\/span><\/span>\nDELETE<\/span> <\/span>FROM<\/span> t2_hist <\/span>WHERE<\/span> rid <\/span>IN<\/span> (<\/span>SELECT<\/span> rid <\/span>FROM<\/span> t2_tcrv);<\/span><\/span>\nCOMMIT<\/span>;<\/span><\/span>\n<\/span>\n-- disable DML an FBA tables<\/span><\/span>\nBEGIN<\/span><\/span>\n   tvd_fba_helper.reassociate_tcrv(in_owner_name => <\/span>USER<\/span>, in_table_name => <\/span>'T2'<\/span>);<\/span><\/span>\n   <\/span>dbms_flashback_archive.<\/span>reassociate_fba<\/span>(owner_name => <\/span>USER<\/span>, table_name => <\/span>'T2'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now the query returns 8 rows:<\/p>\n\n\n\n
Plaintext<\/span><\/path><\/path><\/svg><\/span>
SQL> SELECT ROWNUM AS vid, OID, created_at, outdated_at, c1, c2<\/span><\/span>\n  2    FROM (SELECT OID,<\/span><\/span>\n  3                 TO_DATE(TO_CHAR(versions_starttime, 'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  4                         'YYYY-MM-DD HH24:MI:SS') AS created_at,<\/span><\/span>\n  5                 NVL(TO_DATE(TO_CHAR(versions_endtime, 'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  6                             'YYYY-MM-DD HH24:MI:SS'),<\/span><\/span>\n  7                     TIMESTAMP '9999-12-31 23:59:59') AS outdated_at,<\/span><\/span>\n  8                 c1,<\/span><\/span>\n  9                 c2<\/span><\/span>\n 10            FROM t2 VERSIONS BETWEEN TIMESTAMP TIMESTAMP '2012-12-19 13:00:57'<\/span><\/span>\n 11                                     AND SYSTIMESTAMP<\/span><\/span>\n 12           ORDER BY 1, 2);<\/span><\/span>\n<\/span><\/span>\nVID OID CREATED_AT          OUTDATED_AT         C1 C2<\/span><\/span>\n--- --- ------------------- ------------------- -- --<\/span><\/span>\n  1   1 2012-12-19 13:00:53 2012-12-21 08:20:19 A  A1<\/span><\/span>\n  2   1 2012-12-21 08:20:19 2012-12-23 20:58:03 A  A2<\/span><\/span>\n  3   1 2012-12-23 20:58:03 2012-12-27 11:40:40 A  A3<\/span><\/span>\n  4   1 2012-12-27 11:40:40 9999-12-31 23:59:59 A  A4<\/span><\/span>\n  5   2 2012-12-20 13:51:54 9999-12-31 23:59:59 B  B1<\/span><\/span>\n  6   4 2012-12-22 10:26:35 2012-12-23 19:36:07 C  C1<\/span><\/span>\n  7   4 2012-12-28 14:25:50 2012-12-30 17:10:38 C  C1<\/span><\/span>\n  8   4 2012-12-30 17:10:38 2012-12-31 12:05:39 C  C2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The timestamps are slightly different to the ones in T1, but that’s expected behaviour since the precision of TIMESTAMP_TO_SCN conversion is limited to around 3 seconds.<\/p>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
Loading historical data into FBA enable tables requires a strategy to populate historical mappings in SYS.SMON_SCN_TIME. Afterwards, you may load the associated SYS_FBA_HIST_<object_id> table with the help of the Oracle-supplied PL\/SQL package DBMS_FLASHBACK_ARCHIVE and its procedures DISASSOCIATE_FBA and REASSOCIATE_FBA. I recommend this approach for 11gR2 Database environments.<\/p>\n\n\n\n
The solutions to fix migration issue 1 (ORA-1466 when using AS OF TIMESTAMP clause) and migration issue 2 (different number of intervals) are considered experimental. I suggest contacting Oracle Support to discuss how to proceed if you need a solution in this area.<\/p>\n\n\n\n
Updated on 2014-04-10, changed the calculation of SCN_WRP and SCN_BAS in Extend SMON_SCN_TIME (1)<\/a> and Extend SMON_SCN_TIME (2)<\/a>, changed link to new version of TVD_FBA_HELPER<\/a>.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle provides via OTN an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period\/precision or ORA-1466 when using the AS OF TIMESTAMP [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":9543,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[50,68,42,49],"class_list":["post-545","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-flashback","tag-flashback-data-archive","tag-temporal-database","tag-transaction-time"],"yoast_head":"\nLoading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Loading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"Oracle provides via OTN an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period\/precision or ORA-1466 when using the AS OF TIMESTAMP [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2013-01-03T02:34:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T19:05:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png\" \/>\n\t<meta property=\"og:image:width\" content=\"231\" \/>\n\t<meta property=\"og:image:height\" content=\"172\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Loading Historical Data Into Flashback Archive Enabled Tables\",\"datePublished\":\"2013-01-03T02:34:39+00:00\",\"dateModified\":\"2023-11-08T19:05:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/\"},\"wordCount\":1638,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/flight_recorder.png\",\"keywords\":[\"Flashback\",\"Flashback Data Archive\",\"Temporal Database\",\"Transaction Time\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/\",\"name\":\"Loading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/flight_recorder.png\",\"datePublished\":\"2013-01-03T02:34:39+00:00\",\"dateModified\":\"2023-11-08T19:05:23+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/flight_recorder.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2013\\\/01\\\/flight_recorder.png\",\"width\":231,\"height\":172},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2013\\\/01\\\/03\\\/loading-historical-data-into-flashback-archive-enabled-tables\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Loading Historical Data Into Flashback Archive Enabled Tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Loading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/","og_locale":"en_US","og_type":"article","og_title":"Loading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog","og_description":"Oracle provides via OTN an import solution for FBA (Flashback Data Archive also known as Total Recall). The solution extends the SCN to TIMESTAMP mapping plus provides a wrapper to existing APIs to populate the history. However, issues like using a customized mapping period\/precision or ORA-1466 when using the AS OF TIMESTAMP [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2013-01-03T02:34:39+00:00","article_modified_time":"2023-11-08T19:05:23+00:00","og_image":[{"width":231,"height":172,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Loading Historical Data Into Flashback Archive Enabled Tables","datePublished":"2013-01-03T02:34:39+00:00","dateModified":"2023-11-08T19:05:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/"},"wordCount":1638,"commentCount":7,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png","keywords":["Flashback","Flashback Data Archive","Temporal Database","Transaction Time"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/","url":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/","name":"Loading Historical Data Into Flashback Archive Enabled Tables - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png","datePublished":"2013-01-03T02:34:39+00:00","dateModified":"2023-11-08T19:05:23+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2013\/01\/flight_recorder.png","width":231,"height":172},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2013\/01\/03\/loading-historical-data-into-flashback-archive-enabled-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Loading Historical Data Into Flashback Archive Enabled Tables"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=545"}],"version-history":[{"count":10,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/545\/revisions"}],"predecessor-version":[{"id":12751,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/545\/revisions\/12751"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/9543"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
I’ve written a PL\/SQL package TVD_FBA_HELPER<\/a> which updates SYS.TAB$ behind the scenes to overcome this restriction. Please consult Oracle Support on how to proceed if you plan to use it in productive environments. The package is provided as is and of course, you are using it at your own risk.<\/p>\n\n\n\n
Alternatively, you may simply extend the SYS.SMON_SCN_TIME based on SYS.V$LOG_HISTORY.
<\/a><\/p>\n\n\n\n
In my case, I do not need to extend the mapping in SYS.SMON_SCN_TIME, but here is an example of how it can be done:
<\/a><\/p>\n\n\n\n