General
===============================
Example of loging activity on Instance by several processes:
Log Sessions.
Log Processes.
Use Logon trigger, writing all login, logout, and basic session activity to a log file
===============================
Current Sessions Usage
===============================
SELECT schemaname, COUNT(*) FROM V$SESSION
GROUP BY schemaname
ORDER BY COUNT(*) DESC;
===============================
Log Sessions and Processes
===============================
Files:
SH_PROCESSES_HIST.sql
SH_SESSIONS_HIST.sql
MONITOR_DB_PKG.sqlSH_SESSIONS_HIST.sql
SH_PROCESSES_HIST.sql
-- Create table
create table SH_PROCESSES_HIST
(
run_date DATE not null,
schema_name VARCHAR2(30) not null,
processes_num NUMBER(9) not null
)
tablespace IGT_TABLE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SH_PROCESSES_HIST
add constraint SH_PROCESSES_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
using index
tablespace IGT_INDEX
SH_SESSIONS_HIST.sql
-- Create table
create table SH_SESSIONS_HIST
(
run_date DATE not null,
schema_name VARCHAR2(30) not null,
sessions_num NUMBER(9) not null
)
tablespace IGT_TABLE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SH_SESSIONS_HIST
add constraint SH_SESSIONS_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
using index tablespace IGT_INDEX;
MONITOR_DB_PKG.sql-- Create table
create table SH_PROCESSES_HIST
(
run_date DATE not null,
schema_name VARCHAR2(30) not null,
processes_num NUMBER(9) not null
)
tablespace IGT_TABLE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SH_PROCESSES_HIST
add constraint SH_PROCESSES_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
using index
tablespace IGT_INDEX
SH_SESSIONS_HIST.sql
-- Create table
create table SH_SESSIONS_HIST
(
run_date DATE not null,
schema_name VARCHAR2(30) not null,
sessions_num NUMBER(9) not null
)
tablespace IGT_TABLE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SH_SESSIONS_HIST
add constraint SH_SESSIONS_HIST_PK primary key (RUN_DATE, SCHEMA_NAME)
using index tablespace IGT_INDEX;
CREATE OR REPLACE PACKAGE BODY MONITOR_DB_PKG IS
PROCEDURE LOG_SESSIONS IS
BEGIN
INSERT INTO SH_SESSIONS_HIST (run_date, schema_name, sessions_num)
SELECT SYSDATE, schemaname, count(*)
FROM ( SELECT *
FROM V$SESSION
WHERE paddr IN (
SELECT addr
FROM V$PROCESS
WHERE background IS NULL
AND username = 'oracle'
AND program LIKE 'oracle@aut-tma%')
)
GROUP BY schemaname;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO SH_SESSIONS_HIST (run_date, schema_name, sessions_num)
VALUES (SYSDATE,'N/A',0);
COMMIT;
END LOG_SESSIONS;
------------------------------------------------
PROCEDURE LOG_PROCESSES IS
BEGIN
INSERT INTO SH_PROCESSES_HIST (run_date, schema_name, processes_num)
SELECT SYSDATE, schema_name, COUNT(*)
FROM (
SELECT SESSIONS.schemaname AS schema_name,
PROCESSES.spid as process
FROM V$PROCESS PROCESSES,
V$SESSION SESSIONS
WHERE PROCESSES.program like 'oracle@aut-tma%'
AND PROCESSES.background IS NULL
AND PROCESSES.addr=SESSIONS.paddr
)
GROUP BY schema_name ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO SH_PROCESSES_HIST (run_date, schema_name, processes_num)
VALUES (SYSDATE,'N/A',0);
COMMIT;
END LOG_PROCESSES;
------------------------------------------------
PROCEDURE LOG_ACTIVITY IS
BEGIN
LOG_SESSIONS;
LOG_PROCESSES;
EXCEPTION
WHEN OTHERS THEN
NULL;
END LOG_ACTIVITY;
------------------------------------------------
END MONITOR_DB_PKG;
===============================PROCEDURE LOG_SESSIONS IS
BEGIN
INSERT INTO SH_SESSIONS_HIST (run_date, schema_name, sessions_num)
SELECT SYSDATE, schemaname, count(*)
FROM ( SELECT *
FROM V$SESSION
WHERE paddr IN (
SELECT addr
FROM V$PROCESS
WHERE background IS NULL
AND username = 'oracle'
AND program LIKE 'oracle@aut-tma%')
)
GROUP BY schemaname;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO SH_SESSIONS_HIST (run_date, schema_name, sessions_num)
VALUES (SYSDATE,'N/A',0);
COMMIT;
END LOG_SESSIONS;
------------------------------------------------
PROCEDURE LOG_PROCESSES IS
BEGIN
INSERT INTO SH_PROCESSES_HIST (run_date, schema_name, processes_num)
SELECT SYSDATE, schema_name, COUNT(*)
FROM (
SELECT SESSIONS.schemaname AS schema_name,
PROCESSES.spid as process
FROM V$PROCESS PROCESSES,
V$SESSION SESSIONS
WHERE PROCESSES.program like 'oracle@aut-tma%'
AND PROCESSES.background IS NULL
AND PROCESSES.addr=SESSIONS.paddr
)
GROUP BY schema_name ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO SH_PROCESSES_HIST (run_date, schema_name, processes_num)
VALUES (SYSDATE,'N/A',0);
COMMIT;
END LOG_PROCESSES;
------------------------------------------------
PROCEDURE LOG_ACTIVITY IS
BEGIN
LOG_SESSIONS;
LOG_PROCESSES;
EXCEPTION
WHEN OTHERS THEN
NULL;
END LOG_ACTIVITY;
------------------------------------------------
END MONITOR_DB_PKG;
Logon Trigger
===============================
Files:
permission.sql
AUD_ACTIVITY.sql
LOGON_AUDIT_TRIGGER.sql
LOGOFF_AUDIT_TRIGGER.sql
permission.sql
CREATE USER MONITOR_USER IDENTIFIED BY MONITOR_PASS;
GRANT CONNECT, RESOURCE TO MONITOR_USER;
GRANT SELECT ANY DICTIONARY TO MONITOR_USER;
GRANT UNLIMITED TABLESPACE TO MONITOR_USER;
GRANT ADMINISTER DATABASE TRIGGER TO MONITOR_USER;
AUD_ACTIVITY.sql
-- Create table
create table AUD_ACTIVITY
(
session_id NUMBER(10) not null,
session_sid NUMBER(10) not null,
session_serial_num VARCHAR2(40) not null,
user_id VARCHAR2(120),
process VARCHAR2(24),
session_host VARCHAR2(64),
session_os_user VARCHAR2(30),
session_schema_name VARCHAR2(30),
session_program VARCHAR2(48),
session_module VARCHAR2(64),
session_action VARCHAR2(64),
last_program VARCHAR2(48),
last_action VARCHAR2(64),
last_module VARCHAR2(64),
logon_day DATE,
logon_time VARCHAR2(40),
logoff_day DATE,
logoff_time VARCHAR2(40),
elapsed_minutes NUMBER(8)
)
tablespace IGT_TABLE;
-- Create/Recreate indexes
create index AUD_ACTIVITY_IND1 on AUD_ACTIVITY (USER_ID, LOGON_DAY) tablespace IGT_INDEX;
-- Create/Recreate primary, unique and foreign key constraints
alter table AUD_ACTIVITY
add constraint AUD_ACTIVITY_PK primary key (SESSION_ID, SESSION_SID, SESSION_SERIAL_NUM) using index tablespace IGT_INDEX;
LOGON_AUDIT_TRIGGER.sql
CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRIGGER AFTER LOGON ON DATABASE
DECLARE
v_session_id NUMBER(10):=0;
v_session_sid V$SESSION.sid%TYPE;
v_session_serial V$SESSION.serial#%TYPE;
v_session_paddr V$SESSION.paddr%TYPE;
v_session_module V$SESSION.module%TYPE;
v_session_program V$SESSION.program%TYPE;
v_session_action V$SESSION.action%TYPE;
v_session_host V$SESSION.machine%TYPE;
v_session_os_user V$SESSION.osuser%TYPE;
v_schema_name V$SESSION.schemaname%TYPE;
v_process V$PROCESS.spid%TYPE;
BEGIN
v_session_id := sys_context('USERENV','SESSIONID');
IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'
THEN
BEGIN
SELECT sid, serial#, paddr, module, program, action, machine, osuser, schemaname
INTO v_session_sid, v_session_serial, v_session_paddr, v_session_module, v_session_program, v_session_action, v_session_host, v_session_os_user, v_schema_name
FROM V$SESSION
WHERE audsid=v_session_id
AND ROWNUM<2;
EXCEPTION
WHEN OTHERS THEN
v_session_sid:=9999999;
v_session_serial:='9999999';
v_session_module := 'N/A';
v_session_program:= 'N/A';
v_session_host := 'N/A';
v_session_os_user := 'N/A';
v_schema_name := 'N/A';
v_session_action := 'N/A';
END;
BEGIN
SELECT spid
INTO v_process
FROM V$PROCESS
WHERE ADDR = v_session_paddr;
EXCEPTION
WHEN OTHERS THEN
v_process := 0;
END;
INSERT INTO AUD_ACTIVITY
(
session_id,
session_sid,
session_serial_num,
user_id,
process,
session_host,
session_os_user,
session_schema_name,
session_program,
session_module,
session_action,
last_program,
last_action,
last_module,
logon_day,
logon_time,
logoff_day,
logoff_time,
elapsed_minutes
)
VALUES
( v_session_id,
v_session_sid,
v_session_serial,
USER,
v_process,
v_session_host,
v_session_os_user,
v_schema_name,
v_session_program,
v_session_module,
v_session_action,
NULL,
NULL,
NULL,
SYSDATE,
TO_CHAR(SYSDATE,'HH24:MI:SS'),
NULL,
NULL,
NULL);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
LOGOFF_AUDIT_TRIGGER.sql
CREATE OR REPLACE TRIGGER LOGOFF_AUDIT_TRIGGER BEFORE LOGOFF ON DATABASE
DECLARE
v_session_id NUMBER(10):=0;
v_last_module V$SESSION.module%TYPE;
v_last_program V$SESSION.program%TYPE;
v_last_action V$SESSION.action%TYPE;
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
v_session_id := sys_context('USERENV','SESSIONID');
IF v_session_id != 0 AND USER NOT IN ('SYS','SYSTEM','SITEBACKUP','SHDAEMON') AND USER = 'MONITOR_USER'
THEN
BEGIN
SELECT action, program, module
INTO v_last_action, v_last_program, v_last_module
FROM V$SESSION
WHERE audsid = v_session_id
AND rownum < 2;
EXCEPTION
WHEN OTHERS THEN
v_last_action := NULL;
v_last_program := NULL;
v_last_module := NULL;
END;
UPDATE AUD_ACTIVITY
SET last_action = v_last_action,
last_program = v_last_program,
last_module = v_last_module,
logoff_day = sysdate,
logoff_time = to_char(sysdate, 'hh24:mi:ss'),
elapsed_minutes = round((sysdate - logon_day) * 1440)
WHERE session_id = v_session_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
