/*
 * Title  : sho_arc_list.sql
 * Author : EXADIME LLC
 * Created: 09-Apr-2026
 * Updated: 09-Apr-2026
 * Release: 1.0.0
 * Purpose: Shows Archivelog details between the given sequence numbers.
 * Comment: None
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 200
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

column sno			format 9999			heading "#"
column thread			format 9999			heading "Thread"
column seq			format 99999999			heading "Sequence"
column scn	 		format 99999999999999		heading "SCN"
column time			format a15			heading "Time"
column name			format a80			heading "Name"
column applied			format a10			heading "Applied?"
column deleted			format a10			heading "Deleted?"

accept p_st_log	prompt "Enter starting log sequence: "
accept p_sp_log	prompt "Enter ending log sequence: "
accept p_thread	prompt "Enter thread# (Default 1): "	default 1
accept p_delete char prompt "Include deleted? (Enter YES|[NO]): " default "NO"

spool &&rpt_dir/sho_arc_list.rpt

select
	rownum						sno,
	thread#						thread,                  
	sequence#					seq,
	first_change#					scn, 
	to_char(first_time,'yyyymmdd hh24:mi')		time,
	name						name,
	applied						applied,                       
	deleted						deleted					
from 
	gv$archived_log
where
	sequence# >= &&p_st_log
and	sequence# <= &&p_sp_log
and	thread# = &&p_thread
and	(deleted like  upper('%&&p_delete%'))
/

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
