From what I understand, Flashback query requires that you use undo tablespaces, or at least setting the UNDO_RETENTION parameter does. Is that true?

If it is, doesn't using flashback imply that you're going to be using UNDO tablespaces vs. RBS? Or that you'd potentially be keeping undo around a lot longer than you'd normally have to just to handle consistent reads. Can either of these two changes can affect application performance? (the second, obviously, means greater disk usage)

From the 9i Application Developer's Guide, section 7:

<quote>
Setting Up the Database for Flashback Query

Before you can perform flashback queries, enlist the help of your DBA. Ask them to:

Use automatic undo management to maintain read consistency, rather than the older technique using rollback segments. In particular, the DBA should:

Set the UNDO_RETENTION initialization parameter to a value that represents how far in the past you might want to query. The value depends on your needs. If you only need to recover data immediately after a mistaken change is committed, the parameter can be set to a small value. If you need to recover deleted data from days before, you might need several days worth of data.

Set the initialization parameter UNDO_MANAGEMENT=AUTO.

Create an UNDO tablespace, with enough space to keep the required data. The more often the data is updated, the more space is required. Calculating the space requirements is a job for the DBA; you can find the formula in the Oracle9i Database Administrator's Guide.
</quote>

It's unclear as to whether they're saying it's just a good idea or required to use auto-undo-management. That ambiguity has caused at least a few people to declare it's required. Of course, anyone writing an article should have checked, but...

Thanks for clearing things up as usual.

Yes, that would be the best way (so you KNOW how far you can flash) but it is not the ONLY way:

ops$tkyte@ORA920.US.ORACLE.COM> show parameter undo_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL

ops$tkyte@ORA920.US.ORACLE.COM> connect scott/tiger


scott@ORA920.US.ORACLE.COM> update emp set sal = sal * 1.5;
14 rows updated.

scott@ORA920.US.ORACLE.COM> commit;
Commit complete.

scott@ORA920.US.ORACLE.COM> select a.ename, a.sal new_sal, b.sal old_sal
  2    from emp a, <b>emp as of timestamp sysdate-5/24/60 b</b>
  3   where a.empno = b.empno
  4  /

ENAME         NEW_SAL    OLD_SAL
---------- ---------- ----------
SMITH            1200        800
ALLEN            2400       1600
WARD             1875       1250
JONES          4462.5       2975
MARTIN           1875       1250
BLAKE            4275       2850
CLARK            3675       2450
SCOTT            4500       3000
KING             7500       5000
TURNER           2250       1500
ADAMS            1650       1100
JAMES            1425        950
FORD             4500       3000
MILLER           1950       1300

14 rows selected.

scott@ORA920.US.ORACLE.COM> update emp
  2     set sal = ( select sal from emp <b>as of timestamp</b> sysdate-5/24/60 b where b.empno = emp.empno )
  3  /

14 rows updated.

scott@ORA920.US.ORACLE.COM> commit;
Commit complete.

scott@ORA920.US.ORACLE.COM> select ename, sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

scott@ORA920.US.ORACLE.COM>


shows that using old fashioned RBS works as well. 

Hi Tom,

Would this work in 8i?

Because TIMESTAMP exists only in 9.

Regards,
Sagi

May be I am confusing things but how does this differ
from the workspace management ability to go to any
point of time and do a select.I know that workspace
offers much more but in terms of the ability to go back
in time and select records, are there any differences.
If we need to maintain history of table data can
we just use flashback. As I am typing in one thing that
comes to my mind is that flashback probably depends on
the undo retention periof where as WM does not?

Please elaborate.

And hats off to you for your awesome support to all of us!

is that with workspace you could go back forever?

Is that true, Tom?

Thanx!

Could you please provide a link to learn how to implement using workspace management? Thanks.

Tom, does flashback work for CLOB and BLOB also? Can I flashback to sometime if somebody deleted rows containing BLOBs and CLOBs? Thanks.

Someone on the OTN Forums suggested using Flashback Query against ALL_SOURCE as a way of retrieving the text of an accidentally (and not backed-up) procedure. However, it doesn't seem to work.

I would hazard that this is because ALL_SOURCE isn't really a table and data wasn't deleted as such: it was erased as a side effect of an act of DDL. Flashback query is not going to work in this case because the source code of the procedure is not written to the undo tablespace.

Is this correct or do you have a better expalnation?

Cheers, APC

ops$tkyte@ORA920PC> select text from all_source where name = 'HK_ANALYZE';
 
TEXT
-----------------------------------------------------------------------------------------------------------------------------------
procedure hk_analyze
(v_schema in varchar2,
 v_estimate_pct in number default 0,
....
    DBMS_OUTPUT.PUT_LINE('Analysointi alkoi: ' || start_time);
    DBMS_OUTPUT.PUT_LINE('Analysointi loppui: ' || stop_time);
end hk_analyze;
 
43 rows selected.
 
ops$tkyte@ORA920PC> drop procedure hk_analyze;
Procedure dropped.


ops$tkyte@ORA920PC> @connect "/ as sysdba"

sys@ORA920PC>  select text from all_source where name = 'HK_ANALYZE';
no rows selected
 
sys@ORA920PC>  select text from all_source as of timestamp sysdate-5/24/60 where name = 'HK_ANALYZE';
 
TEXT
-----------------------------------------------------------------------------------------------------------------------------------
procedure hk_analyze
(v_schema in varchar2,
 v_estimate_pct in number default 0,
 v_degree in number default 1)
...
    DBMS_OUTPUT.PUT_LINE('Analysointi alkoi: ' || start_time);
    DBMS_OUTPUT.PUT_LINE('Analysointi loppui: ' || stop_time);
end hk_analyze;
 
43 rows selected.
 



<b>what issue did you encounter?</b>

 

Apparently using Flashback Query against ALL_SOURCE works with SYSDBA accounts but not mere mortals. So my guess was wrong.

Does this mean the contents of data dictionary tables are written to UNDO when we execute DDL?

Cheers, APC

Tom,

With flashback queries, can filter a set of records ?

For example, at 14.00 can I select the records inserted between 08.00 and 11.00 ?

Thanks in advance.

-San

Thanks Tom.

I read the documentation and identified that I can identify insertions and deletion with simple queries using minus.
And to identify updates I can check some key with some other data.

I'd like to know that is flashback query is a solid method for such a thing ?
Currently, we use triggers to identify data changes (updates, inserts, deleles). But these triggers slows downs the operations.
Is it a good idea to replace this trigger system with flashback query system ? Is it reliable ?

Thanks in advance.

And when are you going to release 10g ?

[...well, until 10g when you can "guarantee" the undo retention...]

About the statement above, how exactally will this "undo guarantee" work? In 10g, will it be possible to flashback to any time in the past? (I heard something about, but I´m not sure).

Thank you.

Hi Tom,
FLASHBACK query gives the information at specified time.
1.where does it store those information?
2.How does oracle get it?

By default flashback table is enable -- Is it possible disable flashback table?

Always to drop (real drop) do I must "drop table t purge;"?

Isn't it an overhead?

Hi Tom,

SQL> select ename,sal from emp;

ENAME             SAL
---------- ----------
KING             5000
BLAKE            2850
CLARK            2450
JONES            2975
FORD             3000
SMITH             800
ALLEN            1600
WARD             1250
MARTIN           1250
SCOTT            3000
TURNER           1500

ENAME             SAL
---------- ----------
ADAMS            1100
JAMES             950
MILLER           1300

14 rows selected.

SQL> update emp set sal=sal*1.5
  2  /

14 rows updated.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       7500                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       4275                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       3675                    10
      7566 JONES      MANAGER         7839 02-APR-81     4462.5                    20
      7902 FORD       ANALYST         7566 03-DEC-81       4500                    20
      7369 SMITH      CLERK           7902 17-DEC-80       1200                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       2400        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1875        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1875       1400         30
      7788 SCOTT      ANALYST         7566 09-DEC-82       4500                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       2250          0         30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1650                    20
      7900 JAMES      CLERK           7698 03-DEC-81       1425                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1950                    10

14 rows selected.

SQL> commit
  2  /

Commit complete.

SQL> select a.ename,a.sal new_sal,b.sal old_sal from
  2  emp a, emp b as of timestamp sysdate-26-JUL-04
  3  /
emp a, emp b as of timestamp sysdate-26-JUL-04
             *
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> select sysdate from dual;

SYSDATE
---------
27-JUL-04

SQL> select a.ename, a.sal new_sal, b.sal old_sal
  2        from emp a, emp as of timestamp sysdate-26/JUL/04 b
  3       where a.empno = b.empno
  4  /
      from emp a, emp as of timestamp sysdate-26/JUL/04 b
                      *
ERROR at line 2:
ORA-00933: SQL command not properly ended


SQL>  

They are on 8.1.7. so even if they corrected the syntax , flashback would not work.

Hi Tom,

<quote>
Surprise -- flashback query has been enabled in the database for many many many years.
</quote>

Why contradiction?

Does Flashback Query survive a database bounce?

In 9iR2, I was pleasantly surprised to see that I can do

select * from complex_view as of timestamp sysdate - interval '5' hour and it just works!

How is this implemented? Does it see if it can find blocks to satisfy the query during the parse phase? Does it need only the minimum set of blocks that my query needs i.e. after applying all the filters and predicates in the query? Or would need the entire table to exist as of that time?

Can you shed some light on this? Thanks

This feature has always been in Oracle -- just exposed to you.

If you did this at 9am:

sql> variable x refcursor
sql> exec open :x for select * from a_50_billion_row_table;


it would happen "immediately" -- no io is performed, it just set the SCN for the query and set up the cursor area.  We didn't copy the data or anything like that.

Now, you go away for 5 hours and when you come back you:

sql> print x

the data will come out "as it existed at 9am".  That is read consistency, supported by our multi-versioning.  


The "as of timestamp" takes your date and within about 5 minutes (the timestamp is about +/- 2.5 minutes in 9i, +/- 3 seconds in 10g -- the SCN one is precise) finds the SCN that was current then and opens your query with THAT scn instead of the SCN as of right now.


You could also (at 9am)

SQL> set transaction read only;


go away for 5 hours -- and when you come back, every query you run will be "as of 5 hours ago"

(this has been true since about v4 -- all queries are done read consistent and as of a point in time)

We use the undo to reconstuct the blocks -- the concepts guide has a good chapter on this (and i cover it in Expert one on one Oracle).  It is THE difference between Oracle and the others in my opinion.  Perhaps the most important thing to learn about Oracle. 

I tried as of timestamp sysdate - interval '2' day and got

08180, 00000, "no snapshot found based on specified time"
// *Cause: Could not match the time to an SCN from the mapping table.
// *Action: try using a larger time.

I thought I would get a ORA-1555? What does the above error mean by "try using a larger time"?

Thanks

What is the name of that smon scn to time mapping table?

"by "larger" they meant one "greater than sysdate- 2 days""

That means the mapping table only has 1 days worths of data and I am asking for something older than that? So I guess the error mesasge is a little misleading, it should say "try using a more recent timestamp"

Can this flashback feature be used to rollback changes(DML and DDL ) in all tables in a schema? or the hot backup restore to a certain time is a better option?

Tom,

In this POST I noticed two places where you mention about old rollback segments...

<Quote>
with RBS's (old fashioned ones), it is a crap-shoot. Maybe you can flashback 1/2 hour -- maybe not.
</Quote>

and 

<Quote>
shows that using old fashioned RBS works as well
</Quote>

What I am confused about is,  in version 9.2.0.4 FLASHBACK-QUERY works OK with old style rollback segments???
If yes, then are there any limitations as compared to datbases using AUM???  I have RETENTION_PERIOD set to a value of 900. My other question is, that is FLASHBACK functionality enabled by default, when a new database is created, or it needs some setup?

here are the parameter setup,

SQL>  sho parameter undo

NAME                                 TYPE    VALUE
------------------------------------ ------- ------
undo_management                      string  MANUAL
undo_retention                       integer 900
undo_suppress_errors                 boolean FALSE
undo_tablespace                      string  null

Thanks for your help.

  

So let me try to paraphrase this, it is not gauranteed that you can recover the data for sure with MANUAL (as it is in AUM) but only if you are lucky and changes are still sitting in the same RBS(no other transaction has overridden those changes in the RBS), you can recover the changes...and all the undo_xxxx.. parameters are useless.

Thanks

So let me try to paraphrase this, it is not gauranteed that you can recover the data for sure with MANUAL (as it is in AUM) but only if you are lucky and changes are still sitting in the same RBS(no other transaction has overridden those changes in the RBS), you can recover the changes...and all the undo_xxxx.. parameters are useless with the MANUAL rollbacks.

Thanks

Hi Tom
I am working on database version 9.2.0.3.

I am trying to write test cases where I run
a procedure which updates a bunch of tables.
Is it possible for me to do the following:
1. Run the procedure and commit changes
2. Get the relevant data of the table before the procedure ran (i.e. of the data before this transaction)

This can not be done on the basis of timestamp since
the procedure may be too fast.
This way I can compare the before and after images
to find out exactly which columns were changed
for each table.

One workaround could be to capture the before
and after images in a different table of course
but if we can do it with flashback, it would
be neat.

Thank you.

May be I can use "as of SCN" for resolving the above
issue...

That is what I did :)

Well, I think flashback can be used to automate tests
or to have a framework where you dont have to re-invent
the wheel.
Most of our code is about changing table data by running some code. We can compare the before and after images of our code changes. This can also be automated of course. The process would be:
1. For a use case, create data in tables
2. Get SCN before you run your test case (before_scn)
3. Run your code
4. Get SCN after your run (after_scn)
5. Compare all affected table records for before_scn and after_scn.

You can also do this for your earlier version of code and the new version of code to see the differences. Automating this also would be a natural step.

I am curious if people use flashback in this way. What do you guys (Tom and others) think?

Tom,

Flash back query is giving only COMMITTED/Rollbacked data ,even from the same session.

Why "from t1 as of timestamp sysdate" is different from "from t1", if we don't commit/rollback our transaction?

eg.
create table t1 ( X int)
DevDB> select * from t1 ;

X
----------
0
1
2
4
34

Elapsed: 00:00:00.08
DevDB> select * from t1 as of timestamp sysdate;

X
----------
0
1
2
4
34

Elapsed: 00:00:00.08
DevDB> delete from t1;

5 rows deleted.

Elapsed: 00:00:00.04
DevDB> select * from t1 ;

no rows selected

Elapsed: 00:00:00.04
DevDB> select * from t1 as of timestamp sysdate;

X
----------
0
1
2
4
34

Elapsed: 00:00:00.08
DevDB> commit;

Commit complete.

Elapsed: 00:00:00.04
DevDB> select * from t1 ;

no rows selected

Elapsed: 00:00:00.04
DevDB>
DevDB> select * from t1 as of timestamp sysdate;

no rows selected

Elapsed: 00:00:00.04
DevDB>

Version is 10g.

Thanks for the clarification.

Dear Tom,

I had try through below way but i can't get my data back.

scott@NIKUNJ> select * from test;

no rows selected

scott@NIKUNJ> insert into test select * from tab;

5 rows created.

scott@NIKUNJ> insert into test select * from tab;

5 rows created.

scott@NIKUNJ> insert into test select * from tab;

5 rows created.

scott@NIKUNJ> delete from tab;
delete from tab
*
ERROR at line 1:
ORA-01031: insufficient privileges


scott@NIKUNJ> delete from test;

15 rows deleted.

scott@NIKUNJ> commit;

Commit complete.

scott@NIKUNJ> select * from test as of timestamp SYSDATE;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-5/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-5/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-10/24/60

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-11/24/60

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-2/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-1/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-5/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-15/24/60

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-20/24/60

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-25/24/60

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-30/24/60

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE

scott@NIKUNJ> select * from test as of timestamp sysdate-5/24/60;

no rows selected

scott@NIKUNJ> select * from test as of timestamp sysdate-5/24/60;

no rows selected.

Why ?
which timestamp i need to define if i realised after firing delete from test;
commit;
immediatly.

Regards,

Hi Tom,


Is flashback is dependent upon multi-versioning.

I think it is [ Please correct me if I am wrong ] ..
As per my knowldge no other database DB2,Teradata,Informix,MS SQl Server,Sybase ... is supporting this as of now ...

So, they can not have this functionality ... right ?

Thanks for your response ..

Is there any other database supporting flashback ?

I was testing Flashback Versions Query in 10gR2 and found it returns very old records besides those I specified in the VERSIONS range. For example on a table which we haven't changed any data for more than a month, flashback query with versions in the past 5 minutes would return all rows back. The following is just the first 10 rows. 

  1  select versions_startscn, versions_endscn,
  2   versions_operation,
  3   effort_id, created_dt, updated_dt
  4  from effort versions between timestamp
  5   systimestamp - interval '5' minute and systimestamp
  6* where rownum < 11
SQL> /

VERSIONS_STARTSCN VERSIONS_ENDSCN V  EFFORT_ID CREATED_DT     UPDATED_DT
----------------- --------------- - ---------- -------------- --------------
                                           201 20060607 15:20 20060607 15:20
                                           202 20060607 15:20 20060607 15:20
                                           203 20060607 15:20 20060607 15:20
                                           204 20060607 15:20 20060607 15:20
                                           205 20060607 15:20 20060607 15:20
                                           206 20060607 15:20 20060607 15:20
                                           207 20060607 15:20 20060607 15:20
                                           208 20060607 15:20 20060607 15:20
                                           209 20060607 15:20 20060607 15:20
                                           210 20060607 15:20 20060607 15:20

10 rows selected.

You can see all their Versions_% peudo columns are null, which makes sense for records whose undo info is gone. I can easily work around it (specifying "where versions_startscn is not null") but this is quite annoying. My question is is this a known issue to Oracle or just I mis-understood something here?

Thanks 

Very userful thread I was trying to find out whether I can use flashback for my integration application and I found the information in the thread very useful.

But I have query on how gaurantee a retention period in 10g , is it a combination of setting the retention parameter, plus the space to undo tablespace using some algorithm?

How does it account for some mass updates on a odd day ?

Will it throw some error messages when the DB has run out of physical space or will it just start reusing the undo extents silently.

Does the maximum limit of 5 days still apply in 10g and if yes why is the maximum limit for undo_retention 2 raise to the power 32?

Hi Tom

When inserting and quickly deleting a row, FBQ seems unable to see the changes.

Why is this? Is there any way to make the flashback query more reliable?

I have a test script taking a sleep time parameter:

DROP TABLE x;

CREATE TABLE x ( x VARCHAR2(22) );

INSERT INTO x VALUES ( 'aa' );
COMMIT;
host sleep &1

DELETE FROM x;
COMMIT;
host sleep &1

INSERT INTO x VALUES ( 'bb' );
COMMIT;
host sleep &1

SELECT TO_CHAR(versions_starttime,'YYYY/MM/DD HH24:MI:SS') AS start_tm,
versions_startscn AS scn,
versions_operation AS op,
x.*
FROM x versions BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
ORDER BY versions_starttime;



With a sleep time of about 6 or more seconds or so I get expected output:
START_TM SCN O X
--------------------------------------------------------------------------- ---------- - ----------------------
2009/05/27 08:33:58 5046724723 D aa
2009/05/27 08:34:07 5046724793 I bb
aa

With a shorter sleep time it seems unable to pick up the deleted row:
START_TM SCN O X
--------------------------------------------------------------------------- ---------- - ----------------------
bb
> show parameter undo
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

This was tested using version 10.2.0.1 and on 10.2.0.3.

Thanks

ops$tkyte%ORA10GR2> desc t
 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 DATA                                          VARCHAR2(80)

ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2> delete from t;

0 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column s new_val START
ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number S from dual;

         S
----------
  84586136

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  insert into t values ( 'inserted ' || i );
  5                  commit;
  6                  delete from t;
  7                  commit;
  8          end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column s new_val STOP
ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number S from dual;

         S
----------
  84586169

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select data,
  2         versions_operation,
  3         versions_starttime,
  4         versions_endtime   from t versions between scn &START and &STOP
  5   where data like 'inserted %'
  6   order by versions_startscn nulls first
  7  /
old   4:        versions_endtime   from t versions between scn &START and &STOP
new   4:        versions_endtime   from t versions between scn   84586136 and   84586169

DATA                 V VERSIONS_STARTTIME             VERSIONS_ENDTIME
-------------------- - ------------------------------ ------------------------------
inserted 1           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 1           D 27-MAY-09 10.09.12 AM
inserted 2           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 2           D 27-MAY-09 10.09.12 AM
inserted 3           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 3           D 27-MAY-09 10.09.12 AM
inserted 4           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 4           D 27-MAY-09 10.09.12 AM
inserted 5           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 5           D 27-MAY-09 10.09.12 AM
inserted 6           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 6           D 27-MAY-09 10.09.12 AM
inserted 7           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 7           D 27-MAY-09 10.09.12 AM
inserted 8           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 8           D 27-MAY-09 10.09.12 AM
inserted 9           I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 9           D 27-MAY-09 10.09.12 AM
inserted 10          I 27-MAY-09 10.09.12 AM          27-MAY-09 10.09.12 AM
inserted 10          D 27-MAY-09 10.09.12 AM

20 rows selected.

Hi Tom,

Thanks for your patiency in clarifying our doubts.
We are facing performance issue when executing the below query built using flashback. Can you please help in finding a optimized solution to fine tune this flashback query. so that we can use this feature.

SELECT CREATE_DT, LISTING_KEY FROM DIRECTORY_LISTING
AS OF TIMESTAMP TO_TIMESTAMP ('02-03-11 00:00:01', 'DD-MM-YY HH24:MI:SS') WHERE LISTING_KEY IN
((SELECT LISTING_KEY from directory_listing
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('27-02-11 00:00:01', 'DD-MM-YY HH24:MI:SS')
AND TO_TIMESTAMP('02-03-11 00:00:01', 'DD-MM-YY HH24:MI:SS')
WHERE LISTING_STATUS_CD = 'A' AND LAST_UPDATE_DT BETWEEN TO_DATE('27-02-11 00:00:01', 'DD-MM-YY HH24:MI:SS')
AND TO_DATE ('02-03-11 00:00:01', 'DD-MM-YY HH24:MI:SS')))

the query time is around 110 Seconds for this.

If the flashback isn't enabled and the retention time for undo has aleady passed in case of procedure drop/change.

Is there a way to get back the changed/dropped procedure.

Hi Tom,

I think I've misunderstood the use of undo_retention for flashback as discussed above. On my 11.2.0.3 database undo_retention is set to 900 (the default) but I can query data from up to 5 days ago. What governs how far you can flashback?

Thanks

Hi Tom, hope life is going well for you :). I have spent the last couple of hours reading through all of the posts related to SCNs and Flashback Query and I appreciate the Ask Tom website for educating me on these subjects. Unfortunately it did not leave me completely comfortable that my requirement can be met without exposure to possible incorrect data retrieval from the Flashback Query supplied by Oracle (11.2.0.3).
The requirement appears to be simple and I would think that this would be a primary usage of the Flashback Query:
We have an applications table with typical child tables like contacts, addresses, … The applications go through a flow of status changes (initial, active, pending, complete,…) We will have all of the tables under flashback archive. Below is a script to demonstrate how we thought this would work:
Crete table put under flashback and add a record:
create table applications_fb
( id number
, status varchar2(25)
, status_timestamp timestamp(6)
, constraint applications_fb_pk
primary key
( id )
enable validate
);
Table created.
alter table applications_fb flashback archive fb_arch;
Table altered.
insert
into applications_fb
( id
, status
, status_timestamp
)
values( 1
, ‘Initial’
, systimestamp
);
commit;

1 row created.
Commit complete.
Show data from table and flashback table:
select * from applications_fb;
ID STATUS STATUS_TIMESTAMP
1 Initial 1/23/2014 3:59:39.987184 PM

select applications_fb.status
, applications_fb.status_timestamp
, versions_operation
, versions_startscn
, versions_starttime
, versions_endscn
, versions_endtime
from applications_fb
versions between scn minvalue and maxvalue
order
by versions_startscn;

STATUS STATUS_TIMESTAMP VERSIONS_OPERATION VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME
Initial 1/23/2014 3:59:39.987184 PM I 10602156766721 1/23/2014 3:59:38.000000000 PM

Create child table add data and show data from table and flashback:
create table application_contacts_fb
( application_id number
, contact_id number
, time_stamp timestamp(6)
, constraint application_contacts_fb_pk
primary key
( application_id
, contact_id
)
enable validate
);
Table created.
alter table application_contacts_fb flashback archive fb_arch;
Table altered.
insert
into application_contacts_fb
values( 1
, 102
, systimestamp
);
commit;
1 row created.
Commit complete.
select * from application_contacts_fb;
APPLICATION_ID CONTACT_ID TIME_STAMP
1 102 1/23/2014 4:07:12.638690 PM
select application_contacts_fb.application_id
, application_contacts_fb.contact_id
, versions_operation
, versions_startscn
, versions_starttime
, versions_endscn
, versions_endtime
from application_contacts_fb
versions between scn minvalue and maxvalue
order
by versions_startscn;
APPLICATION_ID CONTACT_ID VERSIONS_OPERATION VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME
1 102 I 10602156970347 1/23/2014 4:07:12 PM

Update status of application 1 to Active, wait 10 seconds and then update it to Pending.
update applications_fb
set status = 'Active'
, status_timestamp = systimestamp
where id = 1;
commit;
execute dbms_lock.sleep(10);
update applications_fb
set status = 'Pending'
, status_timestamp = systimestamp
where id = 1;
update application_contacts_fb
set contact_id = 3
, time_stamp = systimestamp
where application_id = 1;
commit;

1 row updated.
Commit complete.
PL/SQL procedure successfully completed.
1 row updated.
1 row updated.
Commit complete.
Show results:

select * from applications_fb;

ID STATUS STATUS_TIMESTAMP
1 Pending 1/23/2014 4:09:40.812000 PM


select applications_fb.status
, applications_fb.status_timestamp
, versions_operation
, versions_startscn
, versions_starttime
, versions_endscn
, versions_endtime
from applications_fb
versions between scn minvalue and maxvalue
order
by versions_startscn;

STATUS STATUS_TIMESTAMP VERSIONS_OPERATION VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME
Initial 1/23/2014 3:59:39.987184 PM I 10602156766721 1/23/2014 3:59:38.000000000 PM 10602157023623 1/23/2014 4:09:27.000000000 PM
Active 1/23/2014 4:09:30.165902 PM U 10602157023623 1/23/2014 4:09:27.000000000 PM 10602157023630 1/23/2014 4:09:39.000000000 PM
Pending 1/23/2014 4:09:40.812000 PM U 10602157023630 1/23/2014 4:09:39.000000000 PM

This is where the application will ask “What did my application table and all of the child table records look like at the time I saved the status as Pending”. The flashback query uses the timestamp from the applications table that was saved with the status “Pending”. As you can see the query brought back unexpected results. We got the Active record when using the Pending status timestamp and flashback. ??
select applications_fb.id as app_id
, applications_fb.status as app_status
, applications_fb.status_timestamp as app_ts
, application_contacts_fb.application_id as ac_app_id
, application_contacts_fb.contact_id as ac_contact_id
, application_contacts_fb.time_stamp as ac_time_stamp
, application_contacts_fb.*
from applications_fb as of timestamp to_timestamp('1/23/2014 4:09:40.812000 PM', 'mm/dd/yyyy HH:MI:SS:FF PM')
, application_contacts_fb as of timestamp to_timestamp('1/23/2014 4:09:40.812000 PM', 'mm/dd/yyyy HH:MI:SS:FF PM')
where application_contacts_fb.application_id = applications_fb.id;

APP_ID APP_STATUS APP_TS AC_APP_ID AC_CONTACT_ID AC_TIME_STAMP APPLICATION_ID CONTACT_ID TIME_STAMP
1 Active 1/23/2014 4:09:30.165902 PM 1 102 1/23/2014 4:07:12.638690 PM 1 102 1/23/2014 4:07:12.638690 PM

A second test:
update application_contacts_fb
set contact_id = 103
where application_id = 1;
commit;

1 row updated.
Commit complete.

select * from application_contacts_fb;

APPLICATION_ID CONTACT_ID TIME_STAMP
1 103 1/23/2014 4:09:40.831592 PM


update applications_fb
set status = 'Complete'
, status_timestamp = systimestamp
where id = 1;
commit;

select * from applications_fb;

ID STATUS STATUS_TIMESTAMP
1 Complete 1/23/2014 4:24:10.172672 PM

select applications_fb.id as app_id
, applications_fb.status as app_status
, applications_fb.status_timestamp as app_ts
, application_contacts_fb.application_id as ac_app_id
, application_contacts_fb.contact_id as ac_contact_id
, application_contacts_fb.time_stamp as ac_time_stamp
, application_contacts_fb.*
from applications_fb as of timestamp to_timestamp('1/23/2014 4:24:10.172672 PM', 'mm/dd/yyyy HH:MI:SS:FF PM')
, application_contacts_fb as of timestamp to_timestamp('1/23/2014 4:24:10.172672 PM', 'mm/dd/yyyy HH:MI:SS:FF PM')
where application_contacts_fb.application_id = applications_fb.id;

Updated the status to complete ran the Flashback query for the status complete timestamp from the applications table and got back the results that we expected.
APP_ID APP_STATUS APP_TS AC_APP_ID AC_CONTACT_ID AC_TIME_STAMP APPLICATION_ID CONTACT_ID TIME_STAMP
1 Complete 1/23/2014 4:24:10.172672 PM 1 103 1/23/2014 4:09:40.831592 PM 1 103 1/23/2014 4:09:40.831592 PM

Clearly I don’t understand how to utilize the flashback table to fulfill this requirement. My questions are:
1. How can I use flashback to ensure I get back the correct data records that were effective at the time of the application status/timestamp change?
2. Given the +/- 3 second delay is it possible that my query could bring back an incorrect child record that got saved during the 3 second time period? In other words could another SCN be closer to the status table timestamp due to the +/- 3 seconds for a change that may have occurred in one of the related child tables?
3. The oracle documentation “General Guidelines for Oracle Flashback Technology” says: To query past data at a precise time, use an SCN. If you use a timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Oracle Database uses SCNs internally and maps them to timestamps at a granularity of 3 seconds. How can I use SCN if there is no documented way to grab and save the SCN that matches my status change commit transaction?
Greatly appreciate your help since we have a lot riding on this solution.
Sorry for the length of the post.

I am trying to introduce Flashback to the whole enterprise but at this time we are only trying to use it for 1 application 1 schema. Yet since we have a clustered database Oracle wants us to pay almost a million dollars. Is there no way to specify the Flashback for one schema? Management will force me to write custom code before they pay this amount of money. We are on 11.2.0.3.0 and can not upgrade to 11.2.0.4.0 for the free Flashback since that release has many documented issues for our shareplex.

Any suggestions would be appreciated.

Looks like we may proceed with the 11.2.0.4.0 upgrade to the cluster and apply patches to the shareplex product.

When the optimizer builds an execution plan for a flashback query, what stats does it use? (Are there historical stats available, or does it use the current stats and assume the tables are roughly the same now as for the time when the query is executed)?

Hi,

Was wondering that it is possible to use flashback query with "as of timestamp between tmpstmpval1 and tmpstmpval2?

Could not find it anywhere so I suppose it is not supported (tried it as well on our 19c DB and got an error as expected.

One approach I could think of was:

Select col from tab as of timestamp systimestamp-2/14
MINUS
Select col from tab as of timestamp systimestamp-1/14.

Your thoughts?

Thank you :o)

select ... from ...  versions between timestamp ts1 and ts2

Hi Chris,

Thank you for the reply.

But not "version" as it will give what were the operations done on the table between a window.

I guess it is not possible for now.

Thanks for your help though :)

Cheers!