From time to time you might need to find out where a particular transaction is in the redo logs. Most of the time if you want to look for an old transaction you will need to look into the archived logs history. This script will give you a good understanding of where the SCN for a particular transaction is.
--------------------------------------------------------------------------------
--
-- Name: obtain_archlog_from_snc.sql
-- Purpose: Find which archlog file a specific SNC is written
--
-- Author: Alex Lima
--
--
--------------------------------------------------------------------------------
set pages 100 lines 100
col name for a70
col first_change# for 9999999999
col next_change# for 9999999999
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
select name,
thread#,
sequence#,
status,
first_time,
next_time,
first_change#,
next_change#
from gv$archived_log
where &SCN between first_change# and next_change#;
alexlima.com 