Rem drv:
Rem
Rem $Header: log_tables.sql 29-jun-2005.01:47:30 gsbhatia Exp $
Rem
Rem log_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem log_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
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 aholser 03/15/03 - add log level
Rem edemembe 11/06/02 - Increasing duration precision
Rem aholser 07/10/02 - add emdurl column to error table
Rem aholser 05/31/02 - add display and dbms job to performance names
Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
rem
rem PURPOSE
rem
rem The MGMT_PERFORMANCE_NAMES contains the list of valid logging names. A
rem name must be added to this table before it can make entries in the
rem MGMT_SYSTEM_PERFORMANCE_LOG or MGMT_SYSTEM_ERROR_LOG
rem
rem COLUMNS
rem
rem module_name - The module that is permitted logging
rem display_name - The name displayed to the user. Should be short and
rem simple. If not specified, the module_name or dbms_jobname will be used.
rem These are only needed for jobs that display 'total' data to the user
rem dbms_jobname - the 'what' field from user_jobs for dbms jobs. Non
rem dbms jobs should leave this blank.
rem is_dbmsjob - Defaults to 'N'. Should be set to 'Y' for dbms jobs
rem
CREATE TABLE MGMT_PERFORMANCE_NAMES
(
job_name VARCHAR2(512) NOT NULL,
display_name VARCHAR2(128) DEFAULT NULL,
dbms_jobname VARCHAR2(128) DEFAULT NULL,
is_dbmsjob VARCHAR2(1) DEFAULT 'N'
)
MONITORING;
ALTER TABLE MGMT_PERFORMANCE_NAMES
ADD CONSTRAINT MGMT_PERFORMANCE_NAMES_PK
PRIMARY KEY(job_name);
rem
rem PURPOSE
rem
rem The MGMT_SYSTEM_ERROR_LOG table holds the list of system errors
rem
rem COLUMNS
rem
rem module_name - The module that is logging
rem occur_date - The time stamp of the error occurrance.
rem error_code - The error code of the system error being logged.
rem error_msg - THe detailed message of the system error being logged.
rem facility - The facility code for the error
rem client_data - job-specific data. Allows multiple instances to be
rem grouped by module name and distinguished by the client_data
rem host_url - the url for the host for oms(eml) entries, such as the loader
rem or job dispatcher. This should be left NULL for dbms repositoriy jobs
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_SYSTEM_ERROR_LOG
(
module_name VARCHAR2(512) NOT NULL,
occur_date DATE DEFAULT SYSDATE,
error_code NUMBER DEFAULT 0,
log_level VARCHAR2(16) DEFAULT 'DEBUG',
error_msg VARCHAR2(2048) DEFAULT ' ',
facility VARCHAR2(6) DEFAULT 'MGMT',
client_data VARCHAR2(128),
host_url VARCHAR2(256) DEFAULT NULL,
emd_url VARCHAR2(256) DEFAULT NULL
)
MONITORING;
ALTER TABLE MGMT_SYSTEM_ERROR_LOG
ADD CONSTRAINT MGMT_SYSTEM_ERROR_LOG_FK
FOREIGN KEY (module_name)
REFERENCES MGMT_PERFORMANCE_NAMES(job_name);
rem
rem PURPOSE
rem
rem The MGMT_SYSTEM_PERFORMANCE_LOG table holds the performance log entries
rem for jobs being monitored. Jobs should have one 'is_total = 'Y'' entry for each
rem pass. Any number of 'is_total='N' entries can be made to track the processing
rem time of specific data.
rem
rem These entries are used to generate performance statistics and graphs. The overall
rem statistics are generated by calculations on the fields as desired. Minimal
rem statistics include 'Total processing time' and 'Average processing time'. Graphs
rem are generaged using the timestamp to position the entry on the X-axis. Overall
rem performance is graphed using the 'is-total='Y' entries with duration as the
rem Y-axis value. Additional graphs can be generated with the Y-axis label set to
rem the 'duration' or 'name' field, and the Y-axis set to the 'value' field.
rem If a job has several instances, they are grouped by job_name, and split by
rem client_data name.
rem
rem
rem COLUMNS
rem
rem job_name - The name of the job making the entry. An entry must exist in
rem MGMT_PERFORMANCE_NAME_TABLE for it
rem time - The start time for the log entry
rem duration - The duration of the processing in milliseconds
rem module_name - The module that is logging
rem action - The action being performed
rem is_total - 'Y' if the entry is logging total processing time, 'N' if it
rem is logging a subset of the total processing time. If is_total is
rem 'N', then it is expected (but not required) for name to contain the
rem name of the item being timed by this entry.
rem name - The name of the value being logged (e.g. records). Optional
rem value - The value (e.g. 100) of the item in name processed in the time
rem specified in this entry. Optional
rem client_data - job-specific data. Allows multiple instances to be
rem grouped by module name and distinguished by the client_data
rem host_url - the url for the host for oms(eml) entries, such as the loader
rem or job dispatcher. This should be left NULL for dbms repositoriy jobs
rem
rem
rem
CREATE TABLE MGMT_SYSTEM_PERFORMANCE_LOG
(
job_name VARCHAR2(512) NOT NULL,
time DATE DEFAULT SYSDATE,
duration NUMBER(9) DEFAULT 0,
module VARCHAR2(512) DEFAULT NULL,
action VARCHAR2(32) DEFAULT NULL,
is_total VARCHAR2(1) DEFAULT 'Y',
name VARCHAR2(128) DEFAULT NULL,
value VARCHAR2(128) DEFAULT NULL,
client_data VARCHAR2(128) DEFAULT NULL,
host_url VARCHAR2(256) DEFAULT NULL
)
MONITORING;
ALTER TABLE MGMT_SYSTEM_PERFORMANCE_LOG
ADD CONSTRAINT MGMT_SYSTEM_PERFORMANCE_LOG_FK
FOREIGN KEY (job_name)
REFERENCES MGMT_PERFORMANCE_NAMES(job_name);