Gathering Data from a Query

The following set of scripts will run a simple query against a database and add the results to a file. To keep the file from getting too long, when a new day starts a new file will be created to store the results.

Query.sql:

SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET SERVEROUTPUT ON
SELECT COUNT(*) FROM v$SESSION;
EXIT

runQuery.sh:

#!/bin/sh
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
TODAY=$(date +%Y-%m-%d)

sqlplus -S perfstat/perfstat @query.sql >> data$TODAY.txt

Now run "crontab -e" and add the following line:

0,15,30,45 * * * * /directory/runQuery.sh

For more information on setting up Cron jobs see Scheduling a Cron Job.