REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_log_hist_8.sql
REM
REM <SQLDIR_GRP>LOG</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script will show history of threads for database
REM	ORACLE 8 script
REM	Use s_log_hist_7.sql for ORACLE 7
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_log_hist_8.sql
REM 
REM Example:
REM	s_log_hist_8.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

--alter session set nls_date_format='MM_YYYY';
accept 1 prompt "Enter Start Date [format MM-DD-YYYY]: "
accept 2 prompt "Enter days of history [1-31]: "

set pages 999
set verify off
break on date_sub skip 1
col number_of_threads heading 'Number Of|Threads'
col thread# heading 'Thread|Number'
col date_t format a20 heading 'Time Frame'
col date_sub heading 'Date'
compute sum of number_of_threads on date_sub



select          substr(to_char(first_time,'RRRR: Mon - DDth'),1,16) DATE_SUB
,               to_char(first_time,'Mon - DDth  HH24') DATE_T
,               count(*) NUMBER_OF_THREADS
,               thread#
FROM            V$LOGHIST
WHERE           TRUNC(first_time) BETWEEN TO_DATE(UPPER('&1'),'MM-DD-YYYY') AND TO_DATE(UPPER('&&1'),'MM-DD-YYYY')+&2
GROUP BY        substr(to_char(first_time,'RRRR: Mon - DDth'),1,16)
,               THREAD#
,               to_char(first_time,'Mon - DDth  HH24')
order by        substr(to_char(first_time,'RRRR: Mon - DDth'),1,16)
,               to_char(first_time,'Mon - DDth  HH24')
/