Recording query output in a self contained shell script...

General form

#!/bin/bash
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=orclcdb1
export PATH=$ORACLE_HOME/bin:$PATH

while [ true ]
do
  sqlplus -s / as sysdba  >> output.txt << EOF
SELECT * FROM DUAL;
EXIT
EOF
  sleep 10
done

Session Serialization Example

#!/bin/bash
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=orclcdb1
export PATH=$ORACLE_HOME/bin:$PATH

while [ true ]
do
  sqlplus -s / as sysdba >> output.txt << EOF
SET FEEDBACK OFF
SET PAGESIZE 0
column username format a20
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD.HH24:MI') as querytime,
       s.sid,
       s.serial#,
       s.username,
       s.machine,
   CASE BITAND(t.flag, POWER(2, 28))
      WHEN 0 THEN 'READ COMMITTED'
      ELSE 'SERIALIZABLE'
   END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr
WHERE s.sid <> sys_context('USERENV', 'SID')  AND s.USERNAME IS NOT NULL;
EXIT
EOF
  sleep 5
done

Add new comment