Database Backup and Recovery (MOSC)

MOSC Banner

Enough Tablespace storage for undo retention but getting ORA-01555 when using flashback query.

Oracle Database 19.13

Unsure what I'm missing but Automatic Undo Management is enabled and it seems like there is plenty of space but with the undo retention set to 10 days we can't flashback query more than 50 minutes.


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

ROUND((d.undo_size / (to_number(f.value) *

g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"

FROM (

SELECT SUM(a.bytes) undo_size

FROM v$datafile a,

v$tablespace b,

dba_tablespaces c

WHERE c.contents = 'UNDO'

AND c.status = 'ONLINE'

AND b.name = c.tablespace_name

AND a.ts# = b.ts#

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center