Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Log Miner Question

Log Miner Question

From: Sam Bootsma <>
Date: Fri, 20 May 2005 09:36:43 -0400
Message-ID: <>


Last week we had a problem in our production environment due to a dropped Oracle package that invalidated a trigger. Our Development team was able to research the problem, then recreate the dropped package and compile the trigger. Nobody admitted to dropping the Oracle package so I used Log Miner to confirm that the package had been dropped, what day and time it was dropped, the Oracle user logged in, and the OS user logged in. The details immediately below come from v$logmnr_contents (select username, session#, serial#, to_char(timestamp,'yy-mon-dd hh:mi:ss') "time", session_info, sql_redo .....)  

SYSTEM                                222       2036 05-may-16 03:04:12

login_username=SYSTEM client_info= OS_username=banner Machine_name=TCC6C402 OS_t

erminal= OS_process_id=82044 OS_program name=sqlplus_at_TCC6C402 (TNS V1-V3)

drop package BANINST1.WSKSTST;  

Note that the OS process id is included in session_info column of this virtual table. The problem is, I am not able to trace the OS process_id back to a user workstation. I requested a log of user logins from our AIX Administrator and he returned with a report that shows the Unix user id, terminal (eg. pts/2), the name or IP address connecting to the Unix box, and the time period of the connection. However, the log did not include Unix Process ID so I am not able to link back to the information provided by Log Miner. The System Administrator is currently following up with the vendor on how Unix Process ID can be obtained. Can anybody tell me if there is another way to trace the drop command back to a workstation? For example, does Log Miner store the terminal type in any of its tables?  

We are running Oracle on AIX Unix 5.  

Our other Oracle DBA (who was dropping packages an hour earlier as part of cleanup tasks) is telling everybody this is a security breach. But I think it is much more likely to be human error.  


Sam Bootsma

George Brown College <>

416-415-5000 x4933  

Received on Fri May 20 2005 - 10:45:30 CDT

Original text of this message