Re: Track down where SQL is being executed from

On Jul 9, 12:10 pm, Jeremy Smith <godto..._at_hotmail.com> wrote:
> Yes thanks for the fast response,
>
> I checked there and the the SID is gone. Should have added that the
> SQl only runs around 12 mid night. I will login tonight and see if I
> can catch it in the session view.
>
> Thanks

Depending upon which release of Oracle you're using you may be able to implement connection auditing to track such logins; connect as SYS and run the following statements:

--
-- Create the connection log table
--

create table stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(50),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(48),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8),
elapsed_seconds number(8)
)
tablespace tools;

--
-- Create the logon trigger to populate the audit trail
--
create or replace trigger logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
if user <> 'SYS' then
insert into stats$user_log
( user_id ,
session_id ,
host ,
last_program ,
last_action ,
last_module ,
logon_day ,
logon_time ,
logoff_day ,
logoff_time ,
elapsed_minutes,
elapsed_seconds)
values
( user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null,
null);
end if;
END;
/

create or replace trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
DECLARE
lo_dt date:=sysdate;
BEGIN
-- ***************************************************
-- Update the user record
--
-- Set last_action, last_program, last_module,
-- logoff day and time and total minutes connected
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid),
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid),
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid),
logoff_day = lo_dt,
logoff_time = to_char(lo_dt, 'hh24:mi:ss'),
elapsed_minutes = round((lo_dt - logon_day)*1440),
elapsed_seconds = round((lo_dt - logon_day)*86400)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/

After the user has logged on you should be able to query stats
$user_log for host information:

select host
from stats$user_log
where logoff_day is null;

Of course you may have several entries meeting this criteria; you
could also find the username for the session in question and filter
the output further.


David Fitzjarrell