20) # "KILL A RUNNING SESSION"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} KILL A RUNNING SESSION ${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 '|'
col MACHINE for a30
col username for a25
col OSUSER for a30
col PROGRAM for a60
col "SID,SERIAL,@INST" for a20
select OSUSER
, MACHINE
, SID || ',' || SERIAL# || ',@' || INST_ID as "SID,SERIAL,@INST"
, USERNAME
, PROGRAM
from gv\$session
where osuser not in ('GRID','grid','ORACLE','oracle')
order by 1,2,3,5;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col MACHINE for a30
col username for a25
col OSUSER for a30
col PROGRAM for a60
col "SID,SERIAL,@INST" for a20
select OSUSER
, MACHINE
, SID || ',' || SERIAL# || ',@' || INST_ID as "SID,SERIAL,@INST"
, USERNAME
, PROGRAM
from gv\$session
where osuser not in ('GRID','grid','ORACLE','oracle')
order by 1,2,3,5;
quit;
EOF
fi
Thursday, December 7, 2017
19) # "VERIFY RUNNING JOBS"
19) # "VERIFY RUNNING JOBS"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} VERIFY RUNNING JOBS ${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 500 timing on VERIFY OFF colsep '|'
col "Last Date" for a30
col "This Date" for a30
SELECT a.job "Job"
, a.sid
, a.failures "Failures"
, Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date"
, Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM dba_jobs_running a;
SET VERIFY ON
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 1000 lines 500 timing on VERIFY OFF colsep '|'
col "Last Date" for a30
col "This Date" for a30
SELECT a.job "Job"
, a.sid
, a.failures "Failures"
, Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date"
, Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM dba_jobs_running a;
SET VERIFY ON
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} VERIFY RUNNING JOBS ${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 500 timing on VERIFY OFF colsep '|'
col "Last Date" for a30
col "This Date" for a30
SELECT a.job "Job"
, a.sid
, a.failures "Failures"
, Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date"
, Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM dba_jobs_running a;
SET VERIFY ON
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 1000 lines 500 timing on VERIFY OFF colsep '|'
col "Last Date" for a30
col "This Date" for a30
SELECT a.job "Job"
, a.sid
, a.failures "Failures"
, Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date"
, Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM dba_jobs_running a;
SET VERIFY ON
quit;
EOF
fi
18) # "MATERIALIZEDS VIEWS DISABLEDS"
18) # "MATERIALIZEDS VIEWS DISABLEDS"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} MATERIALIZEDS VIEWS DISABLEDS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col owner for a20
col type for a20
select owner
,type
,tablespace_name
,round(sum(mb)) as mb
from (select owner,'mview' as type, tablespace_name, round(sum(bytes)/1024/1024) as mb from dba_segments where (owner,segment_name) in
(select owner, mview_name from dba_mviews) group by owner, segment_type, tablespace_name
union
select owner
, 'mview_log' as type
, tablespace_name
, round(sum(bytes)/1024/1024) as mb
from dba_segments
where (owner, segment_name) in
(select log_owner, log_table from dba_snapshot_logs)
group by owner, segment_type, tablespace_name
union
select owner
, 'mview_index' as type
, tablespace_name
, round(sum(bytes)/1024/1024) as mb
from dba_segments
where (owner,segment_name) in
(select owner, index_name from dba_indexes where (owner,table_name) in
(select owner, mview_name from dba_mviews))
group by owner, segment_type, tablespace_name) t1
group by owner,type,tablespace_name;
quit;
EOF
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} MATERIALIZEDS VIEWS DISABLEDS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col owner for a20
col type for a20
select owner
,type
,tablespace_name
,round(sum(mb)) as mb
from (select owner,'mview' as type, tablespace_name, round(sum(bytes)/1024/1024) as mb from dba_segments where (owner,segment_name) in
(select owner, mview_name from dba_mviews) group by owner, segment_type, tablespace_name
union
select owner
, 'mview_log' as type
, tablespace_name
, round(sum(bytes)/1024/1024) as mb
from dba_segments
where (owner, segment_name) in
(select log_owner, log_table from dba_snapshot_logs)
group by owner, segment_type, tablespace_name
union
select owner
, 'mview_index' as type
, tablespace_name
, round(sum(bytes)/1024/1024) as mb
from dba_segments
where (owner,segment_name) in
(select owner, index_name from dba_indexes where (owner,table_name) in
(select owner, mview_name from dba_mviews))
group by owner, segment_type, tablespace_name) t1
group by owner,type,tablespace_name;
quit;
EOF
17) # "JOBS CONTROL OF THE CLIENT [ @ ]"
17) # "JOBS CONTROL OF THE CLIENT [ @ ]"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} JOBS CONTROL OF THE CLIENT ( @ ) ${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 '|'
col COMMENTS for a100
col JOB_NAME for a30
col owner for a10
col state for a12
col SCHEDULE_TYPE for a16
col LAST_EXEC for a20
col enabled for a8
SELECT JOB_NAME
, STATE
, ENABLED
, PROGRAM_OWNER as OWNER
, SCHEDULE_TYPE
, RUN_COUNT as EXEC
, to_char(LAST_START_DATE, 'dd/mm/yyyy HH:MM:SS') as LAST_EXEC
, COMMENTS
FROM DBA_SCHEDULER_JOBS
ORDER BY 1,2,3,4,5,6,7;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col COMMENTS for a100
col JOB_NAME for a30
col owner for a10
col state for a12
col SCHEDULE_TYPE for a16
col LAST_EXEC for a20
col enabled for a8
SELECT JOB_NAME
, STATE
, ENABLED
, PROGRAM_OWNER as OWNER
, SCHEDULE_TYPE
, RUN_COUNT as EXEC
, to_char(LAST_START_DATE, 'dd/mm/yyyy HH:MM:SS') as LAST_EXEC
, COMMENTS
FROM DBA_SCHEDULER_JOBS
ORDER BY 1,2,3,4,5,6,7;
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} JOBS CONTROL OF THE CLIENT ( @ ) ${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 '|'
col COMMENTS for a100
col JOB_NAME for a30
col owner for a10
col state for a12
col SCHEDULE_TYPE for a16
col LAST_EXEC for a20
col enabled for a8
SELECT JOB_NAME
, STATE
, ENABLED
, PROGRAM_OWNER as OWNER
, SCHEDULE_TYPE
, RUN_COUNT as EXEC
, to_char(LAST_START_DATE, 'dd/mm/yyyy HH:MM:SS') as LAST_EXEC
, COMMENTS
FROM DBA_SCHEDULER_JOBS
ORDER BY 1,2,3,4,5,6,7;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col COMMENTS for a100
col JOB_NAME for a30
col owner for a10
col state for a12
col SCHEDULE_TYPE for a16
col LAST_EXEC for a20
col enabled for a8
SELECT JOB_NAME
, STATE
, ENABLED
, PROGRAM_OWNER as OWNER
, SCHEDULE_TYPE
, RUN_COUNT as EXEC
, to_char(LAST_START_DATE, 'dd/mm/yyyy HH:MM:SS') as LAST_EXEC
, COMMENTS
FROM DBA_SCHEDULER_JOBS
ORDER BY 1,2,3,4,5,6,7;
quit;
EOF
fi
16) # "INVALIDS OBJECTS ( @ )"
16) # "INVALIDS OBJECTS ( @ )"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} INVALIDS OBJECTS ( @ ) ${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 '|'
col owner for a25
col OBJECT_TYPE for a25
col "Total of invalids objects." for a30
select owner
, decode(object_type,null,'========================>', object_type) as "OBJECT_TYPE"
, count(object_type) as "TOTAL"
, decode(grouping(owner),0,null,1,'Total of invalids objects.') as " "
from dba_objects where status <> 'VALID'
group by rollup (owner, object_type)
order by owner, object_type desc;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col owner for a25
col OBJECT_TYPE for a25
col "Total of invalids objects." for a30
select owner
, decode(object_type,null,'========================>', object_type) as "OBJECT_TYPE"
, count(object_type) as "TOTAL"
, decode(grouping(owner),0,null,1,'Total of invalids objects.') as " "
from dba_objects where status <> 'VALID'
group by rollup (owner, object_type)
order by owner, object_type desc;
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} INVALIDS OBJECTS ( @ ) ${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 '|'
col owner for a25
col OBJECT_TYPE for a25
col "Total of invalids objects." for a30
select owner
, decode(object_type,null,'========================>', object_type) as "OBJECT_TYPE"
, count(object_type) as "TOTAL"
, decode(grouping(owner),0,null,1,'Total of invalids objects.') as " "
from dba_objects where status <> 'VALID'
group by rollup (owner, object_type)
order by owner, object_type desc;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col owner for a25
col OBJECT_TYPE for a25
col "Total of invalids objects." for a30
select owner
, decode(object_type,null,'========================>', object_type) as "OBJECT_TYPE"
, count(object_type) as "TOTAL"
, decode(grouping(owner),0,null,1,'Total of invalids objects.') as " "
from dba_objects where status <> 'VALID'
group by rollup (owner, object_type)
order by owner, object_type desc;
quit;
EOF
fi
15) # "LOAD LASTS 1/5/10 MINUTES"
15) # "LOAD LASTS 1/5/10 MINUTES"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} LOAD LASTS 1/5/10 MINUTES ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
if [ `uname` = "SunOS" ]
then
uptime | awk '{print $(NF-2)" "$(NF-1)" "$(NF-0)}'
elif [ `uname` = "AIX" ]
then
echo ${ECHO} "AIX"
elif [ `uname` = "Linux" ]
then
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
echo ${ECHO} "${RED} NOW |5min |15min | ${BLACK}"
cat /proc/loadavg
else
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
echo ${ECHO} " -- This Operation System is Unknown --"
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} LOAD LASTS 1/5/10 MINUTES ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
if [ `uname` = "SunOS" ]
then
uptime | awk '{print $(NF-2)" "$(NF-1)" "$(NF-0)}'
elif [ `uname` = "AIX" ]
then
echo ${ECHO} "AIX"
elif [ `uname` = "Linux" ]
then
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
echo ${ECHO} "${RED} NOW |5min |15min | ${BLACK}"
cat /proc/loadavg
else
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
echo ${ECHO} " -- This Operation System is Unknown --"
echo ${ECHO} ">----------------------------------------------------------------------------------------------------"
fi
14) # "VERIFY LONG OPERATIONS"
14) # "VERIFY LONG OPERATIONS"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "-- ${RED} VERIFY LONG OPERATIONS ${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 '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "-- ${RED} VERIFY LONG OPERATIONS ${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 '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
prompt # ACTIVE SESSIONS AND SQL STATEMENTS #
prompt ##############################################################
col USERNAME for a15
col SQL_TEXT for a100
col machine for a35
col osuser for a15
select S.USERNAME
, s.sid
, s.serial#
, s.machine
, s.osuser
, s.seconds_in_wait as "waiting seg"
, t.sql_id
, sql_text
from v\$sqltext_with_newlines t, V\$SESSION s
where t.address = s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
order by s.sid, t.piece;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OBJECT_NAME for a30
select object_name
, object_type
, session_id
, type
, lmode
, request
, block
, ctime
from v\$locked_object, all_objects, v\$lock
where v\$locked_object.object_id = all_objects.object_id AND v\$lock.id1 = all_objects.object_id AND v\$lock.sid = v\$locked_object.session_id
order by session_id, ctime desc, object_name;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a35
col UNITS for a10
col MESSAGE for a80
col sql_id for a15
select distinct * from (select opname, target, sofar, sql_id, totalwork, units, elapsed_seconds, message from v\$session_longops order by start_time desc) where rownum <= 10;
prompt ##############################################################
prompt # #
prompt ##############################################################
col OPNAME for a30
col TARGET for a30
SELECT opname
, target
, osuser
, sl.sql_id
, sl.sql_hash_value
, elapsed_seconds
, time_remaining
FROM v\$session_longops sl
inner join v\$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL# WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col USERNAME for a15
col sql_fulltext for a75
col TARGET for a30
SELECT s.username
, sl.sid
, sq.executions
, sl.last_update_time
, sl.sql_id
, sl.sql_hash_value
, opname
, target
, elapsed_seconds
, time_remaining
, sq.sql_fulltext
FROM v\$session_longops sl
INNER JOIN v\$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v\$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
prompt ##############################################################
prompt # #
prompt ##############################################################
col units for a15
col opname for a30
SELECT sql_id
, opname
, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') as started
, to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now
, trunc(((((86400*(sysdate-start_time))/60)/60)/24)/7) weeks
, trunc((((86400*(sysdate-start_time))/60)/60)/24) days
, trunc(((86400*(sysdate-start_time))/60)/60)-24*(trunc((((86400*(sysdate-start_time))/60)/60)/24)) hours
, trunc((86400*(sysdate-start_time))/60)-60*(trunc(((86400*(sysdate-start_time))/60)/60)) minutes
, trunc(86400*(sysdate-start_time))-60*(trunc((86400*(sysdate-start_time))/60)) seconds
, sofar
, totalwork
, units
, round(elapsed_seconds/60/60,2) as seconds
, round(time_remaining/60/60,2) as remaining
FROM v\$session_longops
WHERE sofar != totalwork;
quit;
EOF
fi
13) # "HIT RATIO THE LASTS 30 DAYS"
13) # "HIT RATIO THE LASTS 30 DAYS"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} HIT RATIO THE LASTS 30 DAYS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col name for a40
col instance_name for a30
exec dbms_application_info.set_action('latches');
SELECT instance_name
, name
, case when hit_ratio < 95 then 'Warning' when hit_ratio < 99 then 'Critical' end as hit_ratio
, sleep_miss
from (select i.instance_name, l.name, round((gets-misses)/decode(gets,0,1,gets),3)*100 hit_ratio, round(sleeps/decode(misses,0,1,misses),3) sleep_miss
from gv\$latch l, gv\$instance i
where l.gets != 0
and l.inst_id = i.inst_id)
where hit_ratio < 100
order by hit_ratio;
quit;
EOF
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} HIT RATIO THE LASTS 30 DAYS ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col name for a40
col instance_name for a30
exec dbms_application_info.set_action('latches');
SELECT instance_name
, name
, case when hit_ratio < 95 then 'Warning' when hit_ratio < 99 then 'Critical' end as hit_ratio
, sleep_miss
from (select i.instance_name, l.name, round((gets-misses)/decode(gets,0,1,gets),3)*100 hit_ratio, round(sleeps/decode(misses,0,1,misses),3) sleep_miss
from gv\$latch l, gv\$instance i
where l.gets != 0
and l.inst_id = i.inst_id)
where hit_ratio < 100
order by hit_ratio;
quit;
EOF
12) # "VERIFY SESSIONS PER I/O"
12) # "VERIFY SESSIONS PER I/O"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} VERIFY SESSIONS PER I/O ${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 LINESIZE 2000 PAGESIZE 2000 TIMING ON
COLUMN username FORMAT A25
col osuser for a25
SELECT NVL(s.username, '(oracle)') AS username
, s.osuser
, s.sid
, s.serial#
, si.block_gets
, si.consistent_gets
, si.physical_reads
, si.block_changes
, si.consistent_changes
FROM v\$session s, v\$sess_io si
WHERE s.sid = si.sid
and s.osuser not in ('GRID','grid','ORACLE','oracle')
ORDER BY s.username, s.osuser, si.physical_reads;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
SET LINESIZE 2000 PAGESIZE 2000 TIMING ON
COLUMN username FORMAT A25
col osuser for a25
SELECT NVL(s.username, '(oracle)') AS username
, s.osuser
, s.sid
, s.serial#
, si.block_gets
, si.consistent_gets
, si.physical_reads
, si.block_changes
, si.consistent_changes
FROM v\$session s, v\$sess_io si
WHERE s.sid = si.sid
and s.osuser not in ('GRID','grid','ORACLE','oracle')
ORDER BY s.username, s.osuser, si.physical_reads;
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} VERIFY SESSIONS PER I/O ${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 LINESIZE 2000 PAGESIZE 2000 TIMING ON
COLUMN username FORMAT A25
col osuser for a25
SELECT NVL(s.username, '(oracle)') AS username
, s.osuser
, s.sid
, s.serial#
, si.block_gets
, si.consistent_gets
, si.physical_reads
, si.block_changes
, si.consistent_changes
FROM v\$session s, v\$sess_io si
WHERE s.sid = si.sid
and s.osuser not in ('GRID','grid','ORACLE','oracle')
ORDER BY s.username, s.osuser, si.physical_reads;
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
SET LINESIZE 2000 PAGESIZE 2000 TIMING ON
COLUMN username FORMAT A25
col osuser for a25
SELECT NVL(s.username, '(oracle)') AS username
, s.osuser
, s.sid
, s.serial#
, si.block_gets
, si.consistent_gets
, si.physical_reads
, si.block_changes
, si.consistent_changes
FROM v\$session s, v\$sess_io si
WHERE s.sid = si.sid
and s.osuser not in ('GRID','grid','ORACLE','oracle')
ORDER BY s.username, s.osuser, si.physical_reads;
quit;
EOF
fi
11) # "DATABASE SIZE"
11) # "DATABASE SIZE"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} DATABASE SIZE ${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 '|'
prompt ##############################################################
PROMPT # DATABASE SIZE #
prompt ##############################################################
col "SIZE MB" for a15
col "SIZE GB" for a15
col "SIZE TB" for a15
select to_char(sum(bytes)/1024/1024, '9G999G999D99') "SIZE MB"
, to_char(sum(bytes)/1024/1024/1024, '9G999G999D99') "SIZE GB"
, to_char(sum(bytes)/1024/1024/1024/1024, '9G999G999D999') "SIZE TB"
from (select sum(bytes) bytes from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(bytes * members) from v\$log
union all
select sum(block_size * file_size_blks) from v\$controlfile);
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
PROMPT # DATABASE SIZE #
prompt ##############################################################
col "SIZE MB" for a15
col "SIZE GB" for a15
col "SIZE TB" for a15
select to_char(sum(bytes)/1024/1024, '9G999G999D99') "SIZE MB"
, to_char(sum(bytes)/1024/1024/1024, '9G999G999D99') "SIZE GB"
, to_char(sum(bytes)/1024/1024/1024/1024, '9G999G999D999') "SIZE TB"
from (select sum(bytes) bytes from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(bytes * members) from v\$log
union all
select sum(block_size * file_size_blks) from v\$controlfile);
quit;
EOF
fi
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} DATABASE SIZE ${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 '|'
prompt ##############################################################
PROMPT # DATABASE SIZE #
prompt ##############################################################
col "SIZE MB" for a15
col "SIZE GB" for a15
col "SIZE TB" for a15
select to_char(sum(bytes)/1024/1024, '9G999G999D99') "SIZE MB"
, to_char(sum(bytes)/1024/1024/1024, '9G999G999D99') "SIZE GB"
, to_char(sum(bytes)/1024/1024/1024/1024, '9G999G999D999') "SIZE TB"
from (select sum(bytes) bytes from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(bytes * members) from v\$log
union all
select sum(block_size * file_size_blks) from v\$controlfile);
quit;
EOF
else
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
prompt ##############################################################
PROMPT # DATABASE SIZE #
prompt ##############################################################
col "SIZE MB" for a15
col "SIZE GB" for a15
col "SIZE TB" for a15
select to_char(sum(bytes)/1024/1024, '9G999G999D99') "SIZE MB"
, to_char(sum(bytes)/1024/1024/1024, '9G999G999D99') "SIZE GB"
, to_char(sum(bytes)/1024/1024/1024/1024, '9G999G999D999') "SIZE TB"
from (select sum(bytes) bytes from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(bytes * members) from v\$log
union all
select sum(block_size * file_size_blks) from v\$controlfile);
quit;
EOF
fi
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
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
9) # "TOP 10 DB-CPU ACTIVITY"
9) # "TOP 10 DB-CPU ACTIVITY"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} TOP 20 DB-CPU ACTIVITY ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col STAT_NAME for a50
col "% PERC" for a10
SELECT STAT_NAME
, TIME_WAITED
, case when pct_waited >= 0.5 then 'Critical' when pct_waited >= 0.2 then 'Warning' end as "Status"
, to_char(round(pct_waited*100,1), '999D00') || '%' as "% PERC"
from (select STAT_NAME, time_waited, TIME_WAITED / sum(time_waited) over () pct_waited
from (select STAT_NAME, round(sum(VALUE)/(1000*1000)) AS time_waited
from GV\$SYS_TIME_MODEL
group by STAT_NAME)
order by 2 desc)
where rownum <= 20;
quit;
EOF
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
echo ${ECHO} "${RED} TOP 20 DB-CPU ACTIVITY ${BLACK}"
echo ${ECHO} ">--------------------------------------------------------------------------------------------------${RED} ${OPTION} ${BLACK}"
${LOADING}
sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 700 lines 700 timing on colsep '|'
col STAT_NAME for a50
col "% PERC" for a10
SELECT STAT_NAME
, TIME_WAITED
, case when pct_waited >= 0.5 then 'Critical' when pct_waited >= 0.2 then 'Warning' end as "Status"
, to_char(round(pct_waited*100,1), '999D00') || '%' as "% PERC"
from (select STAT_NAME, time_waited, TIME_WAITED / sum(time_waited) over () pct_waited
from (select STAT_NAME, round(sum(VALUE)/(1000*1000)) AS time_waited
from GV\$SYS_TIME_MODEL
group by STAT_NAME)
order by 2 desc)
where rownum <= 20;
quit;
EOF
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
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
Subscribe to:
Posts (Atom)