Hi Folks,
I am verifying Oracle Flashback Data Archive for our application. My Database Version is
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
We have a BI Tool which accesses our Data via Views, so the Flashback Queries will be issued against those views. Now there is some issue I am having with running flashback Queries against that views: it seems the database doesn't “know” that it should use FBDA on that table if the select statement is issued against that view.
e.g. in the HR Schema flashback enabled upon the employees table; I set undo_retention to a low value, limited the undo tablespace, changed a few employee records, and started a big DML Operation on another table to “flush” the employee records out of the undo tablespace to be sure that FBDA is used.
I create a view upon employees:
create or replace force view v_employees as select * from employees;
Now I run
select * from employees as of timestamp <…> order by employee_id desc
No problem here, the data gets returned. Then I run
select * from v_employees as of timestamp <the very same timestamp> order by employee_id desc
And I get ORA-1555. Obviously the undo records aren't there anymore in the undo tablespace, but clearly they are in the FBDA. I can see the modified records as well in the SYS_FBA_HIST_12345 Table I picked up from dba_flashback_archive_tables.
I processed the SQL Statement via dbms_utility.expand_sql_text ; at least this function seems to “push down” the as of timestamp clause to the real object:
select expand_sql_text(q'[select * from v_employees as of timestamp to_timestamp('24.05.2023 09:00', 'dd.mm.yyyy hh24:mi') order by employee_id desc]') from dual;
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","A1"."EMAIL" "EMAIL","A1"."PHONE_NUMBER" "PHONE_NUMBER","A1"."HIRE_DATE" "HIRE_DATE","A1"."JOB_ID" "JOB_ID","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1"."MANAGER_ID" "MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM (SELECT "A2"."EMPLOYEE_ID" "EMPLOYEE_ID","A2"."FIRST_NAME" "FIRST_NAME","A2"."LAST_NAME" "LAST_NAME","A2"."EMAIL" "EMAIL","A2"."PHONE_NUMBER" "PHONE_NUMBER","A2"."HIRE_DATE" "HIRE_DATE","A2"."JOB_ID" "JOB_ID","A2"."SALARY" "SALARY","A2"."COMMISSION_PCT" "COMMISSION_PCT","A2"."MANAGER_ID" "MANAGER_ID","A2"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."EMPLOYEES" AS OF TIMESTAMP (TO_TIMESTAMP('24.05.2023 09:00','dd.mm.yyyy hh24:mi')) "A2") AS OF TIMESTAMP (TO_TIMESTAMP('24.05.2023 09:00','dd.mm.yyyy hh24:mi')) "A1" ORDER BY "A1"."EMPLOYEE_ID" DESC
(The function expand_sql_text is a simple wrapper to the packaged function which returns a clob so I can use it in SQL).
If I execute the resulting query obviously this works again as expected. So IMHO the SQL Engine doesn't seem to recognize that there is a FBDA Table involved once this Table is “wrapped” in a view. Is this a known limitation? Did I miss something in the docs? Or is this a “feature”
Thanks & Regards
Christian