Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Cameron.

Asked: October 03, 2024 - 7:27 pm UTC

Last updated: October 09, 2024 - 1:59 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I'm running this query

select * from DOC_MANAGEMENT.project AS OF timestamp to_timestamp('20241002','YYYYMMDD') 
where project_id = 179121; 


And I get this error
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_2808613384$" too small


However it work when I run it with today's date

select * from DOC_MANAGEMENT.project AS OF timestamp to_timestamp('20241003','YYYYMMDD') 
where project_id = 179121; 


*Please don't go into what version I am currently running. I realize it a older version I'm a newly hired DBA of 7 weeks and will upgrade the DB but that is not going to help me now with this issue.

and Chris said...

Flashback (as of) queries need to reconstruct how the data looked at that time in the past.

They do this by looking back through the undo. These days this would be in the undo tablespace; your database is using rollback segments instead for this.

If that undo information is unavailable, then the database can't restore the past state.

This is the problem you're hitting. The information to know how the data looked yesterday is gone!

The database will keep undo to go back as far as in seconds as the UNDO_RETENTION is set to. Flashback queries looking for a date older than this have no guarantee the information is available.

To allow you to query older data, increase the value of this parameter (assuming it's available in your version) and ensure your rollback segments are large enough to support this much undo.

Rating

  (1 rating)

Comments

FBDA

Rajeshwaran, Jeyabal, October 07, 2024 - 8:29 am UTC

....
To allow you to query older data, increase the value of this parameter (assuming it's available in your version) and ensure your rollback segments are large enough to support this much undo.
....


or perhaps, you need to use Flashback data archive features

Chris Saxon
October 09, 2024 - 1:59 pm UTC

Perhaps - states they're running an ancient version their so options may be limited.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.