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
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.
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;
[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
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1276787