Friday, April 15, 2016

Create idle MWA session logging table for sessions to be terminated

This script is intended to be used with the Post:

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler


CREATE TABLE XXX.XXX_KILLED_MWA_USER_SESSIONS
(
  SCHEMANAME    VARCHAR2(30 BYTE),
  INST_ID       NUMBER,
  SID           NUMBER,
  SERIAL#       NUMBER,
  LOGON_TIME    DATE,
  LAST_CALL_ET  NUMBER,
  MODULE        VARCHAR2(64 BYTE),
  ACTION        VARCHAR2(64 BYTE),
  KILLED_TIME   DATE,
  SQL_ID        VARCHAR2(64 BYTE)
)

TABLESPACE XXX
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


GRANT SELECT ON XXX.XXX_KILLED_MWA_USER_SESSIONS TO APPS;

No comments:

Post a Comment