doubt in audit trail , oracle 8i

[Nirmalkumar]

Hi,

i need to get a report of users and his sqlstmts issued on database using audit trail and send a report thru mail.my database is running on oracle 8i.

can some one help me out.

Thanks,
Nirmal.

[Yogeeraj_1]

hi,

You can query the sharedpool online for the different sql statements being executed on the database at one particular interval.

One example query would be:
SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.prev_sql_addr, s.prev_hash_value,
s.prev_sql_id, s.prev_child_number, s.plsql_entry_object_id,
s.plsql_entry_subprogram_id, s.plsql_object_id,
s.plsql_subprogram_id, s.module, s.module_hash, s.action,
s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.service_name, s.sql_trace, s.sql_trace_waits, s.sql_trace_binds
FROM v$session s
WHERE (EXISTS (
SELECT 1
FROM v$session xxx
WHERE s.username = xxx.username
AND s.program = xxx.program
AND s.SID <> xxx.SID
AND s.serial# <> xxx.serial#)
)
AND ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND')
)
AND ((UPPER (s.username) LIKE '&USER'))
ORDER BY "PROGRAM", ownerid


Beware of performance implications. Now the frequency that you run this for audit purposes is another issue.

Otherwise you can enable SQLTRACE at the database level and then query the generated the trace files. Here also beware of the HUGE number of trace files that would be generated.

just my views...

PS. Why do you need to do this?

kind regards
yogeeraj

[Nirmalkumar]

Hi yogeeraj,

Thanks for your reply....

my client need to monitor a database about who are all login in to the database and also sql statements issued on the database.
so i have planned to use auditing.In my database parameter 'audit_trail' is enabled to 'DB'.
so all auditing records are available in sys.aud$ table.i can get username and timestamp from sys.aud$ table.But iam strucked how to get the sql statement.
For to get sql statement what are the things i need to do...

Version of my database is 8i

Kindly advice me...


Thanks
Nirmal.

[Yogeeraj_1]

hi,

you cannot get this information directly. You will need to create a trigger on the aud$ table.

e.g.
create or replace trigger acc_trigger
after insert on system.aud$ for each row
when (new.action# = 3)
declare
v_conc_id number;
begin
v_conc_id := 99;
insert into mytab
select :new.userid,
:new.timestamp#,
:new.logoff$time,
ss.schemaname,
ss.osuser,
ss.process,
ss.machine,
ss.terminal,
ss.program,
ss.type,
:new.logoff$lread,
:new.logoff$pread,
st.sid,
ss.audsid,
st.value,
sq.sql_text
from V$STATNAME S, V$SESSTAT ST, V$SESSION SS, v$sql sq
where S.STATISTIC# = ST.STATISTiC# AND ST.SID = SS.SID
and :new.sessionid = SS.AUDSID
and ss.sql_hash_value=sq.hash_value
and S.NAME='CPU used by this session';
end;
/


hope this helps!
kind regards
yogeeraj