[오라클] Temp/Undo 실시간 모니터링 쉘 스크립트

-- Temp 테이블스페이스 조회 스크립트

-- temp.sql
set timing off
set feedback off
set lines 1000
set pages 1000
col session_info heading "SID,Serial#" format a20
col "SIZE" format 99,999,999
col tablespace_name format a15
col module format a50

set heading off
select '------[[ 각 User Session별 사용량 ]] --------' from dual;
set heading on

select tablespace tablespace_name
     , to_char(s.sid) || ',' || to_char(s.serial#) session_info
     , s.module module
     , sum(so.blocks * 8 /1024) as "SIZE"
     , 'M' Unit
  from gv$session s, gv$sort_usage so
 where s.inst_id = so.inst_id
   and s.saddr = so.session_addr
   and s.serial# = so.session_num
   and s.status = 'ACTIVE'
 group by tablespace ,s.sid,s.serial#,module
 order by "SIZE"; 

set heading off
select '---- [[ 각 인스턴스별 Temp Tablespace 총 사용량 ]] --------' from dual;
set heading on

select inst_id
     , b.tablespace_name
     , 'Used Total Size' sessin_info
     , nvl(sum(so.blocks * 8/1024),0) as "SIZE"
     , 'M' Unit
  from gv$sort_usage so , dba_tablespaces b
 where b.tablespace_name = so.tablespace(+)
   and b.contents = 'TEMPORARY'
 group by b.tablespace_name , inst_id
   order by 1,2;

set heading off
select '---- [[ 각 Temp Tablespace별 총 사용량 ]] --------' from dual;
set heading on

select b.tablespace_name
     , 'Used Total Size' sessin_info
     , nvl(sum(so.blocks * 8/1024),0) as "SIZE"
     , 'M' Unit
  from gv$sort_usage so , dba_tablespaces b
 where b.tablespace_name = so.tablespace(+)
   and b.contents = 'TEMPORARY'
 group by b.tablespace_name , inst_id
   order by 1,2;

set heading off
select '---- [[ 각 Temp Tablespace별 총 할당량 ]] --------' from dual;
set heading on

select b.tablespace_name
     , 'Temp Total Size' sessin_info
     , trunc(sum(bytes)/1024/1024,2) as "SIZE"
     , 'M' Unit
  from dba_temp_files , dba_tablespaces b
 where b.contents = 'TEMPORARY'
   and a.tablespace_name = b.tablespace_name 
group by b.tablespace_name
   order by 1;

select '-------------------' from dual;
set heading on
set timing on
set feedback on

 

-- Undo/System/DBA 테이블스페이스 조회 스크립트

-- ts.sql
-- created by dbcafe.co.kr

set timing off
set feedback off
set lines 1000
set pages 1000
col session_info heading "SID,Serial#" format a20
col "Alloc(M)" format 99,999,999
col "Free(M)" format 99,999,999
col "Used(M)" format 99,999,999
col tablespace_name format a15

select d.tablespace_name
     , round(sum(d.bytes)/1024/1024) "Alloc(M)"  
     , round(sum(f.bytes)/1024/1024) "Free(M)"
     , round((sum(d.bytes)-sum(f.bytes))/1024/1024) "Used(M)"
     , round(nvl(sum(f.bytes),0)/sum(d.bytes) * 100,2) "Free(%)"
     , 100 - round(nvl(sum(f.bytes),0)/sum(d.bytes) * 100,2) "Used(%)"
  from (select d.tablespace_name,d.file_id,sum(d.bytes) bytes
          from dba_data_files d
         group by d.tablespace_name,d.file_id) d
     , (select d.tablespace_name ,d.file_id,sum(d.bytes) bytes 
          from dba_free_space d
         group by d.tablespace_name,d.file_id) f
 where d.tablespace_name = f.tablespace_name(+)
   and d.file_id = f.file_id(+)
   and regexp_like(d.tablespace_name,'SYSTEM|UNDO|DBA')
 group by d.tablespace_name
 order by 1

 

위 스크립트 반복 수행하는 Shell Script

-- aix 용 스크립트
#!/bin/sh
interval=10 # sleep interval in seconds
reps=100000 # ntimes
j=1         # loop variable

while [ $j -le ${reps} ] 
do
echo " ------ cycle ${j} / ${reps} ------"
#@sg.sql is user execute sql file for monitor 
sqlplus -s '/as sysdba'<<EOF
@temp.sql
@ts.sql ${j}
exit
EOF
sleep ${interval}
j=`expr ${j} + 1`
done
exit 0

 

DBA의 위키

출처 : DBCAFE.CO.KR

항상 감사합니다.