[SHELL] 쉘스크립트_sql문 실행

☎ 스크립트 내용

sql문을 shell 내부에서 수행하여 결과값 기록

 

1. 스크립트 내용

 

#transaction.sql

DATE=`date +%Y-%m-%d_%H:%M:%S`
LOG_FILE=/home/ora11g/scripts/transaction.log
program=`sqlplus -s / as sysdba <<EOF
set feedback off
set timing off

select i.instance_name
       ,sum(s.value/(86400*(sysdate - startup_time))) "tps"
from v\\$sysstat s, v\\$instance i
where s.name in ('user commits','transaction rollbacks')
group by instance_name;

exit
EOF`

echo " " >> $LOG_FILE
echo ${DATE} >> $LOG_FILE
echo "$program" >> $LOG_FILE

 

2. 결과값

 

2023-03-09_11:18:21

INSTANCE_NAME           tps
---------------- ----------
ORA11G            .01265334

2023-03-09_11:18:45

INSTANCE_NAME           tps
---------------- ----------
ORA11G           .012651641