Following up on my earlier post, I look at the FBDA Archive Tables.
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.
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.
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.
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).
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 ?
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.
.
.
.
[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 :
.
.
.
3 comments:
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.
I haven't yet seen the rows getting purged from the TCRV tabl.
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'.
Post a Comment