8) # "CONNECTIONS AVARAGE PER HOUR"

8) # "CONNECTIONS AVARAGE PER HOUR"

echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} CONNECTIONS AVARAGE PER HOUR ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
if [ ${DBVERSION} = "12" ]
then
PDB=$(sqlplus -S /nolog <<EOF
conn / as sysdba
set pagesize 0 linesize 32767 feedback off verify off heading off echo off
SELECT NAME FROM V\$CONTAINERS ORDER BY CON_ID, NAME;
quit;
END)
# echo -e "\n${PDB}" | tail +2
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} -- SELECT YOUR DATABASE [ CDB$ROOT / PDB ] ${BLACK} --"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
select SET_PDB in ${PDB}
do
  PPDB=${SET_PDB}
break 1
done
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} " -- YOUR DATABASE SELECTED IS: ${RED} ${PPDB} ${BLACK} --"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
sqlplus -S /nolog <<EOF
conn / as sysdba
alter session set container=${PPDB};
set pages 700 lines 700 timing on colsep '|'
SELECT to_char(TRUNC(s.begin_interval_time,'HH24'),'DD/MM/YYYY HH24:MI:SS') snap_begin, sum(r.current_utilization) sessions
FROM dba_hist_resource_limit r, dba_hist_snapshot s
WHERE (TRUNC(s.begin_interval_time,'HH24'), s.snap_id ) IN
(--Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND (TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(--Select the Maximum no.of sessions for a given begin interval time
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24'))
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
GROUP BY to_char(TRUNC(s.begin_interval_time,'HH24'),'DD/MM/YYYY HH24:MI:SS')
ORDER BY snap_begin;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
SELECT to_char(TRUNC(s.begin_interval_time,'HH24'),'DD/MM/YYYY HH24:MI:SS') snap_begin, sum(r.current_utilization) sessions
FROM dba_hist_resource_limit r, dba_hist_snapshot s
WHERE (TRUNC(s.begin_interval_time,'HH24'), s.snap_id ) IN
(--Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND (TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(--Select the Maximum no.of sessions for a given begin interval time
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24'))
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
GROUP BY to_char(TRUNC(s.begin_interval_time,'HH24'),'DD/MM/YYYY HH24:MI:SS')
ORDER BY snap_begin;
quit;
EOF
fi