Oracle tables Statistics

For long-term memory management, you should keep the OS statistics inside Oracle tables for analysis. Because of the transient nature of UNIX statistics, the DBA must have some facility for keeping information for long-term analysis. Otherwise, the UNIX environment changes so quickly that the DBA will not be able to diagnose a UNIX problem. The UNIX script below shows how you can capture vmstat data into an Oracle table.

#!/bin/ksh

Here's an example of a UNIX script that captures vmstat data and inserts it into an Oracle table:

#!/bin/bash

# Define the database connection string
DB_CONN_STR="user/password@oracle_sid"

# Define the Oracle table name
TABLE_NAME="VMSTAT_DATA"

# Get the current timestamp
TIMESTAMP=$(date '+%Y-%m-%d %H:%M:%S')

# Capture the vmstat data
VMSTAT_DATA=$(vmstat 1 2 | tail -1)

# Extract the individual fields from the vmstat data
READS=$(echo $VMSTAT_DATA | awk '{print $1}')
WRITES=$(echo $VMSTAT_DATA | awk '{print $2}')
MEM_FREE=$(echo $VMSTAT_DATA | awk '{print $4}')
SWAP_FREE=$(echo $VMSTAT_DATA | awk '{print $7}')

# Insert the vmstat data into the Oracle table
sqlplus -S $DB_CONN_STR <


In this script, the vmstat command is used to capture the system statistics data, and the sqlplus utility is used to insert the data into an Oracle table. The script captures the data every 1 second and takes a 2-second sample, so the output is the average of the 2-second interval.


You'll need to modify the script to match your specific Oracle database environment, including the database connection string, table name, and the fields that you want to capture. You can run this script at regular intervals using a scheduling tool, such as cron, to continuously capture vmstat data and insert it into the Oracle table.