Oracle Database Administration: The Essential Refe

Example Script to Copy and Summarize Session Information

The following script is used in a production database to summarize connection information on a daily basis. We include the SQL statements to create the summary table. However, the CREATE TABLE and CREATE INDEX statements obviously do not need to be executed each day.

CREATE TABLE system.dba_audit_session_daily
(os_username varchar2(255),
username varchar2(30),
userhost varchar2(255),
terminal varchar2(255),
timestamp date,
sessions number,
elapse_time number,
logoff_lread number,
logoff_pread number,
logoff_lwrite number)
TABLESPACE tools
STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0);

CREATE INDEX system.dba_audit_session_daily_i
ON dbsa.dba_audit_session_daily (timestamp)
TABLESPACE tools
STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0)
/
INSERT INTO system.dba_audit_session_daily
(os_username,username,userhost,terminal,timestamp,sessions,elapse_time,
logoff_lread,logoff_pread,logoff_lwrite)
SELECT os_username,username,userhost,terminal,TRUNC(timestamp),COUNT(*),
SUM(logoff_time-timestamp),SUM(logoff_lread),SUM(logoff_pread),
SUM(logoff_lwrite)
FROM dba_audit_session
WHERE action_name IN ('LOGOFF','LOGOFF BY CLEANUP')
AND logoff_time < trunc(sysdate)
GROUP BY os_username,username,userhost,terminal,TRUNC(timestamp);

You can change the roll-up period by changing the TRUNC operation. Note that we are only copying rows corresponding to sessions that have been terminated. Any session that was started on the previous day but is still ...