"Can I directly plugin free format sqlplus scripts into EVNT framework?"
Yes!
Simple as that. Besides the fact that you can write your own events from the ground up (read how here) you can simply plugin any custom sqlplus script into the EVNT framework using "SQL_SCRIPT" Event:
# This event runs custom SQL*Plus scripts. Use it to plug and play your
# custom sql scripts without the need for writing your own custom events.
#
# The following steps need to be completed to "register" custom
# sql script with this event:
#
# 1. Make two copies of your existing sql script or create
# two new sql files with the following extensions:
#
# [CHK file] $EVNT_TOP/cust/sql/script_name.chk.sql
# [OUT file] $EVNT_TOP/cust/sql/script_name.out.sql
#
# 2. CHK file should return comma delimited data:
#
# -------- example ---------------
# attribute1,attribute2,attributeX
# attribute1,attribute2,attributeX
# attribute1,attribute2,attributeX
# --------- end ------------------
#
# for example you can use the following syntax to generate comma
# delimited output:
#
# SELECT
# col1
# ||','||col2
# ||','||col3
# ||','||col4
# FROM table;
#
# attribute(s) are parsed and stored in the repository -
# they are used to compare event triggers when evaluating
# their status:
#
# STATUS CONDITION
# ------- --------------------------------------------------
# OFF when check file's output has 0 lines
# ON when check file's output has >0 lines and
# previous attributes don't match current attributes
# CLEARED when check file's output has 0 lines and previous
# status was ON
#
# 3. OUT file can return output in free format as long as it's line size
# not exceeding 255 chars. Output of this script will be loaded into
# the repository and will be available on trigger output screen.
#
# REPORT ATTRIBUTES:
# -----------------------------
# custom [based on sql script run]
#
#
# PARAMETER DESCRIPTION EXAMPLE
# -------------- ------------------------------------------------ --------
# SQL_SCRIPT name of the sql script tabsp
#
# event will automatically append the following
# to the SQL_SCRIPT name:
#
# $EVNT_TOP/cust/sql/${SQL_SCRIPT}.chk.sql
# $EVNT_TOP/cust/sql/${SQL_SCRIPT}.out.sql
#
# for example if you created CHK and OUT files and
# named them:
#
# $EVNT_TOP/cust/sql/tabsp.chk.sql
# $EVNT_TOP/cust/sql/tabsp.out.sql
# simply supply "tabsp" for SQL_SCRIPT parameter
#
# SQL_PARAMS positional parameters (1 2 3 4) to your sql script
# for example if your script is
#
# select *
# from user_free_space
# where tablespace='&1'
# and bytes >= &2
#
# SQL_PARAMS could be "TOOLS 300" (no quotes)
sqlproc.sh is located in $SEEDMON:
stechkin.LMON-> . /home/oracle/admin/scripts/mon/MON.env
stechkin.LMON-> cd $SEEDMON
stechkin.LMON-> pwd
/home/oracle/admin/scripts/mon/evnt/seed/bin
stechkin.LMON-> ls -l sqlproc.sh
-rwxr-xr-x 1 oracle oinstall 4441 Oct 29 17:56 sqlproc.sh
I will demonstrate the complete procedure of registering custom sqlplus scripts using systsusr.sql - "SYSTEM Tablespace Users":
1. Place "systsusr.sql" into $EVNT_TOP/cust/sql/
stechkin.LMON-> cd $EVNT_TOP/cust/sql/
stechkin.LMON-> pwd
/home/oracle/admin/scripts/mon/evnt/cust/sql
stechkin.LMON-> ls -lta
total 20
drwxr-xr-x 2 oracle oinstall 4096 Nov 7 18:28 .
-rw-r--r-- 1 oracle oinstall 306 Nov 7 18:28 systsusr.sql
drwxr-xr-x 4 oracle oinstall 4096 Jul 24 2002 ..
stechkin.LMON-> cat systsusr.sql
set lines 85
set pages 60
col default_tablespace format a15 heading "DEFAULT TS"
col temporary_tablespace format a15 heading "TEMP TS"
ttit "USERS with default ts=SYSTEM"
select username, default_tablespace, temporary_tablespace,
to_char(created,'RRRR-MON-DD HH24:MI:SS') created
from dba_users
where default_tablespace='SYSTEM'
/
2. Test systsusr.sql
Note that all plugged in scripts are run using credentials chosen when an
event is assigned to a SID (more on this further down) just keep this in mind
when creating your scripts. In this case I am using "MON" credentials
(mon/justagate@PRGH) to test my script.
stechkin.LMON-> sqlplus mon/justagate@PRGH
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Nov 7 18:28:23 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @systsusr.sql
Fri Nov 07 page 1
USERS with default ts=SYSTEM
USERNAME DEFAULT TS TEMP TS CREATED
------------------------------ --------------- --------------- --------------------
DBSNMP SYSTEM SYSTEM 2002-NOV-17 08:59:55
MDSYS SYSTEM SYSTEM 2001-NOV-22 02:36:21
ORDPLUGINS SYSTEM SYSTEM 2001-NOV-22 02:36:21
ORDSYS SYSTEM SYSTEM 2001-NOV-22 02:36:20
OUTLN SYSTEM SYSTEM 2001-NOV-21 23:41:12
OAS_PUBLIC SYSTEM TEMP 2000-JUL-28 14:57:59
SYS SYSTEM TEMP 2000-FEB-10 22:13:36
7 rows selected.
3. Create CHK and OUT files.
OK so we know that the script runs fine under the credentials that will be
used when this event is assigned to a SID. Next lets rename the script
following the standart "script_name.out.sql" and create "CHK" file for it.
Remember that the "CHK" file is what is used to ON/OFF/CLEAR an event trigger:
STATUS CONDITION
------- --------------------------------------------------
OFF when check file's output has 0 lines
ON when check file's output has >0 lines and
previous attributes don't match current attributes
CLEARED when check file's output has 0 lines and previous
status was ON
stechkin.LMON-> pwd
/home/oracle/admin/scripts/mon/evnt/cust/sql
stechkin.LMON-> mv systsusr.sql systsusr.out.sql
stechkin.LMON-> cp systsusr.out.sql systsusr.chk.sql
stechkin.LMON-> ls -l syst*
-rw-r--r-- 1 oracle oinstall 342 Nov 7 18:45 systsusr.chk.sql
-rw-r--r-- 1 oracle oinstall 342 Nov 7 18:37 systsusr.out.sql
Next lets modify systsusr.chk.sql to return comma delimited attributes:
stechkin.LMON-> cat systsusr.chk.sql
select username||','||default_tablespace||','||temporary_tablespace||','||
to_char(created,'RRRR-MON-DD HH24:MI:SS')
from dba_users
where default_tablespace='SYSTEM'
/
4. Test CHK script
stechkin.LMON-> sqlplus mon/justagate@PRGH
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Nov 7 18:48:09 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @systsusr.chk.sql
USERNAME||','||DEFAULT_TABLESPACE||','||TEMPORARY_TABLESPACE||','||TO_CHAR(CREAT
--------------------------------------------------------------------------------
DBSNMP,SYSTEM,SYSTEM,2002-NOV-17 08:59:55
MDSYS,SYSTEM,SYSTEM,2001-NOV-22 02:36:21
ORDPLUGINS,SYSTEM,SYSTEM,2001-NOV-22 02:36:21
ORDSYS,SYSTEM,SYSTEM,2001-NOV-22 02:36:20
OUTLN,SYSTEM,SYSTEM,2001-NOV-21 23:41:12
OAS_PUBLIC,SYSTEM,TEMP,2000-JUL-28 14:57:59
SYS,SYSTEM,TEMP,2000-FEB-10 22:13:36
7 rows selected.
Don't worry about the heading and feedback of how many rows are returned it
will all be taken care of by the framework when the "CHK" file is called.
Now that the scripts are in place next step is to define threshold and setup threshold parameters.
The following will help you understand how events, thresholds and threshold parameters work in EVNT repository:
In the case of SQL_SCRIPT event the threshold is the code name for custom SQL script whilst threshold parameter is the actual sql script name.
The following navigation path will take you "New Event Threshold" Screen:
Event Module ->
Thresholds ->
SQL_SCRIPT ->
New Threshold:
Enter the following: Threshold: SYSTEM_DEFTS Description: Reports users with default tablespace set to SYSTEM Click "Create"
Next lets define the threshold parameters where we can tell the framework what sql script to use when this event runs. Click "SYSTEM_DEFTS" link on the screen above, then "New Parameter" button.
Enter the following: Parameter: SQL_SCRIPT Value: systsusr Status: "Active"Click "Create"
Lets recap what we've done so far:
That's all there is to it. Custom SQL script has been plugged into EVNT framework and is ready to be used.
In this section we will assign our custom sql script to a SID - navigate to "Event Assignments" screen using the following links:
Event Module ->
Assigments ->
[Next to a target host select View or Create] ->
Press "Create New" button on the "Control Panel" screen ->
New Event Assigment form will open (choose SQL_SCRIPT as Event and SYSTEM_DEFTS as Threshold):
Press "Create" button.
You will be taken back to the control panel. Note that "SYSTEM_DEFTS" event assignment have triggered (PND column shows 1 and cell is marked red):
Click on the "1" link in the "PND" column next to "SYSTEM_DEFTS" event assignment, then click on the trigger id in the "TID" column:
There you have it! Custom sqlplus script's output shows up right in the trigger navigator. Note that "[ALL VALUES] (76379) Trigger Attributes:" section show the attributes of "CHK" file, while "(76379) Trigger Output:" section shows output of the "OUT" script.