Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle flashback query is not working as expected.

 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just out of curiosity - did you get the flashback query working?
 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes commit statement was the problem , after commiting the insert statements it works.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic