Oracle 8i – How to Run Query Every 5 Minutes

oracleoracle-8ischeduled-tasks

Please find the below query:

select count(*) from v$session;

This has to be run on the database every 5 minutes. What is the right way to achieve this?

The query has to be run every 5 minutes on the same sql*plus screen. Instead of pressing "/" every minute, I wanted to understand if there is a way.

Database/Server details:

SQL> select * from v$version;
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

SQL> !uname -a
HP-UX ap12121 B.11.11 U 9000/800 ap12121 unlimited-user license

Best Answer

I don't know of a way to do what you're asking from within sqlplus, but you can combine a shell script with the "watch" command to achieve something close to what you're looking for:

Create a simple shell script (sessions.sh) to call sqlplus and run the query:

#!/bin/sh
sqlplus username/password << EOF
select count(*) from v$session;
EOF

Then use the watch command (or HP-UX equivalent, assuming there is one) to execute the script:

watch -n 300 sessions.sh

Or use cron to schedule the script to run every 5 minutes and direct the output to a log file:

crontab:
0/5 * * * * sessions.sh >> sessions.log

then tail the log file:

tail -f sessions.log
Related Question