#!/usr/bin/ksh 


SBINS=$SBIN/scripts
TMP=$SBIN/tmp


# .......................................................................................
do_it_by_sed()
{
#-------------------------------------------------------------------------------
# Alternated method if sys is not available
#-------------------------------------------------------------------------------
#-- Script:     instance_variables.sql
#-- Purpose:    to list the values of the instance variables
#--
#-- Copyright:  (c) 1998 Ixora Pty Ltd
#-- Author:     Steve Adams
#__ apapted to smenu by By. Polarski
#-------------------------------------------------------------------------------

TMP_FIL=$SBIN/tmp/get_var$$.txt
sqlplus -s "$CONNECT_STRING" <<EOF >/dev/null 2>&1
spool $TMP_FIL
select
  'X\$KVII' struct, kviitag  variable, kviival  value, kviidsc  description
from sys.x\$kvii
union all
select
  'X\$KVIT', kvittag, kvitval, kvitdsc
from sys.x\$kvit
union all
select
  'X\$KVIS', kvistag, kvisval, kvisdsc
from
  sys.x\$kvis
/
spool off ;
EOF
echo
OLD=TOTO
cat << EOF
MACHINE $HOST        - ORACLE_SID : $ORACLE_SID                            Page:   1

Date              -  `date +%A'  '%d'   '%B'   '%H:%M:%S`
Username          -  SYS
                  -  Show instance variables


STRUCT VARIABLE   VALUE    DESCRIPTION
------ -------- ---------- -----------------------------------------------------
EOF
sed -e '/selected\.$/d' -e '/^---.*--$/d' -e '/^STRUCT/d' $TMP_FIL | while read a b c d
   do
      a=`echo $a | awk '{ printf ("%-7.7s",$1)  }'`
      b=`echo $b | awk '{ printf ("%7.7s",  $1)  }'`
      c=`echo $c | awk '{ printf ("%10.10s",$1)  }'`
      if [ $a = $OLD ];then
         echo "       ${b}${c}   ${d}"
      else
         echo "${a}${b}${c}   ${d}"
      fi
      OLD=$a
done
echo

if [ -f $TMP_FIL ];then
   rm $TMP_FIL
fi
}
# .......................................................................................
function help {

  cat <<EOF
  
       sts -aw <nn> -val    : Show Os stats taken from AWR repository for the last <nn>  periods. Default is 1
                                   -val : gives results in values rather than percentages
       sts -l              : show system log mode
       sts -log            : show system supplemental logging. User 'tbl -log' for table supplemental logging
       sts -fl             : List flash logs
       sts -def            : Show default database properties
       sts -dif            : Show parameter differences
       sts -lim            : List resource limit
       sts -opt            : List most of relevants parameters about the optimizer
       sts -s              : List system statistics
       sts -si             : show When system statistics were taken
       sts -sl             : List gather_database_stats run history
       sts -rac            : Rac : list instances status
       sts -t  <SCN>       : Convert SCN to timestamp
       sts -use            : Show options with licences used
       sts -v              : Show instance variables
       sts -urc            : List no logging operations
       sts -bw [-rn <n>]   : Background process event
       sts -pwd            : Generate a script to preserve all users password
       sts -h             : this help
           
 Note:       -rn <ROWNUM>  : Limit display to <ROWNUM> first rows, default is 30
       
EOF
exit
}
# .......................................................................................
#                               Main
# .......................................................................................
if [ -z "$1" ];then
   help
fi

NSNAP=1
ROWNUM=30
while [ -n "$1" ]
do
  case "$1" in
    -bw ) CHOICE=BW ;;
     -l ) CHOICE=STS ;;
   -log ) CHOICE=SUP_LOG;;
    -fl ) CHOICE=FLASH_LOG;;
     -t ) CHOICE=CVT_SCN; SCN=$2;shift;;
   -rac ) CHOICE=RAC_LIST ;;
   -opt ) CHOICE=OPT_LIST ;;
   -dif ) CHOICE=DIFF ;;
   -def ) CHOICE=DEFAULT ;;
   -lim ) CHOICE=RESOURCE_LIMIT ;;
   -pwd ) CHOICE=PSWD ;;
     -s ) CHOICE=SYSSTAT ;;
    -si ) CHOICE=SYSSTAT_INFO ;;
    -sl ) CHOICE=GDS ;;
   -val ) VAL=TRUE;;
   -urc ) CHOICE=URC;;
     -v ) CHOICE=VAR;;
    -aw ) CHOICE=SAR;
          if [ -n "$2" ];then
             NSNAP=$2
             shift
          fi;;
  -use ) CHOICE=USE ;;
    -rn) ROWNUM=$2 ; shift ;;
     * ) help ;;
  esac
  shift
done

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get the password of $S_USER"
   if [ "$CHOICE" = "VAR" ];then
         do_it_by_sed
   fi
   exit 0
fi

if [ "$CHOICE" = "PSWD" ];then
   TMP=$SBIN/tmp
   FOUT=$TMP/alter_user_passwd_$ORACLE_SID.txt
> $FOUT
echo " Create user script " 
echo " =======================" 
(
sqlplus -s "$CONNECT_STRING" <<EOF
        set pages 0 feed off echo off pause off lines 250 trimspool on
        select 'create user ' ||username || ' identified by values '||''''|| password || '''' || ' default tablespace ' || 
               DEFAULT_TABLESPACE ||' temporary tablespace ' || TEMPORARY_TABLESPACE || ' ; ' from dba_users
/
EOF
) >> $FOUT
echo >> $FOUT
echo " Preserve user passwd for later use" 
echo " ==================================" 
(
sqlplus -s "$CONNECT_STRING" <<EOF
        set pages 0 feed off echo off pause off
        select 'alter user ' ||username || ' identified by values '||''''|| password || ''' ; ' from dba_users
        /
EOF
) >> $FOUT
cd $TMP
echo " "
cat $FOUT
echo " "
exit

elif [ "$CHOICE" = "BW" ];then
#---------------------------------------------------------------------------------
#  date   : 2005 Nov 15
#  Author : Donald K. Burleson  ( derived from a script of Steve Adams  )
#                               ( but DKB seems to have a short memory  )
#  adapted to smenu by By Bernard Polarski
#---------------------------------------------------------------------------------

       TITTLE="System Backround events"
SQL="set lines 190 pages 66
column c1 heading 'System|ID'              format 9999
column c2 heading 'Background|Process'     format a10 justify l
column c3 heading 'Event name'             format a40
column c4 heading 'Total|Waits'            format 999,999,999
column c5 heading 'Time|Waited|(in secs)'  format 999,999,999
column c6 heading 'Nbr |timouts'            format 9999999 justify c
column c7 heading 'Avg|Wait|secs'          format 99990.999
column c8 heading 'Max|Wait|(in secs)'     format 99999

break on c1 on report
select c1,c2,c3,c4,c6,c6,c7,c8 from (
select
   b.sid                                     c1,
   decode(b.username,NULL,c.name,b.username) c2,
   a.event                                   c3,
   a.total_waits                             c4,
   round((a.time_waited / 100),2)            c5,
   a.total_timeouts                          c6,
   round((average_wait / 100),3)             c7,
   round((a.max_wait / 100),2)               c8,
   rank() over ( partition by decode(b.username,NULL,c.name,b.username) order by a.total_waits desc) as topr
from
   sys.v\$session_event a,
   sys.v\$session       b,
   sys.v\$bgprocess     c
 where
   a.event NOT LIKE 'DFS%'
and
   a.event NOT LIKE 'KXFX%'
and
   a.sid = b.sid
and
   b.paddr = c.paddr
and
   a.event NOT IN
   (
   'lock element cleanup',
   'pmon timer',
   'rdbms ipc message',
   'smon timer',
   'SQL*Net message from client',
   'SQL*Net break/reset to client',
   'SQL*Net message to client',
   'SQL*Net more data to client',
   'dispatcher timer',
   'Null event',
   'io done',
   'parallel query dequeue wait',
   'parallel query idle wait - Slaves',
   'pipe get',
   'PL/SQL lock timer',
   'slave wait',
   'virtual circuit status',
   'WMON goes to sleep') $ORDER
) where topr <= $ROWNUM ;
"
elif [ "$CHOICE" = "URC" ];then

TITTLE="List No logging operations"
SQL="
set linesize 125
set head on

COL fName    FORMAT A55      HEADING 'Datafile'
COL tbs  FORMAT A30      HEADING 'Tablespace'
COL uc   FORMAT 999999999999      HEADING 'Scn'
COL fd   FORMAT A20      HEADING 'Date'

SELECT a.fNAME,  a.uc, TO_CHAR (a.fdate,'DD-MON-YYYY HH:MI:SS') fd , b.name tbs
 from (
      SELECT NAME fname, UNRECOVERABLE_CHANGE# uc, UNRECOVERABLE_TIME fdate, ts#
   FROM V\$DATAFILE  where UNRECOVERABLE_CHANGE# > 0 order by fdate desc) a,
   sys.ts\$ b where  a.ts# = b.ts# and rownum <=30
/
"
# .................................................................................
elif [ "$CHOICE" = "VAR" ];then
TITTLE="Show instance variables"
SQL="set lines 190 pages 65
column variable format a16
column description format a60
break on struct

select
  'X\$KVII' struct,
  kviitag  variable,
  kviidsc  description,
  kviival  value
from
  sys.x\$kvii
union all
select
  'X\$KVIT',
  kvittag,
  kvitdsc,
  kvitval
from
  sys.x\$kvit
union all
select
  'X\$KVIS',
  kvistag,
  kvisdsc,
  kvisval
from
  sys.x\$kvis
/
"
elif [ "$CHOICE" = "GDS" ];then
SQL="col operation format a30
col start_time for a22
col duration format a18
break on operation on report
set lines 124 pages 66
select operation,to_char(start_time,'YYYY-MM-DD HH24:MI:SS')start_time,
       (end_time-start_time) day(1) to second(0) as duration from dba_optstat_operations
order by start_time desc;"
elif [ "$CHOICE" = "SYSSTAT" ];then
SQL="
set lines 190 pages 66
set feed off
COL statistics_name         FORMAT A30      HEADING 'Statistics'
COL system_status           FORMAT A10      HEADING 'Status'
COL statistics_view_name    FORMAT A24      heading 'Corresponding view'
COL activation_level        FORMAT A10      heading 'Activation|Level'
COL description              FORMAT A73      heading 'Description'
select statistics_name,system_status, statistics_view_name , activation_level, description from v\$statistics_level ;
prompt 
set feed on
prompt System statistics setting
select pname, pval1  value from sys.aux_stats\$ where sname = 'SYSSTATS_MAIN';
"
elif [ "$CHOICE" = "STS" ];then
   sqlplus -s '/ as sysdba'  <<EOF
   set head off feed off
   prompt
   select 'FORCE_LOGGING                  ' ||force_logging from v\$database;
   archive log list;
   exit
EOF
   exit
elif [ "$CHOICE" = "USE" ];then
SQL="
col owner format a18
col name format a38
col version format a10
col detected_usages format 999999 head 'Detected|usages' justify c
col description format a85
select NAME, DETECTED_USAGES, to_char(LAST_USAGE_DATE,'DD-MM-YYYY HH24:MI:SS') last_usage,
     DESCRIPTION from SYS.DBA_FEATURE_USAGE_STATISTICS where DETECTED_USAGES > 0;"

elif [ "$CHOICE" = "SAR" ];then
   if  [ -z "$VAL" ];then
       PERC="/snap_len *100,2"
       TYPE=%
   else
       TYPE=s
   fi
SQL="
set feed off verify off lines 190 pages 66
col inst new_value inst noprint;
col id1 head 'Idle time($TYPE)' justify c
col usr1 head 'User time($TYPE)' justify c
col sys1 head 'Sys time($TYPE)' justify c
col io1 head 'Io Wait time($TYPE)' justify c
col nice1 head 'Nice time ($TYPE)' justify c
col snap_len head 'Interval| (Secs)' justify c
col num_cpus new_value p_num_cpus  head 'Number of CPU';
col a1 new_value secs noprint;
col SNAP_BEGIN format a20 head 'Snap begin' justify c
col SNAP_END format a20 head 'Snap end' justify c

   select instance_number inst from v\$instance;
   SELECT value num_cpus FROM v\$osstat WHERE stat_name = 'NUM_CPUS';
 prompt
 prompt Negatives values correspond to Shutdown:
 prompt
select  snap_id, snap_len, round(id1 $PERC) id1,  
                       round(usr1 $PERC) usr1,
                       round(sys1 $PERC) sys1, 
                       round(io1 $PERC) io1, 
                       round(nice1 $PERC) nice1 , snap_begin, snap_end 
from (
     select  snap_id,  id1, usr1,sys1, io1, nice1, snap_begin, snap_end , 
             round( extract( day from diffs) *24*60*60*60+
                    extract( hour from diffs) *60*60+
                    extract( minute from diffs )* 60 +
                    extract( second from diffs )) snap_len                  -- this is the exact length of the snapshot in seconds
     from ( select /*+ at this stage, each row show the cumulative value. 
                       r1    7500  8600
                       r2    7300  8300
                       r3    7200  8110
                    we use [max(row) - lag(row)] to have the difference between [row and row-1], to obtain differentials values:
                       r1    200   300
                       r2    100   190
                       r3    0       0
                    */
        a.snap_id,
        (max(id1)    - lag( max(id1))   over (order by a.snap_id))/100        id1 ,
        (max(usr1)   - lag( max(usr1))  over (order by a.snap_id))/100        usr1,
        ( max(sys1)  - lag( max(sys1))  over (order by a.snap_id))/100        sys1,
        ( max(io1)   - lag( max(io1))   over (order by a.snap_id))/100        io1,
        ( max(nice1) - lag( max(nice1)) over (order by a.snap_id))/100        nice1,
          max(to_char(BEGIN_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss'))          snap_begin,      -- for later display
          max(to_char(END_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss'))            snap_end,        -- for later display
        ( max(END_INTERVAL_TIME)-max(BEGIN_INTERVAL_TIME))                    diffs            -- exact len of snap used for percentage calculation
        from ( /*+  perform a pivot table so that the 5 values selected appears on one line.
                    The case, distibute col a.value among 5 new columns, but creates a row for each.
                    We will use the group by (snap_id) to condense the 5 rows into one.
                    if you don't see the utility, just remove the group by and max function, 
                    then re-add it and you will see the what  use is max() and here is what you will see:
        Raw data :   1000     2222
                     1000     3333
                     1000     4444
                     1000     5555
                     1000     6666       
        The SELECT CASE creates populate our inline view with structure: 
                       ID    IDLE    USER   SYS   IOWAIT   NICE
                     1000    2222
                     1000             3333
                     1000                   4444
                     1000                          5555
                     1000                                  6666
         the goup by(1000) condensate the rows in one:
                       ID    IDLE    USER   SYS   IOWAIT  NICE
                      1000   2222    3333   4444   5555   6666
               */
               select a.snap_id,
                  case b.STAT_NAME
                       when 'IDLE_TIME' then a.value / &p_num_cpus
                   end  id1,
                  case b.STAT_NAME
                       when 'USER_TIME' then a.value / &p_num_cpus
                  end usr1 ,
                  case b.STAT_NAME
                       when 'SYS_TIME' then  a.value / &p_num_cpus
                  end sys1 ,
                  case b.STAT_NAME
                       when 'IOWAIT_TIME' then  a.value / &p_num_cpus
                  end io1,
                  case b.STAT_NAME
                       when 'NICE_TIME' then  a.value / &p_num_cpus
                  end nice1
                  from  WRH\$_OSSTAT a,  WRH\$_OSSTAT_NAME b
                       where 
                            a.dbid      = b.dbid       and
                            a.STAT_ID   = b.stat_id    and  
                            instance_number = &inst    and
                            b.stat_name in ('IDLE_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME') and
                            a.snap_id > (( select max(snap_id) from  WRH\$_OSSTAT) - $NSNAP  -1 )
                   order by 1 desc
              ) a,  wrm\$_snapshot s
         where  a.snap_id = s.snap_id
         group by a.snap_id 
         order by snap_id desc 
        )
   )where rownum < ($NSNAP+1);
"

elif [ "$CHOICE" = "RESOURCE_LIMIT" ];then
SQL="set lines 190 pages 66
select * from v\$resource_limit ;
"
elif [ "$CHOICE" = "DEFAULT" ];then
SQL="col PROPERTY_VALUE format a40
 col DESCRIPTION format a40
select * from database_properties;
"
elif [ "$CHOICE" = "SYSSTAT_INFO" ];then
SQL="col pval1 for 9999 
col pval2 for a20
select pname, pval1, pval2 from sys.aux_stats\$ where sname  = 'SYSSTATS_INFO'; 
"
elif [ "$CHOICE" = "DIFF" ];then
SQL="SET LINESIZE 120
COLUMN name          FORMAT A30
COLUMN current_value FORMAT A30
COLUMN sid           FORMAT A8
COLUMN spfile_value  FORMAT A30

SELECT p.name, i.instance_name AS sid, p.value AS current_value, sp.sid, sp.value AS spfile_value      
FROM   v\$spparameter sp,
       v\$parameter p,
       v\$instance i
WHERE  sp.name   = p.name
AND    sp.value != p.value;
"
elif [ "$CHOICE" = "CVT_SCN" ];then
SQL=" SELECT SCN_TO_TIMESTAMP($SCN) from dual ; "
elif [ "$CHOICE" = "OPT_LIST" ];then
SQL="col name format a40
col value format a40
set lines 124 pagesize 66
SELECT name, value FROM v\$parameter 
WHERE name like 'optimizer%' 
OR name like 'parallel%' 
OR name in ('cursor_sharing',
'db_file_multiblock_read_count',
'hash_area_size', 
'hash_join_enabled',
'query_rewrite_enabled',
'query_rewrite_integrity',
'sort_area_size', 
'star_transformation_enabled',
'bitmap_merge_area_size', 
'partition_view_enabled') 
ORDER BY name; 
"
elif [ "$CHOICE" = "RAC_LIST" ];then
#-- +----------------------------------------------------------------------------+
#-- |         Jeffrey M. Hunter  : jhunter@idevelopment.info                     |
#-- | PURPOSE  : Provide a summary report of all configured instances for the    |
#-- |            current clustered database.                                     |
#-- +----------------------------------------------------------------------------+
SQL="
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off

COLUMN instance_name          FORMAT a13         HEAD 'Instance|Name / Number'
COLUMN thread#                FORMAT 99999999    HEAD 'Thread #'
COLUMN host_name              FORMAT a13         HEAD 'Host|Name'
COLUMN status                 FORMAT a6          HEAD 'Status'
COLUMN startup_time           FORMAT a20         HEAD 'Startup|Time'
COLUMN database_status        FORMAT a8          HEAD 'Database|Status'
COLUMN archiver               FORMAT a8          HEAD 'Archiver'
COLUMN logins                 FORMAT a10         HEAD 'Logins?'
COLUMN shutdown_pending       FORMAT a8          HEAD 'Shutdown|Pending?'
COLUMN active_state           FORMAT a6          HEAD 'Active|State'
COLUMN version                                   HEAD 'Version'

SELECT
    instance_name || ' (' || instance_number || ')' instance_name
  , thread# , host_name , status , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
  , database_status , archiver , logins , shutdown_pending , active_state , version
FROM gv\$instance ORDER BY instance_number;
"
elif [ "$CHOICE" = "SUP_LOG" ];then
   SQL="set linesize 134
col SUPPLEMENTAL_LOG_DATA_MIN format  a18 head 'Minimal|Supplemental log| for logminer'
col SUPPLEMENTAL_LOG_DATA_PK format  a18 head 'Supplemental log| for primary keys'
col SUPPLEMENTAL_LOG_DATA_UI format  a18 head 'Supplemental log| for bitmap indexes '
col SUPPLEMENTAL_LOG_DATA_FK format  a18 head 'Supplemental log| for Foreign keys'
col SUPPLEMENTAL_LOG_DATA_ALL format  a18 head 'Supplemental log| for all colums'
col FORCE_LOGGING format  a18 head 'Force logging'
SELECT
  SUPPLEMENTAL_LOG_DATA_MIN ,
  SUPPLEMENTAL_LOG_DATA_PK ,
  SUPPLEMENTAL_LOG_DATA_UI ,
  SUPPLEMENTAL_LOG_DATA_FK ,
  SUPPLEMENTAL_LOG_DATA_ALL ,
  FORCE_LOGGING FORCE_LOG
  from v\$database;"
elif [ "$CHOICE" = "FLASH_LOG" ];then
SQL="
col name format a60
set linesize 134 pagesize 66 head on 
select NAME,log#,THREAD#,SEQUENCE#, bytes/1024/1024 bytes_m,FIRST_CHANGE#,to_char(FIRST_TIME,'DD-MM-YYYY HH24:MI:SS')
from  V\$FLASHBACK_DATABASE_LOGFILE;"
fi

sqlplus -s "$CONNECT_STRING"   <<EOF
$SQL
EOF