aspose file tools*
The moose likes Oracle/OAS and the fly likes Oracle flashback query is not working as expected. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle flashback query is not working as expected." Watch "Oracle flashback query is not working as expected." New topic
Author

Oracle flashback query is not working as expected.

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 249

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...


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Ranch Hand

Joined: Mar 28, 2012
Posts: 249

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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:
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Just out of curiosity - did you get the flashback query working?
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 249

Yes commit statement was the problem , after commiting the insert statements it works.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle flashback query is not working as expected.