Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_tables.sql /st_emcore_10.2.0.4.2db11.2/1 2008/10/20 21:16:39 lsatyapr Exp $
Rem
Rem jobs_tables.sql
Rem
Rem Copyright (c) 2002, 2008, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem jobs_tables.sql - Job System tables
Rem
Rem DESCRIPTION
Rem Schema for the job system
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem lsatyapr 10/12/08 - Backport lsatyapr_bug-7425991 from main
Rem lsatyapr 10/01/08 - Redefine purge_values PK
Rem lsatyapr 09/29/08 - Alter purge tables
Rem jvishen 01/23/08 - Backport jvishen_bug-6649437 from
Rem st_emcore_10.2.0.1.0
Rem kmanicka 05/24/07 - Implement 10.2.0.4 Trusted Remoteop
Rem nqureshi 04/18/07 - XbranchMerge kmanicka_pdp5 from main
Rem kmanicka 07/13/06 - add deleted_target_guid to MGMT_JOB_EXEC_SUMMARY
Rem kmanicka 08/01/06 - Backport kmanicka_bug-5223528 from main
Rem kmanicka 08/01/06 - Backport kmanicka_bug5126621 from main
Rem kmanicka 05/23/06 - make MGMT_JOB_EXEC_SUMMARY.JOB_ID AS "NOT NULL"
Rem kmanicka 02/27/06 - add initrans 4 to mgmt_job_queue
Rem kmanicka 07/19/06 - Backport kmanicka_bug-4967469 from main
Rem kmanicka 12/08/05 - Backport kmanicka_bug-4617892 from main
Rem kmanicka 12/04/05 - add initrans 8 to mgmt_job_execution
Rem skini 09/02/05 - Introduce PARAMSRC_RETRY_EXEC
Rem skini 08/28/05 - Add suspend_on_nocreds
Rem skini 08/22/05 - Get rid of ca_cred_info
Rem skini 08/04/05 - Remove target_guid from nested job credentials
Rem pkantawa 08/05/05 - Fix 4432894: fix tablespace
Rem dsahrawa 07/21/05 - bug 4496616, add support for vector and large
Rem parameters for tasks
Rem skini 07/17/05 - Add async_error_received
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem dsahrawa 06/22/05 - add MGMT_JOB_COMMAND_BLOCK_PROCS
Rem nqureshi 06/21/05 - adding support for online help topic for
Rem multitask job
Rem skini 04/15/05 - Add MGMT_JOB_CA_CRED_INFO
Rem skini 04/13/05 - Broken CAs
Rem dsahrawa 03/16/05 -
Rem kmanicka 03/07/05 - Impl IMMEDIATE Schedule
Rem dsahrawa 02/07/05 - add tables for association param sources
Rem skini 03/02/05 - Allow vector parameters to be passed to nested
Rem jobs
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 scgrover 02/01/05 - compress index
Rem ktlaw 01/11/05 - add repmgr header
Rem nqureshi 01/06/05 - renaming show_target_property to
Rem show_target_properties
Rem nqureshi 12/24/04 - adding showtargetproperty in
Rem MGMT_JOB_TYPE_PARAM_URI_INFO
Rem pkantawa 12/09/04 - Add columns for create/delete text
Rem jaysmith 11/18/04 -
Rem skini 11/17/04 - Add broken reason to job table
Rem skini 11/07/04 - Increase size of nlsid columns
Rem skini 11/01/04 - Add all_target_types
Rem skini 10/31/04 - Allow switchVarName to be vector parameter
Rem skini 10/29/04 - Defaults for version nos
Rem skini 10/27/04 - Versioning support, part 2
Rem skini 10/08/04 - Versioning changes
Rem dsahrawa 10/22/04 - add mgmt_nested_job_cred_info
Rem dsahrawa 10/14/04 - add step_target_type to mgmt_job_execplan
Rem pkantawa 10/08/04 - Add job_type_category column in MGMT_JOB_TYPE_INFO
Rem kmanicka 10/03/04 - changing col non_restartable to restartable
Rem kmanicka 09/20/04 - add col non_restartable in MGMT_JOB
Rem skini 09/17/04 - Fix syntax errors
Rem skini 09/14/04 - Implement error code propogation
Rem kmanicka 08/25/04 - added col apply_on_retry in mgmt_job_param_source
Rem pkantawa 09/01/04 - Added default for use_default_create_ui
Rem nqureshi 08/31/04 - added use_default_create_ui in
Rem MGMT_JOB_TYPE_DISPLAY_INFO
Rem gan 08/20/04 - add monitoring
Rem pkantawa 08/09/04 - Add constraints to MGMT_JOB_PARAM_URI_INFO
Rem nqureshi 08/07/04 - added new tables ,modified MGMT_JOB_TYPE_INFO & MGMT_JOB_EXECPLAN
Rem pshishir 08/07/04 - modified MGMT_JOB_EXEC_SUMMARY
Rem pshishir 07/07/04 - Add Corrective action Table and modified MGMT_JOB
Rem skini 07/26/04 - Add additional metadata for credential sources
Rem jaysmith 07/23/04 - CA table and column in MGMT_JOB
Rem skini 07/21/04 - Add status bucket to state changes table
Rem dcawley 07/20/04 - Rename column in state changes table
Rem skini 07/19/04 - Add MGMT_JOB_NOTIFY_STATES
Rem skini 07/16/04 - Add notification support
Rem dcawley 07/07/04 - Increase user name size
Rem skini 06/30/04 - Add job_id to exec_locks table
Rem rzazueta 06/14/04 - Add mgmt_job_blackout_assoc table
Rem dsahrawa 06/08/04 - skipped executions implementation
Rem aholser 03/18/04 - add oms name column
Rem skini 12/04/03 - Increase queue length to 128
Rem skini 12/05/03 - Change emd_url size to 1024
Rem skini 11/14/03 -
Rem skini 11/13/03 - Add expired column
Rem skini 09/30/03 - queue tables
Rem skini 09/30/03 - Add status bucket column
Rem skini 09/10/03 - Implement suspend job
Rem skini 09/08/03 - timezone changes
Rem skini 08/30/03 - Implement restartable, editable, suspendable
Rem skini 08/26/03 -
Rem skini 08/25/03 - Continue group support
Rem skini 08/24/03 - Support for single-target jobs
Rem skini 08/22/03 - Group/property subst support
Rem skini 08/21/03 - Group/property subst support
Rem skini 08/18/03 - Add setOverride
Rem skini 08/16/03 - Support for new-style credentials
Rem skini 07/16/03 - Job library support
Rem skini 06/25/03 - Fix error
Rem skini 06/23/03 - Callbacks
Rem skini 06/11/03 - Large parameter support
Rem skini 06/08/03 - Make step id primary key in execution table
Rem skini 06/05/03 - Fix bug 2986514: change constraint on event table
Rem rpinnama 03/25/03 - Parameterize ECM DEPOT tablespace
Rem skini 10/13/02 - Add created_at_submit
Rem skini 10/09/02 - Continue work on 2549136
Rem skini 10/08/02 - Persistify extended target list: bug 2549136
Rem skini 09/05/02 - Add target_guid to mgmt_job_exec_locks
Rem skini 08/28/02 - Implement suspend timeout
Rem skini 08/27/02 - Implement event-based suspends
Rem skini 08/28/02 - job lock tables
Rem skini 07/12/02 - Change in target_name column size
Rem skini 07/02/02 - Remove constraints
Rem skini 06/14/02 - continue implementing blackouts
Rem skini 06/07/02 - Changes to support TIMEZONE_SPECIFIED
Rem skini 06/05/02 - Add duration to schedule
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
rem
rem This script requires the following arguments
rem 1. Name of the ecm_depot tablespace where all LOBs are stored
rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
rem
rem Size of parameter names
rem
DEFINE JOB_PARAM_NAME_SIZE=64
DEFINE STEP_NAME_SIZE=64
rem
rem MGMT_JOB_COMMAND
rem PURPOSE
rem
rem The MGMT_JOB_COMMAND table holds the mapping between a command and
rem its java class (executor).
rem
rem COLUMNS
rem COMMAND_NAME The name of the command
rem
rem COMMAND_TYPE The type of the command (0: short-running; 1: long-running)
rem
rem COMMAND_CLASS The java class that the command maps to
rem
rem IS_TRUSTABLE can the command step be marked as trusted.
rem
rem NOTES
rem
CREATE TABLE MGMT_JOB_COMMAND (
command_name VARCHAR2(32),
command_class VARCHAR2(512),
command_type NUMBER(1),
is_trustable NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT "MGMT_JC_PK" PRIMARY KEY("COMMAND_NAME")
)
MONITORING;
rem
rem MGMT_JOB_TYPE_INFO
rem
rem PURPOSE
rem The MGMT_JOB_TYPE_INFO table has all other information about a job
rem type, excluding the execution plan (which is stored in the
rem MGMT_JOB_EXECPLAN table
rem
rem COLUMNS
rem
rem JOB_TYPE_ID The unique guid for this job type entry
rem JOB_TYPE The job type name
rem VERSION The metadata version, not to be confused with the
rem job type version
rem JOB_TYPE_DESCRIPTION The description
rem JOB_TYPE_OWNER The job type owner
rem JOB_TYPE_CATEGORY The category of the job type:
rem INTERNAL: 0, Internal, Oracle-defined job type
rem DRAFT: 1, A draft version of a user-defined job type
rem SHARED: 2, A shared version of a user-defined job type
rem HIDDEN: 3 A hidden job type, used with multitask jobs
rem MAJOR_VERSION The major version of the job type
rem MINOR_VERSION1, MINOR_VERSION2: The minor versions of the job type
rem LAST_MODIFIED_BY The user that last modified this job type
rem LAST_NODIFIED_DATE The date this version was last modified
rem ALL_TARGET_TYPES True if this job type supports "all" target types
rem that have host credentials. Eg., OSCommand
rem AGENT_BOUND Set to 1 if the job is declared agent-bound; false
rem otherwise
rem LOCK_ACTION The action to take when a requested lock cannot be
rem obtained
rem 1: Suspend
rem 2: Abort
rem SINGLE_TARGET Set to 1 if the job is a single-target job
rem DEFAULT_TARGET_TYPE The target type to be associated with the
rem job, if one is not specified at submission
rem EDITABLE True (1) if the job type is editable
rem SUSPENDABLE True (1) if the job type can be suspended
rem SUSPEND_ON_NOCREDS True (1) if the job system should suspend the
rem job type if credentials are not available.
rem RESTARTABLE True (1) if the job type can be retried
rem JOB_TYPE_NLSID The NLS ID of the job type
rem JOB_TYPE_DEFAULT The default display name of the job type
rem CREATE_TEXT_NLSID The NLS ID of the create instruction text
rem CREATE_TEXT_DEFAULT The default display string for create instruction text
rem DELETE_TEXT_NLSID The NLS ID of the delete instruction text
rem DELETE_TEXT_DEFAULT The default display string for delete instruction text
rem TRUSTED is this jobtype Trusted
rem
CREATE TABLE MGMT_JOB_TYPE_INFO (
job_type_id RAW(16) DEFAULT SYS_GUID(),
job_type VARCHAR2(32),
job_type_description VARCHAR2(256),
job_type_owner VARCHAR2(256),
job_type_category NUMBER(1) DEFAULT 0,
last_modified_by VARCHAR2(256),
last_modified_date DATE DEFAULT SYSDATE,
all_target_types NUMBER(1) DEFAULT 0,
version VARCHAR2(20),
major_version NUMBER DEFAULT 1,
minor_version1 NUMBER DEFAULT 0,
minor_version2 NUMBER DEFAULT 1,
agent_bound NUMBER(1) DEFAULT 0,
lock_action NUMBER(1) DEFAULT 0,
single_target NUMBER(1) DEFAULT 0,
default_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
suspendable NUMBER(1) DEFAULT 0,
editable NUMBER(1) DEFAULT 0,
suspend_on_nocreds NUMBER(1) DEFAULT 0,
restartable NUMBER(1) DEFAULT 0,
job_type_nlsid VARCHAR2(64),
job_type_default VARCHAR2(256),
create_text_nlsid VARCHAR2(64),
create_text_default VARCHAR2(256),
delete_text_nlsid VARCHAR2(64),
delete_text_default VARCHAR2(256),
trusted NUMBER(1) DEFAULT -1 NOT NULL,
CONSTRAINT PK_JOB_TYPE_INFO PRIMARY KEY(job_type_id)
)
MONITORING;
rem
rem MGMT_JOB_TYPE_MAX_VERSIONS
rem
rem PURPOSE
rem This table contains the most recent registered versions
rem of a job type
rem
rem COLUMNS
rem job_type The job type
rem job_type_id The job type id corresponding to the job type
rem major_version The major version
rem minor_version1 The minor version, part 1
rem minor_version2 The minor version, part 2
rem
CREATE TABLE MGMT_JOB_TYPE_MAX_VERSIONS (
job_type VARCHAR2(32),
job_type_id RAW(16),
major_version NUMBER DEFAULT 1,
minor_version1 NUMBER DEFAULT 0,
minor_version2 NUMBER DEFAULT 1,
CONSTRAINT PK_JOB_TYPE_MAX_VERSIONS PRIMARY KEY(job_type, major_version)
)
MONITORING;
rem
rem MGMT_JOB_SINGLE_TARGET_TYPES
rem
rem PURPOSE
rem The MGMT_JOB_SINGLE_TARGET_TYPES table holds all the
rem declared target types that a "single-target" job
rem operates on
rem
rem job_type_id The job type id
rem single_target_type The target type
CREATE TABLE MGMT_JOB_SINGLE_TARGET_TYPES (
job_type_id RAW(16),
single_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
CONSTRAINT PK_SINGLE_TARGET_TYPES
PRIMARY KEY(job_type_id, single_target_type),
CONSTRAINT FK_SINGLE_TARGET_TYPES
FOREIGN KEY(job_type_id) REFERENCES
MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_EXECPLAN
rem
rem PURPOSE
rem The MGMT_JOB_EXECPLAN table holds information about the execution
rem plan of a job; ie what the steps (and stepsets) of a job are, what
rem command each step executes, and the relationships between them.
rem
rem COLUMNS
rem
rem JOB_TYPE_ID The job type id
rem
rem STEP_NAME The name of the step
rem
rem STEP_TYPE The type of the step. This has the following meanings:
rem 1 Step
rem 2 Serial Stepset
rem 3 Switch Stepset
rem 4 Parallel Stepset
rem 5 Iterative Serial Stepse
rem 6 Iterative Parallel Stepset
rem 7 (Nested) Job
rem 8 Parameter source
rem 9 Parameter source for rescheduled steps
rem 10 Start wait step
rem 11 Grace wait step
rem 12 Flatten targets step
rem 13 Parameter source for retried executions
rem
rem NESTED_JOB_TYPE If the step is of type JOB (4), this column
rem indicates the type of the job.
rem
rem NESTED_JOB_TARGET_TYPE This target type of the nested job.
rem This is meant to be used _only_ for nested jobs within
rem MultiTask jobs and user defined job types.
rem
rem INCOMING_EDGE_TYPE This is the type of the incoming edge to this
rem step in the job dependency graph. This can have the following
rem values:
rem 1 Serial_Begin
rem 2 Serial_End
rem 3 Switch_End
rem 4 Parallel_Begi
rem 5 IterativeSerial_Begin
rem 6 IterativeParallel_Begin
rem 7 SuccessOf_End
rem 8 FailureOf_End
rem
rem ORIGIN_STEP_NAME The name of the originating step in the graph
rem from which the edge originated.
rem
rem ORIGIN_STEP_TYPE The type of the originating step.
rem
rem SWITCH_VAR_NAME For switch stepsets, the job parameter name to
rem switch on. Null of all other stepste types
rem
rem SWITCH_VAR_INDEX If switchVarName is a vector parameter, this
rem indicates the index within the vector parameter to switch on
rem
rem SWITCH_CASE_VAL For immediate children of switch stepsets, this
rem represents the case value. If job parameter matches the case value
rem then the step is scheduled.
rem
rem ITERATE_PARAM In the case of iterative stepsets, the parameter
rem to iterate over. Null for all other stepset types
rem
rem ITERATE_PARAM_FILTER In the case of iterative stepsets, an optional
rem filter to the values of the iterate_param.
rem Null for all other stepset types
rem
rem NUM_CHILDREN The number of child steps. This is zero for all steps,
rem and is set to the number of children for stepsets.
rem
rem STEPSET_NAME The name of the enclosing stepset. This will be null
rem only for the first stepset in the job type.
rem
rem STEPSET_STATUS The stepset(s) that will contribute to the status
rem of this stepset. This is valid only for stepsets.
rem
rem COMMAND_NAME The name of the command that this step will execute
rem
rem RESTART_MODE Set to 1 if restart is on failure only; 2 if the
rem step/stepset/job must be always restarted
rem
rem ALL_PARAMS True (1) if all job parameters are to be passed to a
rem step/nested job. If false (0), the parameters are taken from
rem MGMT_JOB_NESTED_JOB_PARAMS. Applies to steps and nested jobs only.
rem
rem ALL_TARGETS True (1) if all job targets are to be passed to a
rem nested job; if false (0), the targets are taken from the table
rem MGMT_NESTED_JOB_TARGETS. Applies to nested jobs only.
rem
rem STATUS_ON_FAILURE For iterative serial stepsets only: the status
rem to set the stepset to if one of the child stepsets in the iterative
rem stepset aborts. If -1, the iterative stepset continues execution;
rem If this is set to ABORTED_STATUS or FAILED_STATUS, then the
rem iterative stepset is aborted/failed at the first failure,
rem respectively
rem TRUSTED This column is used with RemoteOp Commands. It specifies if the this step is trusted.
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_EXECPLAN (
job_type_id RAW(16),
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
nested_job_type VARCHAR2(32),
nested_job_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE) DEFAULT NULL,
incoming_edge_type NUMBER(1),
origin_step_name VARCHAR2(&STEP_NAME_SIZE),
origin_step_type NUMBER(2),
switch_var_name VARCHAR2(64),
switch_var_index VARCHAR2(96),
switch_case_val VARCHAR2(4000),
iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
iterate_param_filter VARCHAR2(4000),
num_children INTEGER,
stepset_name VARCHAR2(&STEP_NAME_SIZE),
stepset_status VARCHAR2(512) DEFAULT NULL,
command_name VARCHAR2(32),
restart_mode NUMBER(1) DEFAULT 1,
all_params NUMBER(1) DEFAULT 0,
all_targets NUMBER(1) DEFAULT 0,
halt_on_failure NUMBER(1) DEFAULT 0,
step_nlsid varchar2(64),
step_default VARCHAR2(256) ,
flattened_targets NUMBER(1) DEFAULT 0,
trusted NUMBER(1) DEFAULT -1 NOT NULL,
CONSTRAINT PK_MGMT_JOB_EXECPLAN
PRIMARY KEY(job_type_id, step_name, step_type),
CONSTRAINT FK_MGMT_JOB_EXECPLAN FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_STEP_PARAMS
rem
rem PURPOSE
rem This holds the parameter information specified in a job type
rem specification that will be passed to a command at runtime. Note
rem that this is not the set of parameters actually passed to the
rem job at runtime.
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem STEP_NAME The step name within a job type
rem PARAMETER_TYPE The type of the parameter
rem 0 Vector parameter
rem 1 scalar parameter
rem 2 large parameter
rem ENCRYPTED 1 if this parameter is stored encrypted, 0 if not
rem PARAM_NAME The name of the parameter
rem SCALAR_VALUE The parameter value if the parameter is scalar
rem VECTOR_VALUE The parameter value if the parameter is vector
rem LARGE_VALUE A pointer to the value if the parameter is
rem a large parameter. The value is an id that references
rem a row in the MGMT_LARGE_PARAMS table
rem VALUE_OF If specified, then the name of the job parameter whose
rem value this parameter will take. This can be used to pass
rem vector parameters to steps
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_STEP_PARAMS (
job_type_id RAW(16),
step_name VARCHAR2(&STEP_NAME_SIZE),
param_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
parameter_type NUMBER(1),
encrypted NUMBER(1) DEFAULT 0,
scalar_value VARCHAR2(4000),
vector_value MGMT_JOB_VECTOR_PARAMS,
large_value RAW(16) DEFAULT NULL,
value_of VARCHAR2(64) DEFAULT NULL,
CONSTRAINT PK_MGMT_JOB_STEP_PARAMS
PRIMARY KEY(job_type_id, step_name, param_name)
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
-- NESTED TABLE vector_value STORE AS vector_step_value_tab;
rem
rem MGMT_NESTED_JOB_TARGETS
rem
rem PURPOSE
rem This table holds the targets for nested jobs and flatten steps.
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem STEP_NAME The name of the step
rem STEP_TYPE The type of the step
rem TARGET_NAME The target name
rem TARGET_TYPE The target type
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_NESTED_JOB_TARGETS (
job_type_id RAW(16),
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
target_name VARCHAR2(&EM_TARGET_NAME_SIZE),
target_type VARCHAR2(&EM_TARGET_TYPE_SIZE)
)
MONITORING;
REM
REM The MGMT_NESTED_JOB_CRED_INFO table stores overridden credentials
REM for nested jobs within a job for example, tasks within multitask jobs.
REM These may be overridden per target, or for all targets of a specified
REM type in the nested job.
REM
REM TBD. The job_type should be changed to job_type_id when that
REM is implemented in the jobs tables.
REM
REM JOB_TYPE_ID The job type id
REM NESTED_JOB_NAME The name of the nested job
REM TARGET_NAME The target that this credential set is for, <0> if
REM this is an entry for all targets of a specified type
REM TARGET_TYPE If target_guid is null, specifies the target type
REM that the credentials pertain to
REM CONTAINER_LOCATION The container location
REM CREDENTIAL_SET_NAME The credential set name
REM PDP_DATA PDP Information associated with the credentials.
REM CREDENTIAL_GUID The credential guid that references the stored
REM values in the MGMT_CREDENTIALS table
REM
CREATE TABLE MGMT_NESTED_JOB_CRED_INFO
(
job_type_id RAW(16),
nested_job_name VARCHAR2(64),
target_name VARCHAR2(&EM_TARGET_NAME_SIZE),
target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
container_location VARCHAR2(256),
credential_set_name VARCHAR2(32),
pdp_data VARCHAR(512) DEFAULT NULL,
credential_guid RAW(16),
CONSTRAINT PK_MGMT_NESTED_JOB_CRED_INFO
PRIMARY KEY (job_type_id, nested_job_name, target_name,
target_type, container_location, credential_set_name),
CONSTRAINT FK_MGMT_NESTED_JOB_CRED_INFO
FOREIGN KEY (job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO (job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_PARAM_SOURCE
rem
rem PURPOSE
rem The MGMT_JOB_PARAM_SOURCE table holds information about
rem parameter sources in a job type.
rem
rem COLUMNS
rem
rem SOURCE_ID A unique id for this source
rem JOB_TYPE_ID The job type id
rem APPLY_AT_SUBMISSION If true (1), this source is applied at
rem job submission time, otherwise, it is applied at job
rem execution time (the default).
rem APPLY_ON_RETRY If true (1), this source is applied at
rem the time of restarting the job, otherwise, it is not (default).
rem STEP_NAME, STEP_TYPE The step that this param source is associated with
rem SOURCE_TYPE The source type (currently, 'sql', 'credentials' or 'user'
rem SOURCE_INDEX A number that indicates the order of this parameter source
rem SOURCE_DATA The PCDATA for this source
rem OVERRIDE_USER True (1) if this source should over-ride user-specified
rem parameter values.
rem REQUIRED True (1) if this is a required parameter
rem ENCRYPTED True (1) if this is an encrypted parameter
rem PARAMETER_NAMES The set of parameters that this source supports
rem
CREATE TABLE MGMT_JOB_PARAM_SOURCE (
source_id RAW(16) DEFAULT SYS_GUID(),
job_type_id RAW(16),
apply_at_submission NUMBER(1),
apply_on_retry NUMBER(1) DEFAULT 0,
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
source_type VARCHAR2(32),
source_index NUMBER(4),
source_data VARCHAR2(4000),
override_user NUMBER(1),
required NUMBER(1),
encrypted NUMBER(1) DEFAULT 0,
parameter_names MGMT_JOB_VECTOR_PARAMS,
CONSTRAINT PK_MGMT_PARAM_SOURCE PRIMARY KEY(source_id)
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_USER_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_USER_PARAMS table holds parameters specific to
rem user parameter sources
rem
rem COLUMNS
rem SOURCE_ID source id of the parameter source
rem TARGET_NAME_PARAMS, TARGET_TYPE_PARAMS Hold the name of parameter
rem pairs designated by the job type writer as "target" params.
rem The name of the parameter holding the target names is in
rem target_name_params; the corresponding parameter that holds
rem target types is in target_type_params.
rem
CREATE TABLE MGMT_JOB_USER_PARAMS (
source_id RAW(16),
target_name_params MGMT_JOB_VECTOR_PARAMS,
target_type_params MGMT_JOB_VECTOR_PARAMS,
CONSTRAINT PK_USER_PARAM_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_USER_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_SQL_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_SQL_PARAMS table holds parameters specific to
rem SQL parameter sources
rem
rem
CREATE TABLE MGMT_JOB_SQL_PARAMS (
source_id RAW(16),
vector_params MGMT_JOB_VECTOR_PARAMS,
out_proc NUMBER(1),
out_param_type VARCHAR2(20),
CONSTRAINT PK_SQL_PARAM_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_SQL_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
-- NESTED TABLE vector_params STORE AS vector_params_tab;
rem
rem MGMT_JOB_CRED_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_CRED_PARAMS table stores parameters for each
rem credentials parameter source in a job type.
rem
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem CREDENTIAL_SET_NAME The credential set name
rem CREDENTIAL_SET_TARGET_TYPE The target type of the credential set
rem CREDENTIAL_COLUMNS The credential columns which will be fetched
rem CREDENTIAL_COLUMNS_PARAM The name of a vector parameter that will
rem hold the credential columns, specified in lieu of CREDENTIAL_COLUMNS
rem TARGET_NAMES The names of the targets for which the credentials
rem must be fetched
rem TARGET_TYPES The types of the targets for which the credentials
rem must be fetched
rem CONTAINER_PATHS The container paths for each of the targets
rem TARGET_NAMES_PARAM The name of the job parameter which holds the
rem target names
rem TARGET_TYPES_PARAM The name of the job parameter which holds the
rem target_types
rem CONTAINER_PATHS_PARAM The name of the job parameter that holds the
rem container paths
rem
rem The target_names_param and target_types_param are specified as
rem alternatives to the target_names and target_types parameter
rem
rem SET_OVERRIDE If true (1), set the credentials retrieved as override
rem credentials for the job. If there are multiple executions, this
rem ensures that credentials retrieved at submission are set as
rem credentials for all subsequent executions.
rem
CREATE TABLE MGMT_JOB_CRED_PARAMS (
source_id RAW(16),
credential_set_name VARCHAR2(256),
credential_set_target_type VARCHAR2(256),
base_cred_type_name VARCHAR2(256),
base_cred_type_target_type VARCHAR2(256),
base_cred_type_columns MGMT_JOB_VECTOR_PARAMS,
credential_columns MGMT_JOB_VECTOR_PARAMS,
credential_columns_param VARCHAR2(64),
target_names MGMT_JOB_VECTOR_PARAMS,
target_types MGMT_JOB_VECTOR_PARAMS,
container_paths MGMT_JOB_VECTOR_PARAMS,
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
container_paths_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
set_override NUMBER(1),
CONSTRAINT PK_CRED_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_CRED_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
REM
REM MGMT_JOB_SUBST_PARAMS
REM
REM PURPOSE
REM The MGMT_JOB_SUBST_PARAMS table stores parameters for each
REM substValues parameter source in a job type
REM
REM COLUMNS
REM
REM SOURCE_ID The source id for the param source
REM source_params The source params to be substituted
REM
CREATE TABLE MGMT_JOB_SUBST_PARAMS (
source_id RAW(16),
source_params MGMT_JOB_VECTOR_PARAMS,
CONSTRAINT PK_SUBSTVALUE_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_SUBSTVALUE_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_PROP_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_PROP_PARAMS table stores parameters for each
rem properties parameter source in a job type.
rem
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem PROPERTY_NAMES The property names to fetch
rem PROPERTY_NAMES_PARAM The name of a vector parameter that contains
rem the property names. One of property_names or property_names_param
rem must be specified
rem TARGET_NAMES The names of the targets for which the credentials
rem must be fetched
rem TARGET_TYPES The types of the targets for which the credentials
rem must be fetched
rem TARGET_NAMES_PARAM The name of the job parameter which holds the
rem target names
rem TARGET_TYPES_PARAM The name of the job parameter which holds the
rem target_types
rem
rem The target_names_param and target_types_param are specified as
rem alternatives to the target_names and target_types parameter
rem
CREATE TABLE MGMT_JOB_PROP_PARAMS (
source_id RAW(16),
property_names MGMT_JOB_VECTOR_PARAMS,
property_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
target_names MGMT_JOB_VECTOR_PARAMS,
target_types MGMT_JOB_VECTOR_PARAMS,
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
CONSTRAINT PK_PROP_PARAMS PRIMARY KEY(source_id),
CONSTRAINT FK_PROP_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_VALUE_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_VALUE_PARAMS table stores the values of parameters
rem referenced in the INLINE and CHECKVALUE parameter sources
rem
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem PARAM_VALUES The parameter values. In the case of the inline source,
rem they are assigned; in the case of the checkValue source, they are
rem compared.
rem ACTION The action to perform (for checkValue sources) if values dont
rem match.
rem
CREATE TABLE MGMT_JOB_VALUE_PARAMS (
source_id RAW(16),
param_values MGMT_JOB_PARAM_LIST,
action VARCHAR2(32),
CONSTRAINT PK_VALUE_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_VALUE_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
NESTED TABLE param_values STORE AS param_values_tab
MONITORING;
-- (NESTED TABLE vector_value STORE AS pv_vec_value_tab);
rem
rem MGMT_JOB_ASSOC_PARAMS
rem
rem PURPOSE
rem The MGMT_JOB_ASSOC_PARAMS table stores the parameters
rem referenced in the ASSOC parameter sources in a job type.
rem
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem TARGET_NAMES_PARAM The name of the vector parameter which
rem will contain the associated target names.
rem TARGET_TYPES_PARAM The name of the vecotr parameter which
rem will contain the associated target types.
rem ASSOC_NAME The association that is used
rem SRC_TARGET_NAME The target which is the source for the association
rem SRC_TARGET_TYPE The target type of the source target
rem ASSOC_TARGET_TYPE The desired target type for associated targets
rem Note. If ASSOC_TARGET_TYPE is NULL it implies ALL targets
rem filter the associated targets
rem
CREATE TABLE MGMT_JOB_ASSOC_PARAMS (
source_id RAW(16),
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
assoc_name VARCHAR2(64) NOT NULL,
src_target_name VARCHAR2(&EM_TARGET_NAME_SIZE) NOT NULL,
src_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE) NOT NULL,
assoc_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
CONSTRAINT PK_ASSOC_SRC PRIMARY KEY(source_id),
CONSTRAINT FK_ASSOC_PARAM_SRC FOREIGN KEY(source_id)
REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
ON DELETE CASCADE
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_SEC_INFO
rem
rem PURPOSE
rem The MGMT_JOB_SEC_INFO stores security information about a
rem job type: specifically, the privileges that a user needs
rem to possess in order to submit a job of that type.
rem
rem COLUMNS
rem
rem JOB_TYPE_ID The job type id
rem APPLY_AT_SUBMISSION If true (1), the privilege check is applied
rem at submission time. If false, it is applied at execution time.
rem PRIVILEGE_NAME The privilege name
rem PRIVILEGE_TYPE The type of the privilege. 1: Target 2: System
rem TARGET_NAMES The (possibly symbolic) names of the targets that
rem the privilege must be checked against (if its a target privilege)
rem TARGET_TYPE The corresponding target types
rem TARGET_NAMES_PARAM A parameter that will contain the target names
rem to check
rem TARGET_TYPES_PARAM A parameter that will contain the target types
rem to check
rem
CREATE TABLE MGMT_JOB_SEC_INFO (
job_type_id RAW(16),
apply_at_submission NUMBER(1),
privilege_name VARCHAR2(32),
privilege_type NUMBER(1),
target_names MGMT_JOB_VECTOR_PARAMS,
target_types MGMT_JOB_VECTOR_PARAMS,
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE)
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_LOCK_INFO
rem
rem PURPOSE
rem This table stores lock metadata about a job type
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem LOCK_INDEX The index of the lock in the job type
rem LOCK_NAME The name of the lock; could be null for global locks
rem LOCK_TYPE The type of the lock
rem 1: Global
rem 2: Target Global
rem 3: Target Named
rem LOCK_MODE The mode in which lock is held
rem 1: Exclusive (Keeps out all other executions)
rem 2: Shared exclusive (Keeps out all other executions, except
rem those of the same type)
rem
CREATE TABLE MGMT_JOB_LOCK_INFO (
lock_guid RAW(16) DEFAULT SYS_GUID(),
job_type_id RAW(16),
lock_index NUMBER(4),
lock_name VARCHAR2(32),
lock_type NUMBER(2),
lock_mode NUMBER(1),
target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
CONSTRAINT PK_MGMT_JOB_LOCK_INFO
PRIMARY KEY(lock_guid, job_type_id)
USING INDEX TABLESPACE &EM_ECM_DEPOT_TABLESPACE,
CONSTRAINT FK_MGMT_JOB_LOCK_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_LOCK_TARGETS
rem
rem PURPOSE
rem For target locks, this is a list of targets associated with the lock
rem Note: these could be symbolic
rem
rem COLUMNS
rem LOCK_GUID The unique guid of the lock
rem TARGET_NAME The target name
rem TARGET_TYPE The target type
rem
CREATE TABLE MGMT_JOB_LOCK_TARGETS (
lock_guid RAW(16),
job_type_id RAW(16),
target_name VARCHAR2(&EM_TARGET_NAME_SIZE),
target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
target_index NUMBER(5),
CONSTRAINT FK_JOB_LOCK_TARGETS FOREIGN KEY(lock_guid, job_type_id)
REFERENCES MGMT_JOB_LOCK_INFO(lock_guid, job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_SCHEDULE
rem
rem PURPOSE
rem The schedule table
rem
rem COLUMNS
rem SCHEDULE_ID The id of the schedule
rem FREQUENCY_CODE The frequency code (type) of the schedule.
rem Possible Values are
rem 0 IMMEDIATE_FREQUENCY_CODE
rem 1 ONE_TIME_FREQUENCY_CODE
rem 2 INTERVAL_FREQUENCY_CODE
rem 3 DAILY_FREQUENCY_CODE
rem 4 WEEK_FREQUENCY_CODE
rem 5 MONTH_FREQUENCY_CODE
rem 6 YEAR_FREQUENCY_CODE
rem
rem START_TIME The schedule start time. For daily, days of week and
rem days of month schedules, the start_time denotes when the job should
rem start. Only the time part is used, and the date part is ignored.
rem
rem START_GRACE_PERIOD The grace period for starting the job specified
rem in minutes. If the job does not start between START_TIME and
rem START_TIME + START_GRACE_PERIOD it is skipped. The default of -1
rem indicates an infinite grace period.
rem
rem END_TIME For all periodic schedules, the last date (and time) to
rem run the job. For daily, day of week and day of month schedules,
rem only the date portion is used. For interval schedules, the time is
rem taken into account as well.
rem
rem EXECUTION_HOURS The "execution time" for daily, day of
rem week/month/year schedules. Indicates the time of the day at which
rem the job will execute. Hours are specified in 24-hour format
rem (0 to 23), and minutes is a number between 0 and 59.
rem
rem EXECUTION_MINUTES See EXECUTION_HOURS
rem
rem INTERVAL The interval, in minutes, for interval jobs.
rem
rem MONTHS For days-of-year schedules, this indicates the "month"
rem portion of each (month, day) pair in the schedule. The "days"
rem portion is specified using "days", below.
rem
rem DAYS For day of week/month jobs, the day(s) of the week,
rem or month as the case may be.
rem For days of week, this is a set of numbers from 1-7 indicating
rem the days sunday through saturday, in that order.
rem For days of month, this is a set of numbers from 1-31 indicating the
rem date of the month. A special constant called LAST_DAY (-1) can be used
rem for day-of-month schedules to indicate the last day of the month
rem For days of the year, this is a set of numbers from 1-32 indicating
rem the date of the month. A special constant called LAST_DAY (-1) can
rem be used for day-of-year schedules to indicate the last day of the month.
rem For day-of-year schedules, the corresponding months are taken from the
rem "months" field above
rem
rem
rem TIMEZONE_INFO Specifies which timezone the start and end times
rem are specified in
rem TIMEZONE_REPOSITORY: The timezone of the repository
rem TIMEZONE_TARGET: The timezone of one of the targets in the
rem target list, whose index in the target list is specified by
rem timezone_target_index
rem TIMEZONE_SPECIFIED: The timezone delta is specified, and stored
rem in the TIMEZONE_OFFSET column. Deprecated, in favor of the
rem TIMEZONE_RGN_SPECIFIED option
rem TIMEZONE_RGN_SPECIFIED: The timezone region is specified, and
rem stored in the TIMEZONE_REGION column.
rem
rem TIMEZONE_TARGET_INDEX The index of the target in the target list(s)
rem whose timezone the times are specified in; only relevant when
rem timezone_info=TIMEZONE_TARGET
rem
rem TIMEZONE_OFFSET The offset between GMT and the timezone of the
rem schedule. Only relevant when timezone_info=TIMEZONE_SPECIFIED
rem
rem TIMEZONE_REGION The timezone region, used when timezone_info is
rem set to TIMEZONE_RGN_SPECIFIED
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_SCHEDULE (
schedule_id RAW(16) DEFAULT SYS_GUID(),
frequency_code NUMBER(2),
start_time DATE,
start_grace_period NUMBER DEFAULT -1,
end_time DATE,
execution_hours NUMBER(3),
execution_minutes NUMBER(3),
interval NUMBER,
months MGMT_JOB_INT_ARRAY,
days MGMT_JOB_INT_ARRAY,
timezone_info NUMBER(1),
timezone_target_index NUMBER,
timezone_offset NUMBER,
timezone_region VARCHAR2(64),
CONSTRAINT PK_MGMT_JOB_SCH PRIMARY KEY(schedule_id)
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB
rem
rem PURPOSE
rem The master job table.
rem
rem COLUMNS
rem JOB_ID A unique ID for this job
rem JOB_NAME The name of the job (also considered unique)
rem JOB_OWNER The owner of the job
rem JOB_DESCRIPTION Job description
rem JOB_TYPE The job type
rem JOB_TYPE_MAJOR_VERSION The job type major version
rem NESTED If true (1), the job is a nested job under another job.
rem NESTED_JOB_TYPE_ID If the job is a nested job, this is the
rem exact job type id of the nested job
rem PARENT_JOB_ID If the job is nested, this indicates the id of the
rem parent job
rem SCHEDULE_ID The id of the schedule for this job, will be null for
rem a nested job
rem SYSTEM_JOB 1 if this is a system job, 0 if not
rem TARGET_TYPE For single-target jobs, this is the target type that
rem the job was submitted against.
rem MAX_TARGET_LIST_INDEX The last target-list index chosen for this
rem job. This increases whenever a new target is added
rem JOB_STATUS The current state of the job. Possible values are
rem 0 Active
rem 1 Expired The schedule has expired.
rem 2 Suspended The job is currently suspended by the user
rem IS_CORRECTIVE_ACTION is 1 if the row is a corrective action
rem (and has a corresponding row in MGMT_CORRECTIVE_ACTION)
rem else by default it is 0.
rem RESTARTABLE if 0 it implies the job cant be restarted
rem it is set to '0' when a user is deleted and the job owned by the
rem the user is assined to a new user. This is to make sure the new user
rem does not get acces to the old users credentials
rem BROKEN True if this job or CA is broken. A broken job or CA
rem will not run
rem BROKEN_REASON The reason that caused the job or ca to be broken.
rem BROKEN_USER_REASSIGN: This job/CA was reassigned after a user was
rem deleted
rem BROKEN_NO_CREDS: This CA is broken because no credentials
rem are currently available
rem BROKEN_NO_PRIVS: This CA is broken because the current "owner"
rem does not have operator on the target the CA is associated
rem with
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB (
job_id RAW(16) DEFAULT SYS_GUID(),
job_name VARCHAR2(64) NOT NULL,
job_owner VARCHAR2(256) NOT NULL,
job_description VARCHAR2(4000),
job_type VARCHAR2(32) NOT NULL,
job_type_major_version NUMBER DEFAULT 1,
nested NUMBER(1) DEFAULT 0,
nested_job_type_id RAW(16),
is_library NUMBER(1) DEFAULT 0,
parent_job_id RAW(16) DEFAULT NULL,
schedule_id RAW(16) DEFAULT NULL,
execution_timeout NUMBER DEFAULT -1,
max_target_list_index NUMBER DEFAULT 1,
system_job NUMBER(1) DEFAULT 0,
target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
job_status NUMBER(2) DEFAULT 0,
expired NUMBER(1) DEFAULT 0,
is_corrective_action NUMBER(1) DEFAULT 0,
restartable NUMBER(1) DEFAULT 1,
broken NUMBER(1) DEFAULT 0,
broken_reason NUMBER(2),
CONSTRAINT "MGMT_JOB_PK" PRIMARY KEY("JOB_ID")
)
MONITORING;
rem
rem MGMT_CORRECTIVE_ACTION
rem
rem PURPOSE
rem The correctiveaction for a job
rem
rem COLUMNS
rem JOB_ID The id of the job
rem CORRECTIVE_ACTION_SCOPE This is a corrective action defined
rem for a particular target, and it will be associated with
rem one or more metric thresholds or policy rules on that target.
rem CORRECTIVE_ACTION_TARGET_GUID for target
rem CORRECTIVE_ACTION_TEMPLATE_GUID for template Target type
rem CORRECTIVE_ACTION_REFERENCE_COUNT for total reference count
rem CREDENTIALS_DISABLED is 0 if yes, not disabled
rem NOTES
CREATE TABLE MGMT_CORRECTIVE_ACTION (
job_id RAW(16) DEFAULT SYS_GUID(),
ca_scope NUMBER(1) DEFAULT 1,
ca_target_guid RAW(16),
ca_template_guid RAW(16),
ca_reference_count NUMBER,
CONSTRAINT "MGMT_CORRECTIVE_ACTION_PK" PRIMARY KEY("JOB_ID")
)
MONITORING;
rem
rem MGMT_JOB_TARGET
rem
rem PURPOSE
rem The targets for a job
rem
rem COLUMNS
rem JOB_ID The id of the job
rem TARGET_LIST_INDEX The index of the target list that this
rem entry belongs to. A job could be submitted against
rem multiple target lists
rem TARGET_NAME Target name
rem TARGET_TYPE Target type
rem TARGET_GUID Target GUID
rem TARGET_INDEX The index of this target in the target list
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_TARGET (
job_id RAW(16),
execution_id RAW(16),
target_list_index INTEGER,
target_guid RAW(16) NOT NULL,
target_index NUMBER(6)
)
MONITORING;
rem
rem MGMT_JOB_FLAT_TARGETS
rem
rem PURPOSE
rem An internal book-keeping table used to keep
rem track of the flat target list of single-target
rem jobs.
rem
rem COLUMNS
rem job_id The job id
rem target_guid The target guid
rem target_list_index The target list index
rem is_active This can have the following values:
rem 1 The target is currently active
rem 2 The target has been removed from the flat list
rem
CREATE TABLE MGMT_JOB_FLAT_TARGETS (
job_id RAW(16),
target_guid RAW(16),
target_list_index NUMBER(9),
active NUMBER(1),
CONSTRAINT PK_JOB_FLAT_TARGETS
PRIMARY KEY(job_id, target_guid),
CONSTRAINT FK_MGMT_JOB FOREIGN KEY(job_id)
REFERENCES MGMT_JOB(job_id) ON
DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_EXT_TARGETS
rem
rem PURPOSE
rem The "extended" target list of a job. This contains the target
rem list of the job as well as values of parameters flagged as
rem "target" parameters. Unlike the target list, this list is
rem not an ordered list.
rem
rem COLUMNS
rem JOB_ID The id of the job
rem TARGET_LIST_INDEX The index of the target list that this
rem entry belongs to. A job could be submitted against
rem multiple target lists
rem TARGET_GUID Target GUID
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_EXT_TARGETS (
job_id RAW(16),
execution_id RAW(16),
target_list_index INTEGER,
target_guid RAW(16),
reference_count NUMBER(4),
CONSTRAINT PK_JOB_EXT_TARGETS PRIMARY KEY(job_id, execution_id, target_list_index, target_guid)
)
MONITORING;
rem
rem MGMT_JOB_OUTPUT
rem
rem PURPOSE
rem This table stores job output as a CLOB
rem
rem COLUMNS
rem OUTPUT_ID A unique GUID for this output row
rem REFERENCE_COUNT The number of rows in MGMT_JOB_EXECUTION/HISTORY
rem that are referring to this row
rem OUTPUT The output
rem
CREATE TABLE MGMT_JOB_OUTPUT (
output_id RAW(16),
reference_count INTEGER DEFAULT 1,
output CLOB,
CONSTRAINT PK_MGMT_JOB_OUTPUT PRIMARY KEY(output_id)
)
LOB(output) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;
rem
rem MGMT_JOB_LARGE_PARAMS
rem
rem PURPOSE
rem This table stores large parameter values
rem
rem COLUMNS
rem PARAM_ID A unique GUID for this parameter
rem REFERENCE_COUNT The reference count. Large parameters are
rem not copied, their reference counts are increased
rem OUTPUT The output
rem
CREATE TABLE MGMT_JOB_LARGE_PARAMS (
param_id RAW(16),
reference_count INTEGER DEFAULT 1,
param_value CLOB,
CONSTRAINT PK_MGMT_JOB_LARGE_PARAMS PRIMARY KEY(param_id)
)
LOB(param_value) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;
rem
rem MGMT_JOB_EXEC_SUMMARY
rem
rem PURPOSE
rem Contains a one-line summary for each execution
rem
rem COLUMNS
rem JOB_ID The job id
rem JOB_TYPE_ID The job type id
rem EXECUTION_ID The execution id
rem SOURCE_EXECUTION_ID The source execution id. Set to the execution id
rem if there is no source execution
rem TARGET_LIST_INDEX The target list index for this execution
rem STATUS The status of this execution
rem STATUS_CODE The propogated status code for the execution (usually
rem the status code of the last failed step)
rem STATUS_CODE_CATEGORY The "categpry" of the status code.
rem Possible values are:
rem INTERNAL(2): Generated by a job system component
rem (eg., a remoteop network failure)
rem APP(1): Application-specific (exit code from the application)
rem
rem STATUS_BUCKET The status bucket that this execution belongs to
rem STATUS_DETAIL Detail pertaining to the status if the status is WAITING or SKIPPED
rem SCHEDULED_TIME, EXPECTED_START_TIME, START_TIME, END_TIME The scheduled,
rem expected, started, and end times of the execution. These times are in UTC
rem
rem TIMEZONE_REGION The timezone region that is associated with
rem this execution
rem
rem SUSPEND_TIMEOUT The suspend timeout; valid of > 0
rem
rem SUSPEND_TIME The time the execution was last suspended; Also in UTC
rem
rem QUEUE_ID If the execution is part of a queue, the queue id
rem QUEUE_INDEX The index of the execution in the queue
rem
rem TRIGGERING_SEVERITY if the execution is of a corrective action, the
rem guid of the severity or policy violation that triggered the execution
rem (from MGMT_VIOLATION). null for job execution.
rem
rem DELETED_TARGET_GUID when a target is deleted this column
rem is populated during handle_pre_target_delete
rem and later used by handle_post_target_delete
rem to delete the execution.
rem
CREATE TABLE MGMT_JOB_EXEC_SUMMARY (
job_id RAW(16) NOT NULL,
job_type_id RAW(16),
execution_id RAW(16),
source_execution_id RAW(16),
target_list_index INTEGER,
status INTEGER,
status_code INTEGER,
status_code_category NUMBER(1) DEFAULT 1,
status_bucket INTEGER,
status_detail INTEGER DEFAULT 0,
scheduled_time DATE DEFAULT null,
expected_start_time DATE DEFAULT null,
start_time DATE DEFAULT null,
end_time DATE DEFAULT null,
timezone_region VARCHAR2(64),
suspend_event VARCHAR2(32) DEFAULT NULL,
suspend_time DATE DEFAULT null,
suspend_timeout NUMBER(9) DEFAULT 0,
queue_id RAW(16),
queue_index NUMBER,
triggering_severity RAW(16),
deleted_target_guid RAW(16) DEFAULT NULL,
CONSTRAINT PK_MGMT_EXEC_SUMM PRIMARY KEY(execution_id)
)
MONITORING;
REM
REM MGMT_JOB_EXEC_CRED_INFO
REM
REM PURPOSE
REM Holds information about credential requirements for each
REM execution of a job. Specifies whether or not that
REM an execution needs a specific set of credentials
REM
REM For TOP LEVEL jobs, information will be set at the level of
REM (job_id=>parent job id, execution_id, target_guid)
REM For NESTED jobs, we use
REM (job_id=>nested job id, execution_id, target_guid)
REM For MULTITASK jobs of all flavors, we use
REM (job_id=>parent job id, execution_id, task name, target_guid)
REM For CAs, same as above except that execution_id is set to
REm NO_EXECUTION
REM COLUMNS
REM
REM job_id The job id (nested job id for nested jobs)
REM execution_id The execution id
REM task_name The task name, for multitask jobs only
REM target_guid The target guid
REM credential_set_name The credential set name for that target
REM credentials_set Set to 1 if this execution has credentials for
REM the specific target and credential set, 0 if not.
REM This flag is required since a job may have overridden
REM credentials set for a target
REM
CREATE TABLE MGMT_JOB_EXEC_CRED_INFO (
job_id RAW(16),
execution_id RAW(16),
task_name VARCHAR2(64) DEFAULT '<0>',
target_guid RAW(16),
container_location VARCHAR2(256),
credential_set_name VARCHAR2(32),
credentials_set NUMBER(1),
CONSTRAINT PK_JOB_EXEC_CRED_INFO
PRIMARY KEY(job_id, execution_id, task_name, target_guid,
container_location, credential_set_name)
)
MONITORING;
rem
rem MGMT_JOB_EXEC_EVENT_PARAMS
rem
rem PURPOSE
rem Holds the parameters for events associated with executions.
rem
rem COLUMNS
rem EXECUTION_ID The id of the execution
rem PARAM_NAME The name of the parameter
rem PARAM_VALUE The value of the parameter
rem
CREATE TABLE MGMT_JOB_EXEC_EVENT_PARAMS (
execution_id RAW(16),
param_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
param_value VARCHAR2(256),
CONSTRAINT PK_MGMT_JOB_EXEC_EVPARAMS
PRIMARY KEY(execution_id, param_name, param_value)
)
MONITORING;
rem
rem MGMT_JOB_EXEC_LOCKS
rem
rem PURPOSE
rem Holds all requested and acquired locks during execution
rem
rem COLUMNS
rem EXECUTION_ID The requesting/holding execution id. This column
rem will be null for locks held in SHARED EXCLUSIVE mode
rem JOB_ID The requesting job id.
rem JOB_TYPE_ID The job type of the holding execution(s)
rem LOCK_GUID The unique guid of the lock
rem TARGET_GUID The target guid (for target locks only)
rem LOCK_STATUS The current status of the lock
rem 1: Requested (waiting)
rem 2: Held
rem LOCK_REQUEST_TIME The time the lock was requested
rem LOCK_ACQUIRED_TIME The time the lock was acquired
rem
CREATE TABLE MGMT_JOB_EXEC_LOCKS (
execution_id RAW(16),
job_id RAW(16),
job_type_id RAW(16),
lock_guid RAW(16),
target_guid RAW(16),
lock_mode NUMBER(1),
lock_status NUMBER(1),
lock_request_time DATE DEFAULT NULL,
lock_acquired_time DATE DEFAULT NULL,
lock_index NUMBER
)
MONITORING;
rem
rem MGMT_JOB_PARAMETER
rem
rem PURPOSE
rem The parameters for a job. Note that both the job_id and
rem execution_id are significant while making queries because
rem an execution could have nested jobs, and a job can have
rem several executions...
rem
rem COLUMNS
rem JOB_ID The id of the job
rem PARAMETER_NAME Parameter name
rem PARAMETER_TYPE The type of the parameter
rem 0 Vector parameter
rem 1 scalar parameter
rem 2 large parameter
rem ENCRYPTED 1 if parameter is encrypted, 0 if not
rem SCALAR_VALUE Parameter value if parameter is scalar
rem VECTOR_VALUE Parameter value if parameter is a vector
rem LARGE_VALUE A pointer to the value if the parameter is
rem a large parameter. The value is an id that references
rem a row in the MGMT_LARGE_PARAMS table
rem CREATED_AT_SUBMIT True if the parameter was provided
rem by the user, at submission time
rem NOTES
rem
rem
CREATE TABLE MGMT_JOB_PARAMETER (
job_id RAW(16) NOT NULL,
execution_id RAW(16) DEFAULT '0000000000000000',
parameter_name VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
parameter_type NUMBER(1),
encrypted NUMBER(1) DEFAULT 0,
scalar_value VARCHAR2(4000) DEFAULT NULL,
vector_value MGMT_JOB_VECTOR_PARAMS DEFAULT NULL,
large_value RAW(16) DEFAULT NULL,
created_at_submit NUMBER DEFAULT 0,
CONSTRAINT PK_MGMT_JOB_PARAM
PRIMARY KEY(job_id, execution_id, parameter_name)
USING INDEX (CREATE UNIQUE INDEX PK_MGMT_JOB_PARAM
ON MGMT_JOB_PARAMETER (job_id, execution_id, parameter_name)
COMPRESS 2
TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem MGMT_JOB_HISTORY
rem
rem PURPOSE
rem The MGMT_JOB_HISTORY table tracks the executions of all jobs:
rem jobs that are currently executing and those that are finished.
rem
rem COLUMNS
rem JOB_ID The id of the job
rem
rem EXECUTION_ID The execution id of this execution.
rem
rem STEP_NAME The name of the step
rem
rem STEP_TYPE The step type; See MGMT_JOB_EXECPLAN
rem
rem STEP_ID The step id
rem
rem SOURCE_STEP_ID The step id of the corresponding step if this is a
rem restart execution
rem
rem ORIGINAL_STEP_ID If this step id is copied from another step in
rem a source execution, the "original" step id
rem
rem RESTART_MODE For stepsets and jobs, this value overrides the
rem actual restart mode that their constituent steps may have
rem
rem COMMAND_TYPE 0 for short-running, 1 for long-running
rem
rem ITERATE_PARAM For iterative stepsets (or thier children), the
rem current value of the param to iterate over
rem
rem ITERATE_PARAM_INDEX The current index in the vector param that
rem the current iterative stepset is iterating over
rem
rem PARENT_STEP_ID The sequence number of the parent step-set
rem
rem STEP_STATUS The status of the step. Possible values are
rem 1 SCHEDULED
rem 2 EXECUTING
rem 3 ABORTED
rem 4 FAILED
rem 5 COMPLETED
rem
rem STEP_STATUS_CODE The "status code" of the step
rem
rem STEP_STATUS_CODE_CATEGORY The status code category, indicates
rem whether the error code was generated by the application,
rem or internally by the job system
rem Possible values are APP (1) and INTERNAL (2)
rem NUM_CHILDREN The number of child step(sets) of this stepset
rem that are scheduled for execution.
rem
rem NUM_CHILDREN_COMPLETED In the case of a stepset, the number of
rem children of this stepset that are finished executing (whether
rem they failed, completed, or aborted).
rem
rem OUTPUT_ID The output ID of this step.
rem
rem ERROR_ID The ID corresponding to the error text for this step
rem
rem START_TIME The start time of this step, UTC
rem
rem END_TIME The end time of this step, UTC
rem
rem TIMEZONE_REGION The timezone region associated with this
rem execution
rem
rem SEQUENCE_NUMBER The sequence number of the last chunk that was used
rem to update the status/output/error of this step. This avoids
rem multiple updates. This only applies to async remote ops.
rem
rem DISPATCHER_ID The ID of the dispatcher that is currently processing
rem this step; zero (0) if none
rem
rem OMS_NAME The name of the oms which handled it
rem
CREATE TABLE MGMT_JOB_HISTORY (
job_id RAW(16),
execution_id RAW(16),
step_id INTEGER,
source_step_id INTEGER,
original_step_id INTEGER DEFAULT -1,
restart_mode INTEGER DEFAULT 1,
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
command_type NUMBER(1),
iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
iterate_param_index NUMBER(8),
parent_step_id INTEGER,
step_status NUMBER(2),
step_status_code NUMBER(5) DEFAULT 0,
step_status_code_category NUMBER(1) DEFAULT 1,
num_children INTEGER DEFAULT 0,
num_children_completed INTEGER DEFAULT 0,
output_id RAW(16) DEFAULT NULL,
error_id RAW(16) DEFAULT NULL,
start_time DATE,
end_time DATE DEFAULT NULL,
timezone_region VARCHAR2(64),
sequence_number NUMBER(6) DEFAULT -1,
dispatcher_id INTEGER DEFAULT -1,
oms_name VARCHAR2(256) DEFAULT NULL,
CONSTRAINT PK_MGMT_JOB_HISTORY PRIMARY KEY(step_id)
) PCTFREE 25
INITRANS 8
MONITORING;
rem
rem MGMT_JOB_EXECUTION
rem
rem PURPOSE
rem The MGMT_JOB_EXECUTION table tracks the status of all the steps
rem and stepsets of each execution of a job.
rem
rem COLUMNS
rem JOB_ID The id of the job
rem
rem EXECUTION_ID The execution id of this execution.
rem
rem STEP_NAME The name of the step
rem
rem STEP_TYPE The step type; See MGMT_JOB_EXECPLAN
rem
rem STEP_ID The step id
rem
rem SOURCE_STEP_ID The step id of the corresponding step if this is a
rem restart execution
rem
rem ORIGINAL_STEP_ID If this step id is copied from another step in
rem a source execution, the "original" step id
rem
rem RESTART_MODE For stepsets and jobs, this value overrides the
rem actual restart mode that their constituent steps may have
rem
rem COMMAND_TYPE 0 for short-running, 1 for long-running
rem
rem ITERATE_PARAM For iterative stepsets (or thier children), the
rem current value of the param to iterate over
rem
rem ITERATE_PARAM_INDEX The current index in the vector param that
rem the current iterative stepset is iterating over
rem
rem PARENT_STEP_ID The sequence number of the parent step-set
rem
rem STEP_STATUS The status of the step. Possible values are
rem 1 SCHEDULED
rem 2 EXECUTING
rem 3 ABORTED
rem 4 FAILED
rem 5 COMPLETED
rem
rem STEP_STATUS_CODE The "status code" of the step
rem
rem STEP_STATUS_CODE_CATEGORY The status code category, indicates
rem whether the error code was generated by the application,
rem or internally by the job system
rem Possible values are APP (1) and INTERNAL (2)
rem
rem NUM_CHILDREN The number of child step(sets) of this stepset
rem that are scheduled for execution.
rem
rem NUM_CHILDREN_COMPLETED In the case of a stepset, the number of
rem children of this stepset that are finished executing (whether
rem they failed, completed, or aborted).
rem
rem OUTPUT_ID The output ID of this step.
rem
rem ERROR_ID The ID corresponding to the error text for this step
rem
rem START_TIME The start time of this step
rem
rem END_TIME The end time of this step
rem
rem TIMEZONE_REGION The timezone region associated with this
rem execution
rem
rem SEQUENCE_NUMBER The sequence number of the last chunk that was used
rem to update the status/output/error of this step. This avoids
rem multiple updates. This only applies to async remote ops.
rem
rem DISPATCHER_ID The ID of the dispatcher that is currently processing
rem this step; zero (0) if none
rem
rem EMD_URL For asynchronous remote operations only: the URL of the emd
rem that the remote op was dispatched to
rem
rem OMS_NAME The name of the oms which handled it
rem
rem ASYNC_ERROR_RECEIVED Set to true (1) if an async error notification
rem was received by the agent for this step. This is later used to
rem determine the actual status (failed/aborted) of the step
rem
CREATE TABLE MGMT_JOB_EXECUTION (
job_id RAW(16),
execution_id RAW(16),
step_id INTEGER,
source_step_id INTEGER,
original_step_id INTEGER DEFAULT -1,
restart_mode INTEGER DEFAULT 1,
step_name VARCHAR2(&STEP_NAME_SIZE),
step_type NUMBER(2),
command_type NUMBER(1),
iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
iterate_param_index NUMBER(8),
parent_step_id INTEGER,
step_status NUMBER(2),
step_status_code NUMBER(5) DEFAULT 0,
step_status_code_category NUMBER(1) DEFAULT 1,
num_children INTEGER DEFAULT 0,
num_children_completed INTEGER DEFAULT 0,
output_id RAW(16) DEFAULT NULL,
error_id RAW(16) DEFAULT NULL,
start_time DATE,
end_time DATE DEFAULT NULL,
timezone_region VARCHAR2(64),
sequence_number NUMBER(6) DEFAULT -1,
dispatcher_id INTEGER DEFAULT -1,
emd_url VARCHAR2(1024) DEFAULT NULL,
oms_name VARCHAR2(256) DEFAULT NULL,
async_error_received NUMBER(1) DEFAULT 0,
CONSTRAINT PK_MGMT_JOB_EXECUTION PRIMARY KEY(step_id)
) PCTFREE 30
INITRANS 8
MONITORING;
rem
rem MGMT_JOB_STEP_COMMAND_LOG
rem
rem PURPOSE
rem The MGMT_JOB_STEP_COMMAND_STATUS table holds the status of one
rem or more command blocks in a step.
rem
rem COLUMNS
rem
rem STEP_ID The step id
rem COMMAND_BLOCK_ID The id of the command block. Consecutive blocks
rem will have monotonically increasing ids, although not necessarily
rem contiguous ones.
rem COMMAND_BLOCK_TEXT_ID The id that points to command block text. There
rem will be a row in the job output table that points to the text
rem COMMAND_BLOCK_STATUS The status of this command block. The status
rem could be either COMPLETED or FAILED, depending on whether or
rem not the command block was successfully applied
rem COMMAND_BLOCK_ERROR If the status of the block was FAILED, this
rem column holds an error message that indicates the reason for
rem the failure
rem
CREATE TABLE MGMT_JOB_STEP_COMMAND_LOG (
step_id NUMBER,
command_block_id NUMBER,
command_block_text_id RAW(16),
command_block_status NUMBER(2),
command_block_error VARCHAR2(4000) DEFAULT NULL,
CONSTRAINT PK_STEP_COMMAND_LOG
PRIMARY KEY(command_block_id)
)
MONITORING;
rem
rem MGMT_JOB_COMMAND_BLOCK_PROCS
rem PURPOSE
rem This table holds all pre-registered procedures that can
rem be called from command blocks.
rem COLUMNS
rem proc_name The name of the procedure
rem param_types The (formal) parameter types for the procedure
rem
CREATE TABLE MGMT_JOB_COMMAND_BLOCK_PROCS (
proc_name VARCHAR2(256),
param_types MGMT_SHORT_STRING_ARRAY,
CONSTRAINT PK_COMMAND_BLOCK_PROCS
PRIMARY KEY(proc_name)
)
MONITORING;
rem
rem MGMT_JOB_EMD_STATUS_QUEUE
rem
rem PURPOSE
rem The MGMT_JOB_EMD_STATUS_QUEUE is a table into which a row is inserted
rem every time an EMD bounces or is detected as down (by the ping system).
rem
rem COLUMNS
rem EMD_URL The url of the affected emd
rem event_type 1 for down, 2 for bounced
rem occur_time The time at which the event occurred, in UTC
rem
CREATE TABLE MGMT_JOB_EMD_STATUS_QUEUE (
emd_url VARCHAR2(1024),
event_type INTEGER,
occur_time DATE
)
MONITORING;
rem
rem MGMT_JOB_PURGE_POLICIES
rem
rem PURPOSE
rem The MGMT_JOB_PURGE_POLICIES table holds all currently registered
rem purge policies
rem
rem COLUMNS
rem POLICY_NAME The name of the purge policy
rem TIME_FRAME The time-frame, in days
rem
CREATE TABLE MGMT_JOB_PURGE_POLICIES (
policy_name VARCHAR2(32),
time_frame NUMBER,
CONSTRAINT PK_MGMT_JOB_PURGE_POL PRIMARY KEY(policy_name)
)
MONITORING;
rem
rem MGMT_JOB_PURGE_CRITERIA
rem
rem PURPOSE
rem The MGMT_JOB_PURGE_CRITERIA table holds information about all purge
rem criteria in a purge policy
rem
rem COLUMNS
rem POLICY_NAME The name of the purge policy
rem CRITERION_INDEX The index of this criterion in the purge policy
rem SELECT_CLAUSE A SQL clause that must be applied when applying this
rem purge policy
rem
CREATE TABLE MGMT_JOB_PURGE_CRITERIA (
policy_name VARCHAR2(32),
criterion_index NUMBER(3),
criterion_type NUMBER(1),
negated NUMBER(1)
)
MONITORING;
rem
rem MGMT_JOB_PURGE_TARGETS
rem
rem PURPOSE
rem The MGMT_JOB_PURGE_CRITERIA table holds information about all targets
rem in a target criterion in a purge policy
rem
rem COLUMNS
rem POLICY_NAME The name of the purge policy
rem CRITERION_INDEX The index of this criterion in the purge policy
rem PURGE_GUID The target guid of each target named in the criterion
rem
CREATE TABLE MGMT_JOB_PURGE_TARGETS (
policy_name VARCHAR2(32),
criterion_index NUMBER(3),
purge_tguid RAW(16)
)
MONITORING;
rem
rem MGMT_JOB_PURGE_VALUES
rem
rem PURPOSE
rem The MGMT_JOB_PURGE_VALUES table holds information about criterion
rem values for all criteria other than the target criterion. For
rem example, it could be used to hold all users in a user criterion,
rem or job types in a job-type criterion
rem
rem COLUMNS
rem POLICY_NAME The name of the purge policy
rem CRITERION_INDEX The index of this criterion in the purge policy
rem PURGE_VALUE Each individual value named in the purge criterion
rem
CREATE TABLE MGMT_JOB_PURGE_VALUES (
policy_name VARCHAR2(32),
criterion_index NUMBER(3),
purge_value VARCHAR2(4000)
)
MONITORING;
rem
rem MGMT_JOB_EVENT
rem
rem PURPOSE
rem The MGMT_JOB_EVENT table stores information about all possible
rem external stimuli to the job system. An event can potentially
rem resume executions that are suspended on that event
rem
rem COLUMNS
rem EVENT_NAME The name of the event
rem EVENT_CALLBACK The callback function (if any) associated with the
rem event. The callback should have the signature
rem PROCEDURE callback(p_event_name VARCHAR2,
rem p_event_params SMP_EMD_NVPAIR_ARRAY);
rem
CREATE TABLE MGMT_JOB_EVENT (
event_name VARCHAR2(32),
event_callback VARCHAR2(96),
CONSTRAINT PK_MGMT_JOB_EVENT PRIMARY KEY(event_name)
)
MONITORING;
rem
rem MGMT_JOB_TYPE_URI_INFO
rem
rem PURPOSE
rem The MGMT_JOB_TYPE_URI_INFO table has URI information
rem about a job types display
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem URI_USE What job ui page or fragment is this uri for
rem URI The actual URI
rem IS_JSP Is the URI written in jsp? (or uix)
rem CLASS The page handler or bean that goes with this page (for jsp)
rem
CREATE TABLE MGMT_JOB_TYPE_URI_INFO (
job_type_id RAW(16),
uri_use NUMBER(2),
uri VARCHAR2(4000),
is_jsp NUMBER(1),
class VARCHAR2(4000),
CONSTRAINT FK_JOB_TYPE_URI_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_TYPE_DISPLAY_PARAM
rem
rem PURPOSE
rem The MGMT_JOB_TYPE_DISPLAY_PARAM table lists the
rem params shown or not on the summary page
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem PARAM_ORDER The order in which to list the param (for show only)
rem PARAM_NAME The name of the param to show or hide (multiple per job_type)
rem see MGMT_JOB_TYPE_DISPLAY_INFO.show_param for show vs. hide
rem
CREATE TABLE MGMT_JOB_TYPE_DISPLAY_PARAM (
job_type_id RAW(16),
param_order NUMBER(4),
param_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_TYPE_DISPLAY_INFO
rem
rem PURPOSE
rem The MGMT_JOB_TYPE_DISPLAY_INFO table lists the
rem params shown or not on the summary page and
rem the nlsBundle.
rem
rem COLUMNS
rem JOB_TYPE_ID The job type id
rem SHOW_PARAM Should the params be shown on the summary page?
rem NLS_BUNDLE the class name of the resource bundle for this job_type
rem use_default_create_ui identifies whether this job uses our default create Ui or not 0 not uses 1 uses
CREATE TABLE MGMT_JOB_TYPE_DISPLAY_INFO (
job_type_id RAW(16),
show_param NUMBER(1),
nls_bundle VARCHAR2(4000),
use_default_create_ui number(1) DEFAULT 0,
CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_STEP_TARGETS
rem The MGMT_JOB_STEP_TARGETS table holds the targets for a step.
rem There may be multiple targets for a step.
rem
CREATE TABLE MGMT_JOB_STEP_TARGETS (
step_id NUMBER,
target_guid RAW(16),
CONSTRAINT FK_JOB_STEP_TGT_STEPID FOREIGN KEY(step_id)
REFERENCES MGMT_JOB_HISTORY(step_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem MGMT_JOB_CALLBACKS
rem The MGMT_JOB_CALLBACKS table holds all the callbacks for
rem a given job type
rem
CREATE TABLE MGMT_JOB_CALLBACKS (
job_type_id RAW(16),
callback_type NUMBER(2),
callback_name VARCHAR2(128),
CONSTRAINT PK_MGMT_JOB_CALLBACKS
PRIMARY KEY(job_type_id, callback_type, callback_name),
CONSTRAINT FK_MGMT_JOB_CALLBACKS
FOREIGN KEY(job_type_id) REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
REM
REM MGMT_JOB_QUEUES
REM The MGMT_JOB_QUEUES table holds job queue entries.
REM NOTE: Currently, schedules are not supported, and
REM queues are always active
REM
REM queue_name The name of the queue
REM queue_id The unique guid of the queue
REM schedule_guid The schedule guid
REM schedule_duration The schedule duration
REM enabled True if the queue is enabled
REM active True if the queue is "active" A queue is said to
REM be active if it is enabled *and* its schedule is currently active
REM max_queue_index The index of the last entry in the queue
REM concurrency_factor The queue concurrency factor
REM num_scheduled_executions The number of executions currently running
REM num_executions The number of executions in the queue
REM
CREATE TABLE MGMT_JOB_QUEUES (
queue_name VARCHAR2(128),
queue_id RAW(16),
schedule_guid RAW(16),
schedule_duration NUMBER(9),
enabled NUMBER(1) DEFAULT 1,
active NUMBER(1) DEFAULT 1,
max_queue_index NUMBER DEFAULT 0,
concurrency_factor NUMBER(5) DEFAULT 1,
num_scheduled_executions NUMBER(5) DEFAULT 0,
num_executions NUMBER DEFAULT 0,
CONSTRAINT PK_JOB_QUEUE PRIMARY KEY(queue_id)
)
INITRANS 4
MONITORING;
rem
rem MGMT_JOB_BLACKOUT_ASSOC
rem
rem PURPOSE
rem The MGMT_JOB_BLACKOUT_ASSOC table holds the associations between
rem jobs and blackouts (Ie. jobs that create blackouts)
rem
rem execution_id The execution id which creates the blackout
rem blackout_guid The blackout guid
CREATE TABLE MGMT_JOB_BLACKOUT_ASSOC (
execution_id RAW(16),
blackout_guid RAW(16),
CONSTRAINT PK_MGMT_JOB_BLACKOUT_ASSOC
PRIMARY KEY(execution_id, blackout_guid),
CONSTRAINT FK_MGMT_JOB_BLACKOUT_ASSOC
FOREIGN KEY(execution_id) REFERENCES
MGMT_JOB_EXEC_SUMMARY(execution_id)
ON DELETE CASCADE
)
MONITORING;
rem
rem PURPOSE
rem
rem This table contains a row for each job state change. This is needed
rem for the job notifications framework, and could be used as well
rem to keep track of further changes
rem
rem state_change_guid A guid for the state change
rem job_id The job id
rem execution_id The execution id
rem step_id The step id
rem logged The current time
rem occurred The time at which the state change occurred
rem newstate The new state
rem type ?
rem violation_guid The violation guid
CREATE TABLE MGMT_JOB_STATE_CHANGES
(state_change_guid RAW(16) DEFAULT SYS_GUID(),
job_id RAW(16),
execution_id RAW(16),
step_id INTEGER,
logged TIMESTAMP DEFAULT SYSTIMESTAMP,
occurred DATE,
newstate NUMBER(2),
status_bucket NUMBER(2),
type NUMBER,
violation_guid RAW(16),
CONSTRAINT PK_MGMT_JOB_ST_CHGS PRIMARY KEY(state_change_guid),
CONSTRAINT FK_MGMT_JOB_ST_CHGS_1
FOREIGN KEY(execution_id) REFERENCES
MGMT_JOB_EXEC_SUMMARY(execution_id)
ON DELETE CASCADE
)
MONITORING;
REM
REM MGMT_JOB_NOTIFY_STATES
REM
REM This table is used to store the set of states that the job owner
REM can choose to be emailed on
REM
REM job_id The job id
REM notify_state The state to notify. Note that this is a status bucket
REM value, not a job status
REM
CREATE TABLE MGMT_JOB_NOTIFY_STATES (
job_id RAW(16),
notify_state NUMBER(2),
CONSTRAINT PK_MGMT_JOB_NOTIF_STATE PRIMARY KEY(job_id, notify_state),
CONSTRAINT FK_MGMT_JOB_NOTIF_STATE_1
FOREIGN KEY(job_id) REFERENCES
MGMT_JOB(job_id) ON DELETE CASCADE
) MONITORING;
rem
rem MGMT_JOB_SEQUENCE
rem The sequence that generates step ids in a job execution
rem
CREATE SEQUENCE MGMT_JOB_SEQUENCE INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;
rem
rem TABLE MGMT_JOB_TYPE_PARAM_URI_INFO
rem
rem PURPOSE
rem The TABLE MGMT_JOB_TYPE_PARAM_URI_INFO table holds all the
rem all the parameters UI Controller class info and which file user shud forward to
rem
rem
rem job_type_ID The job type id
rem URI URL of the JSP controller will use this URL to forward the user to page
rem CLASS Fully Qualified name of the controller class
rem HELP_TOPIC online Help Topic used when job is a NORMAL standalone job
rem TASK_HELP_TOPIC online TASK Help Topic use ful when job is a part of multitask job
rem SHOWTARGETPROPERTY if thios flag is set to false parameters page will not display the target properties default value is true(1)
CREATE TABLE MGMT_JOB_TYPE_PARAM_URI_INFO
(
JOB_TYPE_ID RAW(16),
URI VARCHAR2(2000),
CLASS VARCHAR2(2000),
HELP_TOPIC VARCHAR2(2000),
TASK_HELP_TOPIC VARCHAR2(2000),
SHOW_TARGET_PROPERTIES NUMBER(1) default 1 ,
CONSTRAINT PK_PARAM_URI_INFO PRIMARY KEY(job_type_id),
CONSTRAINT FK_PARAM_URI_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
rem
rem TABLE MGMT_JOB_TYPE_PARAM_DSPLY_INFO
rem
rem PURPOSE
rem The MGMT_JOB_TYPE_PARAM_DSPLY_INFO table holds all the
rem all the parameters display parameters Information parameters information stored here
rem will be shownm to the user in create/edit/view jobs/jobtypes to
rem
rem
rem job_type_ID The job type id
rem parameter_name name of the parameter,
rem show_in_create at the time of create we want to show this parameter or not ..,
rem show_in_results whether parameter shud be displayed while showing results ,
rem label_nlsid nlsID ,
rem label_default default Label text ,
rem hint_nlsid NLS ID of the Hint ,
rem hint_default hint text for the user ,
rem
rem display_mode parameters is textBox or a dropdown information,
rem num_lines number of columns in a textBox,
rem default_text default text ,
rem default_nlsid Text NLSID,
rem param_order In which order we want to show parameters to the user ,
CREATE TABLE MGMT_JOB_TYPE_PARAM_DSPLY_INFO (
job_type_id RAW(16),
parameter_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
show_in_create NUMBER(1),
show_in_results NUMBER(1),
label_nlsid VARCHAR2(64),
label_default VARCHAR2(256),
hint_nlsid VARCHAR2(64),
hint_default VARCHaR2(256),
display_mode NUMBER(2),
num_lines NUMBER(2),
default_text VARCHAR2(4000),
default_nlsid VARCHAR2(64),
param_order NUMBER(3) NOT NULL ,
CONSTRAINT PK_JOB_TYPE_PARAM_DSPLY_INFO PRIMARY KEY(job_type_id,parameter_name),
CONSTRAINT FK_JOB_TYPE_PARAM_INFO FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
rem
rem TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS
rem
rem PURPOSE
rem The TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS table holds all the
rem if a parameters is dropdown then all the option values of the parameter
rem
rem
rem job_type_ID The job type id
rem parameter_name Name of the parameter
rem is_default whether is is the default selected option,
rem option_value option value ,
rem option_text_nlsid option display text nlsid,
rem option_text_default option default display text,
rem param_order number(3) option order
rem
CREATE TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS (
job_type_id RAW(16),
parameter_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
is_default NUMBER(1),
option_value varchar2(32),
option_text_nlsid VARCHAR2(64),
option_text_default VARCHAR2(256),
param_order number(3) not null,
CONSTRAINT FK_JOB_TYPE_PARAM_DRPDWNS FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;
rem
rem The MGMT_JOB_DISPLAY_ERROR_CODES table contains a
rem mapping from an application-specific error code to
rem an nls id
rem
rem job_type_ID The job type id
rem id The error code
rem nlsid The NLS ID
rem default_value The default value
rem
CREATE TABLE MGMT_JOB_DISPLAY_ERROR_CODES (
job_type_id RAW(16),
id VARCHAR2(64),
nlsid VARCHAR2(64),
default_value VARCHAR2(256),
CONSTRAINT PK_MGMT_JOB_DSPLY_ERR_CODES PRIMARY KEY(job_type_id, id),
CONSTRAINT FK_JOB_TYPE_DSPLY_ERR_CODES FOREIGN KEY(job_type_id)
REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
ON DELETE CASCADE
) MONITORING;