It may result into a question like, how to of flush undo but here is my situation.
I am unable to make the undo segments as expired event after setting undo_retention to 10-60 seconds.
Here is my output of undo segments group by owner and it's status
OWNER STATUS SUM(BYTES)/1024/1024
SYS ACTIVE 286.25
SYS EXPIRED 0.5625
SYS UNEXPIRED 31447.5
I see no other user, and I'm unable to make it expired even after trying with 0-1000 seconds. It still persists as 30GB. This causing many queries specially delete, truncate Qs to run forever.
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
SID USERNAME XIDUSN USED_UREC USED_UBLK
505 user1 12 1 1
387 user2 22 1 1
464 user3 3 18000 212
379 user4 4 713687 31428
Any possible solution. I don't see any other thread as useful. Forgive me if this is a duplicate.