This functionality doesn't work for actions more than 5 days old. See Metalink note:361480.1:
Cause
This is expected behavior as the SCN must be no older than 5 days as part of the current flashback database
features.
Currently, the flashback query feature keeps track of times up to a
maximum of 5 days. This period reflects server uptime, not wall-clock
time. You must record the SCN yourself at the time of interest, such as
before doing a DELETE.
Discussion on:
View:
Show:
Thanks for the feedback Earl.
It's actually worse than that: Flashback can only go back as far as your UNDO_RETENTION parameter, which in most cases will be quite a bit shorter than the maximum of 5 days.
But that's just the example in Listing B, not the main point of the article. The main point is that ORA_ROWSCN pseudocolumn is ALWAYS available, regardless of the flashback settings. It is stored in the block. (The SELECT statements in Listing A, for example, don't use flashback at all.)
So, to find all rows that have changed since a certain SCN or timestamp, you can just directly select them.
BobW
It's actually worse than that: Flashback can only go back as far as your UNDO_RETENTION parameter, which in most cases will be quite a bit shorter than the maximum of 5 days.
But that's just the example in Listing B, not the main point of the article. The main point is that ORA_ROWSCN pseudocolumn is ALWAYS available, regardless of the flashback settings. It is stored in the block. (The SELECT statements in Listing A, for example, don't use flashback at all.)
So, to find all rows that have changed since a certain SCN or timestamp, you can just directly select them.
BobW
not bad, but if you could get the time stamp converstion for each row against the data each row that would be good.
currently, it does not really help for the number instead of the time stamp.
currently, it does not really help for the number instead of the time stamp.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
