Print Friendly, PDF & Email
A way to catch and store every single SESSION ever connected to a Standard Edition database. Creation SESSION HISTORY snapshots for later analysis.

NEEDS
How can I get historical session information in a Standard Edition database?

GOAL
The goal of this note is to invent a method for storing and getting the HISTORICAL INFORMATION about the sessions ever connected in past in an Oracle Database. As a database on the Standard Edition does not provide this kind of historical information (as Enterprise Edition does), it was highly required to create something similar.

Without a doubt, it is not the best approach to achieve that but this approach for storing and getting historical information could be enough for some cases.

RELATED
DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION
CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS

SOLUTION

TABLESPACE

Create a dedicated tablespace for storing historical session information, for instance with the SESSHIS name

CREATE TABLESPACE SESSHIS DATAFILE '/d00/oracle/oradata/meridb2/sesshis_001.dbf' 
size 100M autoextend on next 500 maxsize unlimited;

You might need to add more datafiles into your tablespace in your specific case. The more workload in a database the largest tablespace would be.

USER
-- DROP USER SESSHIS CASCADE;

CREATE USER SESSHIS IDENTIFIED BY SESSHIS DEFAULT TABLESPACE SESSHIS;
GRANT  UNLIMITED TABLESPACE TO SESSHIS;
GRANT  CONNECT,RESOURCE TO SESSHIS;
GRANT  CREATE VIEW TO SESSHIS;
GRANT  CREATE JOB TO SESSHIS;
GRANT  SELECT  ON V_$SESSION TO SESSHIS;
GRANT  SELECT  ON V_$SQL TO SESSHIS;
GRANT  SELECT  ON DBA_SCHEDULER_JOBS TO SESSHIS;
GRANT  SELECT  ON DBA_SEGMENTS TO SESSHIS;
GRANT  SELECT  ON DBA_SCHEDULER_RUNNING_JOBS TO SESSHIS;
GRANT  EXECUTE ON DBMS_LOCK TO SESSHIS;

Connect to the database as the formerly created user

SQL> CONNECT SESSHIS/SESSHIS
Connected.
SQL> SHO USER
USER is "SESSHIS"
TABLE AND INDEX

A table for storing historical session information

--DROP TABLE SESSHIS.SESSIONS_HISTORY;
--DROP INDEX SESSHIS.SESSIONS_HISTORY_IDX_001;

CREATE TABLE SESSHIS.SESSIONS_HISTORY (
SNAPSHOT_TIMESTAMP DATE,
USERNAME VARCHAR2(100),
SID NUMBER,
SERIAL# NUMBER,
STATUS VARCHAR2(8),
EVENT VARCHAR2(64),
WAIT_CLASS VARCHAR2(64),
LAST_CALL_ET NUMBER,
SECONDS_IN_WAIT NUMBER,
WAIT_TIME_MICRO NUMBER,
TIME_REMAINING_MICRO NUMBER,
SQL_TRACE VARCHAR2(8),
SQL_TRACE_BINDS VARCHAR2(5),
SQL_TRACE_PLAN_STATS VARCHAR2(10),
MACHINE VARCHAR2(64),
PORT NUMBER,
PROGRAM VARCHAR2(48),
SQL_ID VARCHAR2(13),
SQL_CHILD_NUMBER NUMBER,
PREV_SQL_ID VARCHAR2(13),
PREV_CHILD_NUMBER NUMBER,
MODULE VARCHAR2(64),
ACTION VARCHAR2(64),
CLIENT_INFO VARCHAR2(64),
LOGON_TIME DATE,
BLOCKING_SESSION NUMBER);

CREATE INDEX SESSHIS.SESSIONS_HISTORY_IDX_001 ON SESSHIS.SESSIONS_HISTORY(SNAPSHOT_TIMESTAMP);

PROCEDURES

A procedure for gathering and storing historical session information into the SESSHIS.SESSIONS_HISTORY table

CREATE OR REPLACE PROCEDURE SESSHIS.GATHER_SESSIONS_STATISTICS AS
BEGIN 
WHILE TRUE
LOOP
INSERT INTO SESSHIS.SESSIONS_HISTORY
(
SNAPSHOT_TIMESTAMP,
USERNAME,
SID,
SERIAL#,
STATUS,
EVENT,
WAIT_CLASS,
LAST_CALL_ET,
SECONDS_IN_WAIT,
WAIT_TIME_MICRO,
TIME_REMAINING_MICRO,
SQL_TRACE,
SQL_TRACE_BINDS,
SQL_TRACE_PLAN_STATS,
MACHINE,
PORT,
PROGRAM,
SQL_ID,
SQL_CHILD_NUMBER,
PREV_SQL_ID,
PREV_CHILD_NUMBER,
MODULE,
ACTION,
CLIENT_INFO,
LOGON_TIME,
BLOCKING_SESSION)
SELECT SYSTIMESTAMP,
USERNAME,
SID,
SERIAL#,
STATUS,
EVENT,
WAIT_CLASS,
LAST_CALL_ET,
SECONDS_IN_WAIT,
WAIT_TIME_MICRO,
TIME_REMAINING_MICRO,
SQL_TRACE,
SQL_TRACE_BINDS,
SQL_TRACE_PLAN_STATS,
MACHINE,
PORT,
PROGRAM,
SQL_ID,
SQL_CHILD_NUMBER,
PREV_SQL_ID,
PREV_CHILD_NUMBER,
MODULE,
ACTION,
CLIENT_INFO,
LOGON_TIME,
BLOCKING_SESSION
FROM V$SESSION
WHERE USERNAME IS NOT NULL AND USERNAME != 'SESSHIS';
COMMIT;

-- TIMEOUT 1 SECOND 
DBMS_LOCK.SLEEP(1);

END LOOP;
	EXCEPTION
	WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR(-20001,'Something went wrong');
END;
/

NOTE: The SESSHIS.GATHER_SESSIONS_STATISTICS procedure omits gathering background processes information.

A procedure for purging historical session information from the SESSHIS.SESSIONS_HISTORY table

CREATE OR REPLACE PROCEDURE SESSHIS.PURGE_SESSIONS_STATISTICS AS
KEEP_DAYS NUMBER := 14;
BEGIN
  DELETE FROM SESSHIS.SESSIONS_HISTORY WHERE SNAPSHOT_TIMESTAMP <= SYSDATE - KEEP_DAYS ;
  COMMIT;
END;
/
JOBS

A job for executing the SESSHIS.GATHER_SESSIONS_STATISTICS procedure on an automatic manner

CONNECT SESSHIS/SESSHIS

-- Create job 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'STORING_SESSIONS_STATISTICS',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'SESSHIS.GATHER_SESSIONS_STATISTICS',
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=1', 
   comments           =>  'Gather data from the v$session view and store them in SESSHIS.SESSIONS_HISTORY table.');
END;
/


-- Enable job

BEGIN
 DBMS_SCHEDULER.ENABLE ('STORING_SESSIONS_STATISTICS');
END;
/

-- Delete job (if needed)

-- BEGIN
--  DBMS_SCHEDULER.DROP_JOB ('STORING_SESSIONS_STATISTICS');
-- END;
-- /

NOTE: Although the repeat interval of the SESSHIS.STORING_SESSIONS_STATISTICS job is 1 minute, basically it executes only once right after a database startup and runs until a database shutdown. But if the job has been killed by anyone manually then it will be restarted automatically in a minute.

A job for executing the SESSHIS.PURGE_SESSIONS_STATISTICS procedure on an automatic manner

CONNECT SESSHIS/SESSHIS

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'PURGING_SESSIONS_STATISTICS',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'SESSHIS.PURGE_SESSIONS_STATISTICS',
   repeat_interval    =>  'FREQ=HOURLY;INTERVAL=1', 
   comments           =>  'Purge records older than 14 days in the SESSHIS.SESSIONS_HISTORY table');
END;
/

-- Enable job

BEGIN
 DBMS_SCHEDULER.ENABLE ('PURGING_SESSIONS_STATISTICS');
END;
/

-- Delete job (if needed)

-- BEGIN
--  DBMS_SCHEDULER.DROP_JOB ('PURGING_SESSIONS_STATISTICS');
-- END;
-- /
VERIFY JOBS

Verify that jobs have been created

-- Short version 

SET LINES 300 
SET PAGES 999
COL OWNER FOR A10 
COL JOB_NAME FOR A30 
COL LAST_START_DATE FOR A20 

SELECT OWNER,
       JOB_NAME,
       JOB_TYPE,
       TO_CHAR(LAST_START_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_START_DATE,
       ENABLED,
       AUTO_DROP,
       STATE       
FROM 
       DBA_SCHEDULER_JOBS
WHERE
       OWNER = 'SESSHIS';
 
OWNER      JOB_NAME                       JOB_TYPE         LAST_START_DATE      ENABL AUTO_ STATE
---------- ------------------------------ ---------------- -------------------- ----- ----- ---------------
SESSHIS    STORING_SESSIONS_STATISTICS    STORED_PROCEDURE 06-APR-2021 14:33:17 TRUE  TRUE  RUNNING
SESSHIS    PURGING_SESSIONS_STATISTICS    STORED_PROCEDURE 06-APR-2021 14:33:26 TRUE  TRUE  SCHEDULED
-- Extended version of the query

SELECT OWNER,
       JOB_NAME,
       JOB_ACTION,
       JOB_TYPE,
       TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE,
       REPEAT_INTERVAL,
       TO_CHAR(END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE,
       ENABLED,
       AUTO_DROP,
       STATE,
       RUN_COUNT,
       FAILURE_COUNT,
       TO_CHAR(LAST_START_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_START_DATE,
       LAST_RUN_DURATION,
       TO_CHAR(NEXT_RUN_DATE,'DD-MON-YYYY HH24:MI:SS') NEXT_RUN_DATE
FROM 
       DBA_SCHEDULER_JOBS
WHERE
       OWNER = 'SESSHIS';
QUERY HISTORICAL SESSION TABLE

Query the historical session information table

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT DISTINCT SNAPSHOT_TIMESTAMP FROM SESSHIS.SESSIONS_HISTORY ORDER BY 1;
SNAPSHOT_TIMESTAMP
-----------------------------
06-APR-2021 14:33:17
06-APR-2021 14:33:18
06-APR-2021 14:33:19
06-APR-2021 14:33:20

... 

06-APR-2021 14:34:33
06-APR-2021 14:34:34
06-APR-2021 14:34:35
06-APR-2021 14:34:36
06-APR-2021 14:34:37

81 rows selected.

That’s it. By querying the table with historical session information you can find session details for any session that was connected some time ago.

CLEAN UP

To remove all objects created with help of this blog note follow the CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS

 
 

Version  : 14:45 06-Apr-2021
Database : 12c+