sharkdba_at_yahoo.com said...
> We have a weblogic application which runs on top of oracle database
> (8.1.7 on Solaris to be more exact). 1 of the tables looses some
> records, usually overnight, and I need to find out why.
>
> This is a rather large application that got inherited from a different
> software company, and most likely there is a process, stored procedure,
> script, job, whatever that's causing some havoc. The chance that someone
> manually is deleting these records is very low.
>
> I'm thinking of creating a "for each row" trigger that would log any
> activity on this table. Through the trigger I can store the timestamp
> and column values, but can I access what called the statement?
>
> If there is a stored procedure, oracle job, or even a sql+ console,
> that calls a query, does oracle know this? In other words, would
> the trigger know where the statement was coming from? And if yes,
> how can I access this information? Even a process ID, anything would
> be helpful.
>
> Any other ideas on how a change to a table can be tracked?
>
Yes, writing a trigger is the way to go (well, one way at least). Since
the trigger lives in the database, it will fire for each row updated or
deleted (if you specify you want to watch for those changes). That's the
advantage to a trigger. No matter what client someone uses to get to the
database, the trigger will fire when something in the monitored table
changes.
Your problem will be getting something useful out of WebLogic (WL). At
my job, all I see is: username = schema owner to which WL connected (eg,
colo_db6), O/S user = WL userid (eg, everest), O/S process id = same
number for all connections (eg, 620), machine = WL app server name (eg,
watermelon), program = 'JDBC Thin Client'. But since the WL app server
maintains persistent connections, I could use the session id to "catch"
the culprit.
--
/Karsten
DBA > retired > DBA
Received on Wed Mar 12 2003 - 15:35:22 CST
