Oracle of Nashville

Shell scripting can be an effective tool for monitoring Oracle databases because it allows you to automate tasks and perform checks on a regular basis. Here is a Bourne shell script that you can use to monitor Oracle database sessions and send an email alert to admin@domain.com when the number of sessions is greater than 100: #!/bin/sh # Query to count the number of Oracle database sessions SESSION_COUNT=`sqlplus -S << EOF SELECT COUNT(*) FROM v\\$session WHERE type = 'USER'; EOF` # Check if the number of sessions is greater than 100 if [ $SESSION_COUNT -gt 100 ] then # Send an email alert to admin@domain.com mail -s "Oracle Session Alert" admin@domain.com << EOF The number of Oracle database sessions is currently $SESSION_COUNT, which is greater than the threshold of 100. Please check the database and take necessary action. EOF fi This script uses the sqlplus command to execute a SQL query that counts the number of user sessions in ...

Here is a SQL script that you can use to monitor Oracle database sessions: -- Query to monitor Oracle database sessions SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.osuser, s.process, s.logon_time FROM v$session s WHERE s.type = 'USER' ORDER BY s.logon_time ASC; This script queries the v$session view, which contains information about all the sessions currently connected to the database. The WHERE clause filters the rows to show only user sessions, and the ORDER BY clause sorts the results by logon time in ascending order. The columns in the v$session view include: sid : the session ID of the session serial# : the serial number of the session username : the username of the user connected to the session status : the status of the session (e.g. ACTIVE, INACTIVE) machine : the name of the machine from which the session is connected program : the name of the program connected to the session osuser : the operating system user associated with the ses...

Here's a great resource for running Oracle's AWR report manually from the SQL prompt. http://oraclefunda.wordpress.com/2009/10/29/how-to-create-awr-report-manually/