Audit Logon above 9i

1. Enable audit. Set the parameter to
audit_trail=db (or db,extended)
2. Restart the database instance to enable the audit settings.
3. Set up audit for session:
audit session whenever successful;
4. After a relevant period of time, check the DBA_AUDIT_SESSION view, in the documentation

LOGOFF_LREAD Logical reads for the session
LOGOFF_PREAD Physical reads for the session
LOGOFF_LWRITE Logical writes for the session
SESSION_CPU Amount of CPU time used by each Oracle session

A query example:

select username,sum(logoff_lread) "TOTAL READS",
sum(logoff_pread) "TOTAL PHYS READS",
sum(logoff_lwrite) "TOTAL WRITES",
sum(session_cpu) "TOTAL CPU",
sum(logoff_pread)/count(*) "READS/SESSIO",
sum(logoff_lwrite)/count(*) "PHYS_READS/SESSION",
sum(logoff_lwrite)/count(*) "WRITES/SESSION",
sum(session_cpu)/count(*) "CPU/SESSION"
from dba_audit_session group by username;

The range of values can be restricted using the TIMESTAMP and/or LOGOFF_TIME columns (which are the logon and logoff interval ends) to have the results for a specific period of time.

[oracle@rh2 ~]$
[oracle@rh2 ~]$ sqlplus maclean/fdsfds

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 8 12:48:05 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


select * from dba_audit_session
OS_USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
USERHOST
--------------------------------------------------------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------------------------------------------------------------------
TIMESTAMP ACTION_NAME                  LOGOFF_TI LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK                              SESSIONID
--------- ---------------------------- --------- ------------ ------------ ------------- ---------------------------------------- ----------
RETURNCODE CLIENT_ID                                                        SESSION_CPU
---------- ---------------------------------------------------------------- -----------
EXTENDED_TIMESTAMP                                                          PROXY_SESSIONID GLOBAL_UID                       INSTANCE_NUMBER
--------------------------------------------------------------------------- --------------- -------------------------------- ---------------
OS_PROCESS
----------------
oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGOFF                       08-JUL-09          655           51            16 0                                            960800
         0                                                                            9
08-JUL-09 12.45.42.813460 PM +08:00                                                                                                        0
6159

oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960801
      1017
08-JUL-09 12.46.17.938293 PM +08:00                                                                                                        0
6168

oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960802
      1017
08-JUL-09 12.48.05.234442 PM +08:00                                                                                                        0
6176

oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960803
         0
08-JUL-09 12.48.40.687569 PM +08:00                                                                                                        0
6181

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569