10) # "VERIFY SESSIONS PER MEMORY"

10) # "VERIFY SESSIONS PER MEMORY"

echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} VERIFY SESSIONS PER MEMORY ${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 1000 lines 1000 timing on
col name for a25
col program for a60
COL username for A20
COL module for A20
col machine for a50
select se.sid
  , n.name
  , s.program
  , s.machine
  , s.username
  , round(max(se.value)/(1024*1024),2) "MEM (MB)"
from v\$sesstat se, v\$statname n, v\$session s
where n.statistic# = se.statistic#
and s.sid = se.sid
and s.username != 'SYSTEM'
and n.name in ('session pga memory','session uga memory')
group by s.username, s.machine, se.sid,n.name, s.program
order by 6;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 1000 lines 1000 timing on
SET LINESIZE 500
col name for a25
col program for a60
COL username for A20
COL module for A20
col machine for a50
select se.sid
  , n.name
  , s.program
  , s.machine
  , s.username
  , round(max(se.value)/(1024*1024),2) "MEM (MB)"
from v\$sesstat se, v\$statname n, v\$session s
where n.statistic# = se.statistic#
and s.sid = se.sid
and s.username != 'SYSTEM'
and n.name in ('session pga memory','session uga memory')
group by s.username, s.machine, se.sid,n.name, s.program
order by 6;
quit;
EOF
fi