In my workday one of the daily duty is to check how many number of records are
in a particular table in the database everyday.
Today we wanted the frequency of checking to be changed to every 30 mins.
so i came up with this logger.
It has two Files.
1. A Batch Script - Tracker.bat
2. A SQL Script - qry.sql
Tracker.bat
REM *****************************************************
REM Script to Record & Track the number of records in a table
REM **********************************************************
REM Depends on qry.sql to find the DB time and the actual record count
REM Scheduled as a windows scheduled task
REM for every 1/2 an Hour / any frequency you prefer
REM ****************************************************
@echo off
sqlplus db-username/db-password@db-name> @qry.sql
REM Example scoot/tiger@hr @qry.sql
ECHO Log Copied on %DATE% %TIME% >>out.txt
type noticount.log >>out.txt
del noticount.log
Qry.sql
SPOOL noticount.log;
select to_char (sysdate,'DD/MM/YYYY HH:MM::SS') DB_Time , count(*) No_of_Records from <YOUR_TABLE_NAME>;
SPOOL OFF;
EXIT;
Once scheduled the script creates a log file called "out.txt" with the contents like.,
Log Copied on Fri 06/23/2006 12:12:19.59
DB_TIME NO_OF_RECORDS
-------------------- -------------------
23/06/2006 02:06::09 1
Log Copied on Fri 06/23/2006 12:12:37.15
DB_TIME NO_OF_RECORDS
-------------------- -------------------
23/06/2006 02:06::27 1
You can use this scripts to monitor the database for various details just by doing some simple modifications.
i am trying now to directly write it into a excel file.., will let you all know once i do that.,
till then i hope this would make somebody's life a bit easier
(\/) Sifar
