Re: How create a trigger-like procedure on a SYS table???

"deejay" <djuravel_at_dgny.com> wrote in message news:f7b19882.0109050546.6e46c8b3_at_posting.google.com...
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:<tpa98g9smpthd0_at_news.demon.nl>...
> > "deejay" <djuravel_at_dgny.com> wrote in message
> > news:f7b19882.0109040859.2801b08_at_posting.google.com...
> > > I need something that will act like a trigger on a built in system
> > > table or view. Upon the update (insertion or deletion) of the built in
> > > view V$SESSION I need to track the change and notify another
> > > procedure. I tried creating a trigger on the V$SESSION table but since
> > > it belongs to SYS, it's not allowed. How can I get around this?
> >
> > You can not.
> > Please describe in detail which havoc you are up to create.
> > I'm sure there are other ways.
> > Also *always*, *always*, *always* post the *version and platform* you
are
> > using if you are requesting help.
> > Too many people here expect the people replying here
> > are a) clairvoyant b) keeping track of the sw everyone is using.
> > Both assertions don't hold true in my case.
> >
> > In Oracle 8i you could quite easily create an after logon trigger.
> >
> > Regards,
> >
> > Sybrand Bakker, Senior Oracle DBA
>
> Please accept my apologies, Mr. Bakker, for the lack of detail in my
> question. I'm glad you responded despite that. Ok, here goes. I'm
> working with an
> Oracle 8i database on a Solaris 7 platform. (I'm pretty new at this
> so please excuse my ignorance or misuse of terminology.) There are
> many client programs that log onto the database. (These client
> programs are written in java using jdbc.)For some reason, occasionally
> the network connection between client and database server goes down.
> My task is to notify some other process at the point that the network
> connection is lost that it went down. I also need to get the session
> id of the session that went down. My idea was to put a trigger on the
> v$session table so when a row is deleted from there (either due to a
> loggout or a lost connection), the trigger should call some other
> procedure that I will create to do the task that it must do. I hope
> this is clear and I eagerly await your response.
> Thanx.

The audit mechanism is present to do that. You need to enable by changing/adding the init<sid>.ora parameter audit_trail
and set it to db
(So you need to edit $ORACLE_HOME/dbs/init<sid>.ora with vi and include audit_trail = db)
Then you need to issue the
audit connect command
Further details are provided in the Sql reference manual and the Oracle Server Administrators manual.
The audit command will track the logon time, the logoff time, and the error present when the session was disconnected, if any.

Personally on Solaris, I would make sure sqlnet tracing is enabled, and have a batch job grep for error messages in the sqlnet trace and log files. This will definitely result in way less overhead. Client disconnects is a fact of life: many people just shutdown the pc and go home without disconnecting from Oracle. I think you will go nuts when you are going to pay attention to every individual occurence. Learn to live with it. If it is really important, you will get complaints soon enough. But then again I'm working in the ICT sector for 15 years.

Regards,

Sybrand Bakker, Senior Oracle dBA Received on Wed Sep 05 2001 - 18:33:41 CEST