Oracle flashback query is not working as expected.

[Mahtab Alam]

I am preparing for oracle sql expert exam.
and reading about "Track the changes to data over a period of time" and get across this great feature of seeing the table contents in a previous time.

So i created a table
inserted some rows in the table
executed sleep function for two minutes
and then i deleted all the rows from the table and also executed a commit statement.

now when i select from the table , i get no results(as expected since I have previously deleted all the rows from the table).

now I use flashback query ( to see the contents of table before 1 and half minutes , when table actually have some rows)

select *
from table_name
as of timestamp systimestamp - interval '0 0:01:30' day to second;

This query should have shown all the rows the table have( 1 and half minutes before) before the delete statement is executed , but it results in no rows selected.

Can somebody suggest whats the problem.

Shukran...

[Martin Vashko]

I understand that you haven't committed between inserting and deleting the rows. If so, this is why it doesn't work - flashback queries only see committed changes to the database, never uncommitted changes, even if they were made (and then undone) in the same session.

[Mahtab Alam]

No the problem is archive mode.
I saw a video on flashback , .
Can you tell me how to enable archive mod in oracle xe 10.0.0.something.

[Martin Vashko]

No. Archive mode concerns redo logs, flashback doesn't operate over redo logs.

There can be a flashback data archive (sometimes called Total Recall), but even if there isn't, flashback can always use undo. Besides, when the data needed for the flashback is no longer in the undo, an error occurs. Otherwise you couldn't distinguish a table that was empty two minutes ago from situation where there are no data for the table at two minutes ago.

I just did a quick test that shows that a missing commit is a problem:SQL*Plus: Release 11.2.0.1.0 Production on ╚t Led 16 16:26:05 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test(a number); Table created. SQL> insert into test values(1); 1 row created. SQL> commit; Commit complete. SQL> insert into test values(2); 1 row created. [waited for two minutes] SQL> select * from test; A ---------- 1 2 SQL> select * from test as of timestamp systimestamp - interval '0 0:01:30' day to second; A ---------- 1 SQL> delete from test; 2 rows deleted. SQL> commit; Commit complete. SQL> select * from test; no rows selected SQL> select * from test as of timestamp systimestamp - interval '0 0:00:30' day to second; A ---------- 1 SQL>

[Martin Vashko]

Just out of curiosity - did you get the flashback query working?

[Mahtab Alam]

Yes commit statement was the problem , after commiting the insert statements it works.