04 April, 2016

FBDA -- 2 : FBDA Archive Table Structure

Following up on my earlier post, I look at the FBDA Archive Tables.

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 3 23:26:27 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 02 2016 23:32:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
TEST_FBDA
SYS_FBA_DDL_COLMAP_93250
SYS_FBA_HIST_93250
SYS_FBA_TCRV_93250

SQL> desc test_fbda
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE

SQL> 
SQL> desc sys_fba_hist_93250
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID          VARCHAR2(4000)
 STARTSCN         NUMBER
 ENDSCN          NUMBER
 XID          RAW(8)
 OPERATION         VARCHAR2(1)
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE

SQL> desc sys_fba_ddl_colmap_93250
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 STARTSCN         NUMBER
 ENDSCN          NUMBER
 XID          RAW(8)
 OPERATION         VARCHAR2(1)
 COLUMN_NAME         VARCHAR2(255)
 TYPE          VARCHAR2(255)
 HISTORICAL_COLUMN_NAME        VARCHAR2(255)

SQL> desc sys_fba_tcrv_93250
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID          VARCHAR2(4000)
 STARTSCN         NUMBER
 ENDSCN          NUMBER
 XID          RAW(8)
 OP          VARCHAR2(1)

SQL> 


The HIST table is the History table for my active table. It adds columns that track Rowid, Start SCB and End SCN for a range of rows that are copied into the History Table, Transaction Identifier, Operation and then the actual columns of the active table.
The DDL_COLMAP table seems to track Column Mappings.  See example below.
The TCRV table seems to be tracking Transactions ?

Let's try some DDL to ADD and DROP columns to the active table.

SQL> alter table test_fbda add (new_col_1 varchar2(5));

Table altered.

SQL> desc test_fbda
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE
 NEW_COL_1         VARCHAR2(5)

SQL> desc sys_fba_93250
ERROR:
ORA-04043: object sys_fba_93250 does not exist


SQL> desc sys_fba_hist_93250
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID          VARCHAR2(4000)
 STARTSCN         NUMBER
 ENDSCN          NUMBER
 XID          RAW(8)
 OPERATION         VARCHAR2(1)
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE
 NEW_COL_1         VARCHAR2(5)

SQL> 
SQL> select * from sys_fba_ddl_colmap_93250
  2  /

  STARTSCN     ENDSCN XID        O
---------- ---------- ---------------- -
COLUMN_NAME
--------------------------------------------------------------------------------
TYPE
--------------------------------------------------------------------------------
HISTORICAL_COLUMN_NAME
--------------------------------------------------------------------------------
   1697151
ID_COLUMN
NUMBER
ID_COLUMN

   1697151
DATA_COLUMN
VARCHAR2(15)
DATA_COLUMN

   1697151
DATE_INSERTED
DATE
DATE_INSERTED

   1728713
NEW_COL_1
VARCHAR2(5)
NEW_COL_1


SQL> 


The new column added to the active table is also now reflected in the History Table.  The DDL_COLMAP shows the effective start of each column (notice the STARTSCN mapped to the COLUMN_NAME)

Let's set some values in new column and see if they appear in the History Table.

SQL> update test_fbda set new_col_1 = 'New'
  2  where id_column < 6;

5 rows updated.

SQL> commit;

Commit complete.

SQL> select id_column, new_col_1, scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  2  from sys_fba_hist_93250
  3  where id_column < 6 
  4  order by 1,3;

 ID_COLUMN NEW_C
---------- -----
SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
  1
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  2
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  3
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  4
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  5
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM


SQL> 


What rows are copied into the History Table are *prior* image rows (copied from the Undo Area).  The STARTSCN and ENDSCN are of *yesterday* (02-April).

Let me DROP the new column.

SQL> alter table test_fbda drop (new_col_1);

Table altered.

SQL> desc test_fbda
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE

SQL> desc sys_fba_hist_93250;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID          VARCHAR2(4000)
 STARTSCN         NUMBER
 ENDSCN          NUMBER
 XID          RAW(8)
 OPERATION         VARCHAR2(1)
 ID_COLUMN         NUMBER
 DATA_COLUMN         VARCHAR2(15)
 DATE_INSERTED         DATE
 D_1729869_NEW_COL_1        VARCHAR2(5)

SQL>
SQL> select * from sys_fba_ddl_colmap_93250;

  STARTSCN     ENDSCN XID        O
---------- ---------- ---------------- -
COLUMN_NAME
--------------------------------------------------------------------------------
TYPE
--------------------------------------------------------------------------------
HISTORICAL_COLUMN_NAME
--------------------------------------------------------------------------------
   1697151
ID_COLUMN
NUMBER
ID_COLUMN

   1697151
DATA_COLUMN
VARCHAR2(15)
DATA_COLUMN

   1697151
DATE_INSERTED
DATE
DATE_INSERTED

   1728713    1729869
D_1729869_NEW_COL_1
VARCHAR2(5)
NEW_COL_1


SQL> 


The dropped column is no longer in the active table  and has been renamed in the History table.  (The data in the column has to be preserved but the column is renamed).  Notice how the DDL_COLMAP table now shows an ENDSCN for this column, with the new (renamed)  column as in the History table.  The column name seems to include the SCN (ENDSCN ?)

Let's confirm what data is now present in the History table  (remember : Our earlier query showed the pre-update image for this column).

SQL> select id_column, D_1729869_NEW_COL_1, scn_to_timestamp(startscn), scn_to_timestamp(endscn)
  2  from sys_fba_hist_93250
  3  where (id_column < 6 OR D_1729869_NEW_COL_1 is not null)
  4  order by 1,3;

 ID_COLUMN D_172
---------- -----
SCN_TO_TIMESTAMP(STARTSCN)
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ENDSCN)
---------------------------------------------------------------------------
  1
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  1
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

  1 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

  2
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  2
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

  2 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

  3
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  3
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

  3 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

  4
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  4
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

  4 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM

  5
02-APR-16 11.32.55.000000000 PM
02-APR-16 11.46.11.000000000 PM

  5
02-APR-16 11.46.11.000000000 PM
03-APR-16 11.41.33.000000000 PM

  5 New
03-APR-16 11.41.33.000000000 PM
03-APR-16 11.45.24.000000000 PM


15 rows selected.

SQL> 
SQL> select scn_to_timestamp(1729869) from dual;

SCN_TO_TIMESTAMP(1729869)
---------------------------------------------------------------------------
03-APR-16 11.45.27.000000000 PM

SQL> 


Why do we now have 3 rows in the History table for each row in the Active Table ?  Take ID_COLUMN=1.  The first row -- for the time range 02-Apr 11:32pm to 02-Apr 11:46pm--  is as of yesterday, the same row we saw in the History table after the update in the active table.  The second row is the representation to preserve the row for the time rang 02-Apr 11:46pm to 03-Apr 11:41pm to support AS OF queries upto the time of the UPDATE.  The third row for the time range 03-Apr 11:41pm to 03-Apr 11:45pm is to present the UPDATEd value ('New') in the column upto the last transaction updating it before the column was dropped at 03-Apr 11:45:27pm.

Thus, Oracle maintains multiple versions of the same row, including versions for DROPped columns, in the History Table.

Note :  The History Table is not supposed to be directly queried in the manner I have shown here.  The proper query against the active table would be an AS OF query which is automatically rewritten / redirected to "hit" the History table when necessary.

What about the third table table -- the TCRV table ?

SQL> l
  1  select scn_to_timestamp(startscn), op , count(*)
  2  from sys_fba_tcrv_93250
  3  group by scn_to_timestamp(startscn), op
  4* order by 2,1
SQL> /

SCN_TO_TIMESTAMP(STARTSCN)          O
--------------------------------------------------------------------------- -
  COUNT(*)
----------
03-APR-16 11.45.24.000000000 PM          U
      1000


SQL>
SQL> select count(distinct(rid)) from sys_fba_tcrv_93250;

COUNT(DISTINCT(RID))
--------------------
  1000

SQL> 
 


It shows 1000 rows has having been UPDATEd ? (Does OP='U' mean 'UPDATE). We do know that ADD and DROP column are changes to the table.  But are they UPDATEs ?

Next post : Continuing with DML operations (more rows, some updates).  We'll see if we can decipher anything rom the TCRV table as well. Changed to showing support for TRUNCATEs.
.
.
.



3 comments:

Anonymous said...

Hi, Hemant.

I also have done some investigation in past about FDBA.
As I understand TCRV table stores last changes on rows in table. There is a job which delete or update them periodically. It's the main cause that your see some strange behavior in query below:

In your last query
select count(distinct(rid)) from sys_fba_tcrv_93250;
COUNT(DISTINCT(RID))
--------------------
1000

There are a lot of rows with "U" in column "OP" table sys_fba_tcrv_93250
But all these rows are deleted.

If your look at some of these rows you find "endscn" in sys_fba_tcrv_93250 is the same as "XID" in SYS_FBA_HIST belonging to operation delete.

All rows in sys_fba_tcrv will be deleted by some job later(I observe the same behavior in my PC).


Evgeniy.

Hemant K Chitale said...

I haven't yet seen the rows getting purged from the TCRV tabl.

Anonymous said...

TCRV represents the current row - it should always be present. We have noticed brief moments where there are two rows in TCRV (one with an ENDSCN)... which then gets cleaned up by what I believe is called the 'lazy FBA writer'.