分享两个Shell脚本，一键统计数据库临时表空间和阻塞lock信息

当前位置：首页 - 运维 - 正文

君子好学，自强不息！

2022-11-15 | 运维 | gtxyzz | 578°c

A⁺ A^-

今天主要分享一下两个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$1and$2shouldbemandatoryfrominput
if[[-z$1]]||[[-z$2]];then
echo'***********************************************'
echo'WARNING:UserNameAndPassWordIsNeeded!'
echo'***********************************************'

exit
fi
if[[-z$3]]&&[[-z$ORACLE_SID]];then
echo'***********************************************'
echo'WARNING:ThereisInstancecanbeused!'
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'UsingDefaultInstance:'$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'hasbeenconnected'
echo'***********************************************'
exit
fi

exportSH_USERNAME=$SH_USERNAME
exportSH_DB_SID=$SH_DB_SID
exportSH_PASSWORD=$SH_PASSWORD
exportDB_CONN_STR=$SH_USERNAME/$SH_PASSWORD
#echo$DB_CONN_STR
listfile=`pwd`/listdb
Num=`echoshowuser|$ORACLE_HOME/bin/sqlplus-s$DB_CONN_STR@$SH_DB_SID|grep-i'USER'|wc-l`
if[$Num-gt0]
then
##ok-instanceisup
echo'Instance'$SH_DB_SID'hasbeenconnected'
echo-e'--'`date`'--\n--'$SH_USERNAME@$SH_DB_SID'hasbeenconnected--\n'>>listdb
echo'***********************************************'
echo'InitalizeDBlogindetailsregistryOK!'
echo'NowyoucanExecutionscript~'
echo'***********************************************'
$SHELL

else
##instisinaccessible
echoInstance:$SH_DB_SIDIsInvalidOrUserName/PassWordIsWrong
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'ThereAreBelowSessionsStillAlive'
echo'***********************************************'
fi
sed$((${tmp_txt}-${del_length}+1)),${tmp_txt}d$listfile|teetmp_listfile
mvtmp_listfile$listfile

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

分享两个shell脚本，一键统计数据库临时表空间和阻塞lock信息

showtsps.sh

#!/bin/bash
echo"==================================================查看数据库临时表空间================================================================="
sqlplus-s$DB_CONN_STR@$SH_DB_SID<<EOF
setechooffheadingonunderlineon;
columninst_numheading"InstNum"new_valueinst_numformat99999;
columninst_nameheading"Instance"new_valueinst_nameformata12;
columndb_nameheading"DBName"new_valuedb_nameformata12;
columndbidheading"DBId"new_valuedbidformat9999999999justc;

prompt
promptCurrentInstance
prompt~~~~~~~~~~~~~~~~

selectd.dbiddbid
,d.namedb_name
,i.instance_numberinst_num
,i.instance_nameinst_name
fromv\$databased,
v\$instancei;

settermonfeedbackofflines130pagesize999tabofftrimson
columnMBformat999,999,999heading"TotalMB"
columnfreeformat9,999,999heading"FreeMB"
columnusedformat99,999,999heading"UsedMB"
columnLargestformat999,999heading"LrgstMB"
columntablespace_nameformata20heading"Tablespace"
columnstatusformata3truncated
columnmax_extentsformat99999999999heading"MaxExt"
colextent_managementfora1trunchead"M"
colallocation_typefora1trunchead"A"
colExt_Sizefora4trunchead"Init"
columnpfreeformata3truncheading"%Fr"

breakonreport
computesumofMBonreport
computesumoffreeonreport
computesumofusedonreport

select
d.tablespace_name,
decode(d.status,
'ONLINE','OLN',
'READONLY','R/O',
d.status)status,
d.extent_management,
decode(d.allocation_type,
'USER','',
d.allocation_type)allocation_type,
(case
wheninitial_extent<1048576
thenlpad(round(initial_extent/1024,0),3)||'K'
elselpad(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,
(casewhenround(f.bytes/a.bytes*100,0)>=20then''else'*'end)alrt
FROMsys.dba_tablespacesd,
(SELECTtablespace_name,SUM(bytes)bytes
FROMdba_data_files
GROUPBYtablespace_name)a,
(SELECTtablespace_name,SUM(bytes)bytes
FROMdba_free_space
GROUPBYtablespace_name)f,
(SELECTtablespace_name,MAX(bytes)large
FROMdba_free_space
GROUPBYtablespace_name)l
WHEREd.tablespace_name=a.tablespace_name(+)
ANDd.tablespace_name=f.tablespace_name(+)
ANDd.tablespace_name=l.tablespace_name(+)
ANDNOT(d.extent_managementLIKE'LOCAL'ANDd.contentsLIKE'TEMPORARY')
UNIONALL
select
d.tablespace_name,
decode(d.status,
'ONLINE','OLN',
'READONLY','R/O',
d.status)status,
d.extent_management,
decode(d.allocation_type,
'UNIFORM','U',
'SYSTEM','A',
'USER','',
d.allocation_type)allocation_type,
(case
wheninitial_extent<1048576
thenlpad(round(initial_extent/1024,0),3)||'K'
elselpad(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,
(casewhennvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100)>=20then''else'*'end)alrt
FROMsys.dba_tablespacesd,
(SELECTtablespace_name,SUM(bytes)bytes
FROMdba_temp_files
GROUPBYtablespace_nameorderbytablespace_name)a,
(SELECTtablespace_name,SUM(bytes_used)bytes
FROMv\$temp_extent_pool
GROUPBYtablespace_name)t,
(SELECTtablespace_name,MAX(bytes_cached)large
FROMv\$temp_extent_pool
GROUPBYtablespace_nameorderbytablespace_name)l
WHEREd.tablespace_name=a.tablespace_name(+)
ANDd.tablespace_name=t.tablespace_name(+)
ANDd.tablespace_name=l.tablespace_name(+)
ANDd.extent_managementLIKE'LOCAL'
ANDd.contentsLIKE'TEMPORARY'
ORDERby1
/
prompt
exit
EOF

输出：./showtsps.sh

分享两个shell脚本，一键统计数据库临时表空间和阻塞lock信息

showlock.sh

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

#!/bin/bash
sqlplus-S$DB_CONN_STR@$SH_DB_SID<<EOF
setpages500
setfeedbackoff
setverifyoff

setechoon
setlinesize1000
colobject_nameformata25
colosuserformata10
colmachineformata12
colprogramformata20
--colobject_typeformata10
colstateformata10
colstatusformata10
coloracle_usernameformata12
colsid_serialformata12
colsec_waitformat99999999
collock_typeformata5
colmode_heldformata10

promptCurrentLocks
prompt------------------------------------------------------------------------------------------------------

selectses.sid||','||ses.serial#sid_serial,loc.oracle_username,object_name,
--object_type,
ses.LOGON_TIME,ses.SECONDS_IN_WAITsec_wait,ses.osuser,ses.machine,ses.program,ses.state,ses.status,
decode(d.type,
'MR','MediaRecovery',
'RT','RedoThread',
'UN','UserName',
'TX','Transaction',
'TM','DML',
'UL','PL/SQLUserLock',
'DX','DistribXaction',
'CF','ControlFile',
'IS','InstanceState',
'FS','FileSet',
'IR','InstanceRecovery',
'ST','DiskSpaceTransaction',
'TS','TempSegment',
'IV','LibraryCacheInvalidation',
'LS','LogStartorSwitch',
'RW','RowWait',
'SQ','SequenceNumber',
'TE','ExtendTable',
'TT','TempTable',
d.type)lock_type,
decode(d.lmode,
0,'None',/*MonLockequivalent*/
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
fromv\$locked_objectloc,v\$sessionses,dba_objectsobj,v\$lockd
whereloc.object_id=obj.object_id
andloc.session_id=ses.sid
andobj.object_id=d.id1
andses.sid=d.sid
orderbyoracle_username,seconds_in_waitdesc
;
setheadoff
SELECT'Therearealso'||count(*)||'transactionlocks'
FROMv\$transaction_enqueue;
prompt------------------------------------------------------------------------------------------------------

setheadon
setlinesize1000pagesize1000
col进程SIDfor99999trunc
col锁类型formata10
colSQL语句formata60
col等待事件formata20
col锁时间formata20
col锁角色formata15
col阻塞会话SIDformata30


prompt
promptBlockingSessionDetails
prompt------------------------------------------------------------------------------------------------------

SELECTmm.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'
"锁时间",CASEWHENmm.block=1
ANDmm.lmode!=0THEN'holder'
WHENmm.block=0
ANDmm.request!=0THEN'waiter'
ELSENULLEND"锁角色",
CASEWHENee.blocking_sessionISNOTNULLTHEN'waitingforSID'||ee.blocking_session
ELSENULLEND"阻塞会话SID",
dd.sql_text"SQL语句",
cc.event"等待事件"
FROMgv\$lockmm,
gv\$sessionee,
gv\$sqlareadd,
gv\$session_waitcc
WHEREmm.sidIN(SELECTnn.sid
FROM(SELECTtt.*,
COUNT(1)OVER(PARTITIONBYtt.TYPE,
tt.id1,
tt.id2)cnt,MAX(tt.lmode)OVER(PARTITIONBYtt.TYPE,
tt.id1,
tt.id2)lmod_flag,MAX(tt.request)OVER(PARTITIONBYtt.TYPE,
tt.id1,
tt.id2)request_flag
FROMgv\$locktt)nn
WHEREnn.cnt>1
ANDnn.lmod_flag!=0
ANDnn.request_flag!=0)
ANDmm.sid=ee.sid(+)
ANDee.sql_id=dd.sql_id(+)
ANDmm.sid=cc.sid(+)
AND((mm.block=1
ANDmm.lmode!=0)
OR(mm.block=0
ANDmm.request!=0))
ORDERBYmm.TYPE,mm.id1,mm.id2,mm.lmodeDESC,
mm.ctimeDESC;

exit
EOF

分享两个shell脚本，一键统计数据库临时表空间和阻塞lock信息