Rem drv: <create type="tables" pos="template/template_tables.sql+"/>
Rem
Rem $Header: trace_tables.sql 29-jun-2005.01:47:44 gsbhatia Exp $
Rem
Rem trace_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      trace_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    gan         08/13/04 - add monitoring 
Rem    jsadras     02/26/04 - jsadras_trace_create 
Rem    jsadras     02/26/04 - Trace Tables 
Rem    jsadras     02/26/04 - Created
Rem

Rem
Rem PURPOSE
Rem
Rem   The EMDW_TRACE_CONFIG table contains a list of Context Types . 
Rem   A entry must be added to this table before any logging can be
Rem   done against the Context Type names.
Rem
Rem COLUMNS
Rem
Rem   CONTEXT_TYPE_ID  - A unique value assigned to the context type
Rem   CONTEXT_TYPE     - Name of the context type, should be unique and in upper case.
Rem   TRACE_LEVEL      - Trace Level  of the context type, valid values are 
Rem          0 = No Logging
Rem          1 = Error Level : Error level log messages are logged
Rem          2 = Warn Level  : Warn and error level log messages are logged
Rem          3 = Info Level  : Info,warn and wrror Level log messages are logged
Rem          4 = Debug Level : Debug,info,warn and error Level log messages are logged
Rem

CREATE TABLE emdw_trace_config
( context_type_Id    NUMBER(3)    NOT NULL,
  context_type       VARCHAR2(30) NOT NULL,
  trace_level        NUMBER(1)    DEFAULT 0 NOT NULL ,
  create_date        DATE         DEFAULT SYSDATE NOT NULL ,
  last_update_date   DATE         DEFAULT SYSDATE NOT NULL ,
  CONSTRAINT emdw_trace_config_trclevel  CHECK ( Trace_level between 0 and 4) ,
  CONSTRAINT emdw_trace_config_uppercase CHECK (context_type=upper(context_type)) ,
  CONSTRAINT emdw_trace_config_noblanks  CHECK ( instr(context_type,' ')=0) ,
  CONSTRAINT emdw_trace_config_pk primary key (context_type_id) 
) 
MONITORING;

Rem
Rem  PURPOSE
Rem
Rem     The emdw_trace_context_type_id_seq sequence is used to allocate unique
Rem     IDs for emdw_trace_config.context_type_id
Rem
Rem  NOTES
Rem

CREATE SEQUENCE emdw_trace_context_type_id_seq 
INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
NOCACHE ORDER;

Rem
Rem PURPOSE
Rem
Rem   The EMDW_TRACE_DATA table contains the logged messages
Rem   A purge process will be scheduled to purge records from this table.
Rem
Rem COLUMNS
Rem
Rem   Context_Type_Id    - Context_type_ID of the calling application.
Rem   Context_Identifier - Depending on context type , this will store client information.
Rem   Log_Level          - Level at which message was logged
Rem   Log_Timestamp      - Time at which message was logged
Rem   Log_Message        - Actual Log Message
Rem   Module             - Location from where message was logged
Rem   oms_host           -  OMS host of the session


CREATE TABLE emdw_trace_data
(
 context_type_Id    NUMBER(3)     NOT NULL,
 context_identifier VARCHAR2(30),
 log_level          NUMBER(1)     NOT NULL,
 log_timestamp      TIMESTAMP     NOT NULL,
 log_message        VARCHAR2(1000) ,
 module             VARCHAR2(100),
 oms_host           VARCHAR2(100),
 CONSTRAINT emdw_trace_data_fk 
        FOREIGN KEY (context_type_id) REFERENCES
         EMDW_TRACE_CONFIG(context_type_id)
        ON DELETE CASCADE
) 
MONITORING;