概述

今天主要分享一下两个shell脚本，主要是为了查看数据库的临时表空间和阻塞lock信息，下面一起来看看吧~

数据库连接脚本

use script settdb.sh for DB login details registry

#!/bin/bash
tmp_username=$SH_USERNAME
tmp_password=$SH_PASSWORD
tmp_db_sid=$SH_DB_SID

#check $1 and $2 should be mandatory from input
if [[ -z $1 ]] || [[ -z $2 ]]; then
echo '***********************************************'
echo 'WARNING :UserName And PassWord Is Needed!'
echo '***********************************************'

exit
fi
if [[ -z $3 ]] && [[ -z $ORACLE_SID ]];then
echo '***********************************************'
echo 'WARNING :There is Instance can be used !'
echo '***********************************************'
exit
fi

SH_USERNAME=`echo "$1"|tr '[a-z]' '[A-Z]'`
SH_PASSWORD=$2
echo '***********************************************'

if [[ -z $3 ]]
then
 SH_DB_SID=$ORACLE_SID
 echo 'Using Default Instance :'$ORACLE_SID
 echo .
else
 SH_DB_SID=`echo "$3"|tr '[a-z]' '[A-Z]'`
fi

if [[ $SH_DB_SID = $tmp_db_sid ]] && [[ $SH_USERNAME = $tmp_username ]] && [[ $SH_PASSWORD = $tmp_password ]];then
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo '***********************************************'
 exit
fi

export SH_USERNAME=$SH_USERNAME
export SH_DB_SID=$SH_DB_SID
export SH_PASSWORD=$SH_PASSWORD
export DB_CONN_STR=$SH_USERNAME/$SH_PASSWORD
#echo $DB_CONN_STR
listfile=`pwd`/listdb
Num=`echo show user | $ORACLE_HOME/bin/sqlplus -s $DB_CONN_STR@$SH_DB_SID| grep -i 'USER ' | wc -l`
if [ $Num -gt 0 ]
 then
 ## ok - instance is up
 echo 'Instance '$SH_DB_SID 'has been connected'
 echo -e '--' `date`'-- \n--'$SH_USERNAME@$SH_DB_SID 'has been connected --\n' >> listdb
 echo '***********************************************'
 echo 'Initalize DB login details registry OK!'
 echo 'Now you can Execution script~'
 echo '***********************************************'
 $SHELL

 else
 ## inst is inaccessible 
 echo Instance: $SH_DB_SID Is Invalid Or UserName/PassWord Is Wrong 
 echo '***********************************************'
 exit
 fi
del_length=3
tmp_txt=$(sed -n '$=' listdb) 
echo '***********************************************'
echo '********* ' $SH_USERNAME'@'$SH_DB_SID '**********'
echo '***********************************************'
curr_len=`cat $listfile|wc -l`
if [ $curr_len -gt $del_length ]; then
echo ' There Are Below Sessions Still Alive '
echo '***********************************************'
fi
sed $((${tmp_txt}-${del_length}+1)),${tmp_txt}d $listfile | tee tmp_listfile
mv tmp_listfile $listfile

输出：./settdb.sh 用户名 用户密码

showtsps.sh

#!/bin/bash
echo "==================================================查看数据库临时表空间================================================================="
sqlplus -s $DB_CONN_STR@$SH_DB_SID <<EOF
set echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

select d.dbid dbid
 , d.name db_name
 , i.instance_number inst_num
 , i.instance_name inst_name
 from v\$database d,
 v\$instance i;

set term on feedback off lines 130 pagesize 999 tab off trims on
column MB format 999,999,999 heading "Total MB"
column free format 9,999,999 heading "Free MB"
column used format 99,999,999 heading "Used MB"
column Largest format 999,999 heading "LrgstMB"
column tablespace_name format a20 heading "Tablespace"
column status format a3 truncated
column max_extents format 99999999999 heading "MaxExt"
col extent_management for a1 trunc head "M"
col allocation_type for a1 trunc head "A"
col Ext_Size for a4 trunc head "Init"
column pfree format a3 trunc heading "%Fr"

break on report
compute sum of MB on report
compute sum of free on report
compute sum of used on report

select 
 d.tablespace_name, 
 decode(d.status, 
 'ONLINE', 'OLN',
 'READ ONLY', 'R/O',
 d.status) status,
 d.extent_management, 
 decode(d.allocation_type,
 'USER','',
 d.allocation_type) allocation_type,
 (case 
 when initial_extent < 1048576 
 then lpad(round(initial_extent/1024,0),3)||'K' 
 else lpad(round(initial_extent/1024/1024,0),3)||'M' 
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 NVL (f.bytes / 1024 / 1024, 0) free, 
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
 NVL (l.large / 1024 / 1024, 0) largest, 
 d.MAX_EXTENTS ,
 lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
 (case when round(f.bytes/a.bytes*100,0) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_data_files
 GROUP BY tablespace_name) a,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_free_space
 GROUP BY tablespace_name) f,
 (SELECT tablespace_name, MAX(bytes) large
 FROM dba_free_space
 GROUP BY tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = f.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
select 
 d.tablespace_name, 
 decode(d.status, 
 'ONLINE', 'OLN',
 'READ ONLY', 'R/O',
 d.status) status,
 d.extent_management, 
 decode(d.allocation_type,
 'UNIFORM','U',
 'SYSTEM','A',
 'USER','',
 d.allocation_type) allocation_type,
 (case 
 when initial_extent < 1048576 
 then lpad(round(initial_extent/1024,0),3)||'K' 
 else lpad(round(initial_extent/1024/1024,0),3)||'M' 
 end) Ext_Size,
 NVL (a.bytes / 1024 / 1024, 0) MB,
 (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
 NVL (t.bytes / 1024 / 1024, 0) used, 
 NVL (l.large / 1024 / 1024, 0) largest, 
 d.MAX_EXTENTS ,
 lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
 (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
 (SELECT tablespace_name, SUM(bytes) bytes
 FROM dba_temp_files
 GROUP BY tablespace_name order by tablespace_name) a,
 (SELECT tablespace_name, SUM(bytes_used ) bytes
 FROM v\$temp_extent_pool
 GROUP BY tablespace_name) t,
 (SELECT tablespace_name, MAX(bytes_cached) large
 FROM v\$temp_extent_pool
 GROUP BY tablespace_name order by tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = t.tablespace_name(+)
 AND d.tablespace_name = l.tablespace_name(+)
 AND d.extent_management LIKE 'LOCAL'
 AND d.contents LIKE 'TEMPORARY'
 ORDER by 1
/
prompt
exit
EOF

输出：./showtsps.sh

showlock.sh

这里主要是查看阻塞lock信息，脚本内容如下：

#!/bin/bash
sqlplus -S $DB_CONN_STR@$SH_DB_SID <<EOF 
set pages 500
set feedback off
set verify off

set echo on
set linesize 1000
col object_name format a25
col osuser format a10
col machine format a12
col program format a20
--col object_type format a10
col state format a10
col status format a10
col oracle_username format a12
col sid_serial format a12
col sec_wait format 99999999
col lock_type format a5
col mode_held format a10

prompt Current Locks
prompt ------------------------------------------------------------------------------------------------------

select ses.sid||','||ses.serial# sid_serial,loc.oracle_username,object_name,
--object_type,
ses.LOGON_TIME,ses.SECONDS_IN_WAIT sec_wait,ses.osuser,ses.machine,ses.program,ses.state,ses.status,
 decode(d.type,
 'MR', 'Media Recovery',
 'RT', 'Redo Thread',
 'UN', 'User Name',
 'TX', 'Transaction',
 'TM', 'DML',
 'UL', 'PL/SQL User Lock',
 'DX', 'Distrib Xaction',
 'CF', 'Control File',
 'IS', 'Instance State',
 'FS', 'File Set',
 'IR', 'Instance Recovery',
 'ST', 'Disk Space Transaction',
 'TS', 'Temp Segment',
 'IV', 'Library Cache Invalidation',
 'LS', 'Log Start or Switch',
 'RW', 'Row Wait',
 'SQ', 'Sequence Number',
 'TE', 'Extend Table',
 'TT', 'Temp Table',
 d.type) lock_type,
 decode(d.lmode,
 0, 'None', /* Mon Lock equivalent */
 1, 'Null', /* N */
 2, 'Row-S (SS)', /* L */
 3, 'Row-X (SX)', /* R */
 4, 'Share', /* S */
 5, 'S/Row-X (SSX)', /* C */
 6, 'Exclusive', /* X */
 to_char(d.lmode)) mode_held
 from v\$locked_object loc,v\$session ses,dba_objects obj,v\$lock d
 where loc.object_id=obj.object_id 
 and loc.session_id=ses.sid
 and obj.object_id=d.id1
 and ses.sid=d.sid
 order by oracle_username,seconds_in_wait desc
;
set head off 
SELECT 'There are also '||count(*)||' transaction locks'
FROM v\$transaction_enqueue;
prompt ------------------------------------------------------------------------------------------------------

set head on
set linesize 1000 pagesize 1000
col 进程SID for 99999 trunc 
col 锁类型 format a10
col SQL语句 format a60
col 等待事件 format a20
col 锁时间 format a20
col 锁角色 format a15
col 阻塞会话SID format a30


prompt
prompt Blocking Session Details
prompt ------------------------------------------------------------------------------------------------------

SELECT mm.inst_id "实例ID", 
 mm.sid "进程SID", 
 mm.TYPE "锁类型", 
 mm.id1 "事务号ID1", 
 mm.id2 "事务号ID2", 
 LPAD(TRUNC(mm.ctime / 60 / 60), 3) || ' Hour ' || LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60, 'fm09'), 2) || ' Min ' || LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, 'fm09'), 2) || ' Sec'
 "锁时间", CASE WHEN mm.block = 1 
 AND mm.lmode != 0 THEN 'holder' 
 WHEN mm.block = 0 
 AND mm.request != 0 THEN 'waiter' 
 ELSE NULL END "锁角色", 
 CASE WHEN ee.blocking_session IS NOT NULL THEN 'waiting for SID ' || ee.blocking_session 
 ELSE NULL END "阻塞会话SID", 
 dd.sql_text "SQL语句", 
 cc.event "等待事件"
 FROM gv\$lock mm, 
 gv\$session ee, 
 gv\$sqlarea dd, 
 gv\$session_wait cc 
 WHERE mm.sid IN (SELECT nn.sid 
 FROM (SELECT tt.*, 
 COUNT(1) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) cnt, MAX(tt.lmode) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) lmod_flag, MAX(tt.request) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) request_flag 
 FROM gv\$lock tt) nn 
 WHERE nn.cnt > 1 
 AND nn.lmod_flag != 0 
 AND nn.request_flag != 0) 
 AND mm.sid = ee.sid (+) 
 AND ee.sql_id = dd.sql_id (+) 
 AND mm.sid = cc.sid (+) 
 AND ((mm.block = 1 
 AND mm.lmode != 0) 
 OR (mm.block = 0 
 AND mm.request != 0)) 
 ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC, 
 mm.ctime DESC;

exit
EOF

输出：./showlock.sh

大家还有什么需要做统计的可以在下方留言哦，后面我会一起加进来。后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下