Rem
Rem $Header: jobs_audit_tables.sql 08-feb-2005.12:43:57 gsbhatia Exp $
Rem
Rem jobs_audit_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_audit_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    02/08/05 - Removing header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    shianand    10/04/04 - shianand_shi_job_user_audit
Rem    shianand    09/02/04 - Created
Rem

--This tables keeps the all basic job audit realted information

--command_input: The standard input to the command.
--command_output: The command output
--db_username: The database username
--src_os_username: The source O/S username 
--dest_os_username: The destination O/S username 

--dest_args: If the operation type is "pipedRemoteOp", these indicate the
--arguments on the destination agent 

--dest_command: If the operation type is "pipedRemoteOp", these indicate the 
--command on the destination agent

--source_args:  If the operation type is "remoteOp", these indicate the 
--arguments. If the operation type is "pipedRemoteOp", these indicate the 
--arguments on the source agent

--source_command:  If the operation type is "remoteOp", these indicate the 
--command. If the operation type is "pipedRemoteOp", these indicate the command 
--on the source agent 

--exit_code: Exit Code of the Job
--host_name: Host Name which is directing the job (OMS)
--job_name: The job name
--job_owner: The job owner 
--job_type: The job type of the respective job

--operation_type: The type of operation performed. Like remoteOp, putFile, getFile,
--fileTransfer, pipedRemoteOp.
--remoteOp: A remote operation on an agent
--putFile: A file transfered from an OMS to an agent
--getFile: A file transfered from an agent to an OMS
--fileTransfer: A file transfered between two agents
--pipedRemoteOp: A piped remote op (two OSCommands run on two agents, with the 
               --standard output of one streamed to the standard output of
               --the other 

--step_id: step_id of the step its unique

--dest_file: If the operation type is "putFile", this indicates the file that was 
--transfered to the agent. If the operation type is "fileTransfer", this indicates 
--the destination file 

--source_file: If the operation type is "getFile", this indicates the file that
--was transfered from the agen. If the operation type is "fileTransfer", this 
--indicates the source file 
 
--start_time: The start time of the job 
--destTarget_name: The destination target name
--destTarget_type: The target type of the destination target
--sourceTarget_name: The source target name 
--sourceTarget_type: The target type of the source target





CREATE TABLE MGMT_JOB_AUDIT_HISTORY
(
command_input CLOB,
command_output CLOB,
db_username VARCHAR2(128),
src_os_username VARCHAR2(128),
dest_os_username VARCHAR2(128),
dest_args VARCHAR2(1024),
dest_command VARCHAR2(1024),
source_args VARCHAR2(1024),
source_command VARCHAR2(1024),
exit_code NUMBER,
host_name VARCHAR2(64),
job_name VARCHAR2(64),
job_owner VARCHAR2(128),
job_type VARCHAR2(128),
operation_type VARCHAR2(64),
step_id INTEGER,
dest_file VARCHAR2(1024),
source_file VARCHAR2(1024),
start_time DATE,
destTarget_name VARCHAR2(64),
destTarget_type VARCHAR2(256),
sourceTarget_name VARCHAR2(64),
sourceTarget_type VARCHAR2(256),
CONSTRAINT step_id PRIMARY KEY(step_id)
) MONITORING 
LOB(command_input) STORE AS (TABLESPACE MGMT_ECM_DEPOT_TS),
LOB(command_output) STORE AS (TABLESPACE MGMT_ECM_DEPOT_TS);

commit;