ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION
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
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.
-- 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"
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);
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;
/
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 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 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.
To remove all objects created with help of this blog note follow the CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS
Database : 12c+