oracurad_查看游标使用情况
#!/bin/bash
echo "\n=============`date`===================\n"
if [ "$LOGNAME" = "oracle" ]; then
SQLPLUS_CMD="/ as sysdba";
else
SQLPLUS_CMD="/";
fi
if [ $# -ne 0 ];then
echo "Example: $0"
exit
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
set lin 2000;
column parameter format a29;
column value format a5;
column usage format a40;
select
'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '999990') || '%') usage
from
( select
max(s.value) used
from
sys.v_\$statname n,
sys.v_\$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
sys.v_\$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
sys.v_\$statname n,
sys.v_\$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
sys.v_\$parameter
where
name = 'open_cursors'
)
/
EOF
脚本效果:
$./oracuradv
\n=============Mon Oct 19 16:51:19 CST 2020===================\n
PARAMETER VALUE USAGE
----------------------------- ----- ----------------------------------------
session_cached_cursors 50 417406%
open_cursors 300 ####%