/*
Program     : optstat_matrix_header.sql
Purpose     : 
Author      : Daniel W. Fink OptimalDBA.com
Created     : July 17, 2007
Update      : 
Parameters  : v_days, v_report_title, v_comment, v_spoolcomment must be defined before calling
Exit Codes  : Not Used
Comments    : 
Disclaimer  : No warranty is provided for any use of the script, statements or logic included. 
              This script, statements and logic are for personal use only and may not be included 
              as part of a commercial product. 
              Please address any comments to scripts@optimaldba.com
              This comment block and all lines above must be included.
*/


COLUMN  c_begin_snap    NEW_VALUE  v_begin_snap     NOPRINT
COLUMN  c_end_snap      NEW_VALUE  v_end_snap       NOPRINT
COLUMN  c_begin_date_f  NEW_VALUE  v_begin_date_f   NOPRINT FORMAT A11
COLUMN  c_end_date_f    NEW_VALUE  v_end_date_f     NOPRINT FORMAT A11
COLUMN  c_db_name       NEW_VALUE  v_db_name        NOPRINT FORMAT A20
COLUMN  c_instance_name NEW_VALUE  v_instance_name  NOPRINT FORMAT A20
COLUMN  c_spool_name    NEW_VALUE  v_spool_name     NOPRINT

SET ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF

SELECT     MIN(sn.snap_id) c_begin_snap,
           TO_CHAR(TRUNC(sn.snap_time),'MM/DD/YYYY') c_begin_date_f
FROM       stats$snapshot sn
WHERE      TRUNC(sn.snap_time) = TRUNC(sysdate - &p_days)
GROUP BY TRUNC(sn.snap_time)
/

SELECT     MAX(sn.snap_id) c_end_snap,
           TO_CHAR(TRUNC(sn.snap_time),'MM/DD/YYYY') c_end_date_f
FROM       stats$snapshot sn
WHERE      TRUNC(sn.snap_time) = TRUNC(sysdate - 1)
GROUP BY TRUNC(sn.snap_time)
/

SELECT   sdi.db_name c_db_name, 
         sdi.instance_name c_instance_name
FROM     stats$database_instance sdi
WHERE    sdi.snap_id = (SELECT   MAX(sd1.snap_id) 
                        FROM     stats$database_instance sd1 
                       )
/

SELECT     REPLACE('&&v_spool_title',' ')||'_'||'&&v_db_name'||'_'
           ||TRIM('&&v_begin_snap')||'_'||TRIM('&&v_end_snap')||'.lst' c_spool_name
FROM       dual
/

TTITLE CENTER  &v_report_title                                     SKIP 1 -
       LEFT   'Database   '&&v_db_name                                    -
       CENTER 'Snap Range '&&v_begin_snap' To '&&v_end_snap               -
       RIGHT  'Instance '&&v_instance_name                         SKIP 1 -
       CENTER 'Time Range '&&v_begin_date_f' To '&&v_end_date_f    SKIP 1 -
       LEFT    &v_comment                                          SKIP 2