Search This Blog

Total Pageviews

Monday, 21 February 2022

Oracle Find archive from SCN number


Oracle Find archive from SCN number 



alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
set pages 300 lines 300
col name for a90
col first_change# for 9999999999
col next_change# for 9999999999
select name, thread#, sequence#, first_time, next_time, first_change#, next_change#, decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status from v$archived_log
where 1=1
--and &SCN_no between first_change# and next_change#
and first_time > sysdate -3
;


NAME                                                                                          THREAD#  SEQUENCE# FIRST_TIME           NEXT_TIME            FIRST_CHANGE# NEXT_CHANGE# ARC_STATUS
------------------------------------------------------------------------------------------ ---------- ---------- -------------------- -------------------- ------------- ------------ -----------
                                                                                                    1       1285 04-MAR-2021 18:00:53 04-MAR-2021 23:47:23    1598584462   1598757896 Deleted
                                                                                                    2       1654 04-MAR-2021 19:03:02 05-MAR-2021 00:09:54    1598599944   1598785468 Deleted
                                                                                                    1       1286 04-MAR-2021 23:47:23 05-MAR-2021 01:00:03    1598757896   1598833446 Deleted
                                                                                                    2       1655 05-MAR-2021 00:09:54 05-MAR-2021 01:00:04    1598785468   1598833469 Deleted
                                                                                                    1       1287 05-MAR-2021 01:00:03 05-MAR-2021 18:00:02    1598833446   1599094323 Deleted


col name for a90
set linesize 300 pagesize 300 
select thread#,sequence#, name ,creator
	-- , to_char(first_time,'DD-MON HH24:MI')
	, to_char(completion_time,'DD-MON HH24:MI') arc_completion,decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status
    from v$archived_log
    where 1=1  
	-- and first_time > sysdate -3
	-- and CREATOR!='LGWR'
	--and SEQUENCE#>593460
	--and THREAD# =2
  order by 5
 -- FETCH FIRST 10 ROWS ONLY
  ;

No comments:

Oracle DBA

anuj blog Archive