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
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
