Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_task_pkgdef.sql /st_emcore_10.2.0.4.2db11.2/1 2008/10/23 21:13:20 jsadras Exp $
Rem
Rem collections_task_pkgdef.sql
Rem
Rem Copyright (c) 2004, 2008, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem collections_task_pkgdef.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem jsadras 10/21/08 - Backport jsadras_bug-7479372 from
Rem st_emcore_10.2.0.1.0
Rem jsadras 10/14/08 - Bug:7479372,expose run_tasks
Rem jsadras 08/22/07 - Backport jsadras_bug-6347110 from main
Rem jsadras 07/30/07 - Backport jsadras_bug-6156475 from main
Rem jsadras 08/17/07 - Bug:6347110, task workers
Rem jsadras 07/22/07 - Bug:6156475, snapshot collections processing
Rem change
Rem pmodi 09/09/05 - Add new proc to detect and clean anamolies in
Rem task job
Rem scgrover 07/06/05 - add extended sql trace
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem jsadras 06/12/05 - add task class descriptions
Rem jsadras 04/26/05 - set/get default worker parameter
Rem jsadras 11/23/04 - set_max_time_worker
Rem jsadras 11/18/04 - set_min_interval
Rem jsadras 11/03/04 - task_priority
Rem jsadras 11/02/04 - extend task_type
Rem jsadras 08/26/04 - retry
Rem jsadras 08/17/04 - jsadras_repocollect2
Rem jsadras 08/17/04 - module_name
Rem jsadras 08/03/04 - Created
Rem
CREATE OR REPLACE PACKAGE em_task
AS
--
-- Module to implement multiple collection workers for repository
-- collections. This would be enhanced to provide general
-- threads for background execution of pl/sql code in the
-- repository.
-- USE mgmt_collection to add/remove workers for now.
--
G_LOG_CONTEXT CONSTANT VARCHAR2(30) := 'REPOCOLLECTION' ;
--
-- base task priorities for stop, event based tasks,
-- scheduled repository metric collections and others
-- We will add mod(task_type,10) to this to get priority
--
G_TASK_PRIORITY_STOP CONSTANT NUMBER := 10 ;
G_TASK_PRIORITY_EVENT CONSTANT NUMBER := 60 ;
G_TASK_PRIORITY_DEFAULT CONSTANT NUMBER := 100 ;
--
-- Task types used for event metric collections
-- will be removed , do not use
--
G_TASK_TYPE_EVENT_LOW CONSTANT NUMBER := 2 ;
G_TASK_TYPE_EVENT_HIGH CONSTANT NUMBER := 2 ;
--
-- Task status, will be deprecated.
--
G_TASK_STATUS_IDLE CONSTANT NUMBER := 0 ;
G_TASK_STATUS_RUNNING CONSTANT NUMBER := 1 ;
--
-- Worker status
--
G_WORKER_STATUS_STARTING CONSTANT NUMBER(1) := 0 ;
G_WORKER_STATUS_STARTED CONSTANT NUMBER(1) := 1 ;
G_WORKER_STATUS_STOP_PENDING CONSTANT NUMBER(1) := 2 ;
--
-- Supported task types
--
-- 1-Scheduled repository collection task
-- 2-Availability metric calculation task
-- 3-Asyncronously scheduled repo collection task.. will be depreciated
-- replace with g_task_type_snapshot for performance reasons
-- 4-Adhoc task which accepts p_context as input
-- 5-Snapshot dependent collections
G_TASK_TYPE_REPO CONSTANT NUMBER := 1 ;
G_TASK_TYPE_AVAIL CONSTANT NUMBER := 2 ;
G_TASK_TYPE_REPO_ASYNC CONSTANT NUMBER := 3 ;
G_TASK_TYPE_ADHOC CONSTANT NUMBER := 4 ;
G_TASK_TYPE_SNAPSHOT CONSTANT NUMBER := 5 ;
--
-- Task classes
--
G_TASK_CLASS_MIN CONSTANT NUMBER := 0 ;
G_TASK_CLASS_SHORT_RUNNING CONSTANT NUMBER := 0 ;
G_TASK_CLASS_LONG_RUNNING CONSTANT NUMBER := 1 ;
G_TASK_CLASS_MAX CONSTANT NUMBER := 1 ;
--
-- Task class descriptions
--
G_TASK_CLASS_DESC CONSTANT mgmt_short_string_array :=
mgmt_short_string_array('Short Running','Long Running') ;
--
-- Not a task class but to tell the worker to pick up
-- tasks of all classes
--
G_ALL_TASKS CONSTANT VARCHAR2(1) := ' ' ;
--
-- Worker stop modes
-- In Normal stop mode the worker will execute all collections and stop
-- In Immediate mode the worker will exit immediately
-- For both normal/immediate mode, the worker will only be removed when it
-- is running. If the worker is idle, you will have to wait for next execution
--
G_WORKER_STOP_NORMAL CONSTANT NUMBER(1) := 0 ;
G_WORKER_STOP_IMMEDIATE CONSTANT NUMBER(1) := 1 ;
--
-- Worker procedure
G_WORKER_PROC CONSTANT VARCHAR2(30) := 'EM_TASK.WORKER' ;
--
-- To get the next wake up time for the worker
--
G_INTERVAL_PROC CONSTANT VARCHAR2(40) := 'SYSDATE + 1/1440' ;
G_MINUTE CONSTANT NUMBER := 1/1440 ;
G_TASK_QUEUE_NAME CONSTANT VARCHAR2(11) := 'MGMT_TASK_Q' ;
G_MODULE_NAME CONSTANT VARCHAR2(10) := 'EM_TASK' ;
G_UTC_TIMEZONE CONSTANT VARCHAR2(3) := 'GMT' ;
EST_WORKER_NAME CONSTANT VARCHAR2(10) := 'EST_WORKER';
--
-- Locks the task so that others cannot update it
--
PROCEDURE lock_task(p_task_id IN NUMBER) ;
--
-- Procedure to get task information
--
PROCEDURE get_task_info(p_task_id IN NUMBER,
p_task_info OUT NOCOPY mgmt_collection_tasks%ROWTYPE,
p_lock_task IN BOOLEAN := FALSE
) ;
--
-- creates a task with the specified schedule
--
FUNCTION create_task(p_coll_schedule IN mgmt_coll_schedule_obj,
p_task_type IN NUMBER := 0 ,
p_timezone_region IN VARCHAR2 := G_UTC_TIMEZONE,
p_task_proc IN VARCHAR2 DEFAULT NULL,
p_context IN mgmt_namevalue_array DEFAULT NULL,
p_priority IN NUMBER DEFAULT NULL,
p_task_class IN NUMBER DEFAULT 0
)
RETURN NUMBER ;
--
-- Update task Queue Information
--
PROCEDURE update_task(p_task_id IN NUMBER,
p_task_type IN NUMBER := NULL,
p_priority IN NUMBER := NULL,
p_next_time_utc IN DATE := NULL
) ;
--
-- Schedule the next execution of the task
-- called by worker modules after completion
--
FUNCTION schedule_next_execution(p_task_info IN mgmt_collection_tasks%rowtype)
RETURN DATE ;
--
-- Updates the task to a new schedule
--
PROCEDURE update_task(p_task_id IN NUMBER,
p_coll_schedule IN mgmt_coll_schedule_obj,
p_timezone_region IN VARCHAR2) ;
--
-- Procedure to run a task manually
--
PROCEDURE run_task(p_task_id IN NUMBER) ;
--
-- run the tasks in AQ, to be called only from emd_maintenance
-- for dbconsole only, not to be used elsewhere
--
FUNCTION run_tasks(p_worker_id IN NUMBER,
p_task_class_list IN VARCHAR2 := G_ALL_TASKS)
RETURN NUMBER ;
--
-- Procedure to remove a task
--
-- p_task_in_aq:TRUE if the task is in AQ and needs to be removed
-- FALSE if task is not in AQ
--
PROCEDURE remove_task(p_task_id IN NUMBER,
p_remove_aq IN BOOLEAN := TRUE) ;
--
--function to return the next time for the task
--
FUNCTION get_next_time_utc(p_frequency_code IN NUMBER,
p_start_time IN DATE := NULL,
p_end_time IN DATE := NULL,
p_execution_hours IN NUMBER := NULL,
p_execution_minutes IN NUMBER := NULL,
p_interval IN NUMBER := NULL,
p_days IN MGMT_INTEGER_ARRAY := NULL,
p_months IN MGMT_INTEGER_ARRAY := NULL,
p_last_collection_time IN DATE := NULL,
p_timezone_region IN VARCHAR := G_UTC_TIMEZONE
)
RETURN DATE ;
--
-- Create a worker , do not call directly
--
FUNCTION create_worker(p_background IN BOOLEAN := TRUE,
p_task_class_list IN VARCHAR2 := NULL
)
RETURN NUMBER ;
--
-- get the next execution time for the dbms_job
--
FUNCTION get_next_execution_time RETURN DATE ;
--
-- the actual worker which picks up tasks in the queue
--
PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN, p_worker IN NUMBER);
PROCEDURE worker(p_worker_id IN NUMBER) ;
--
-- removes the worker
-- The worker calls this to remove itself when it is
-- signalled to stop using stop_worker
--
PROCEDURE remove_worker(p_worker_id IN NUMBER) ;
--
-- stop the worker identified by worker_id
-- The worker removes itself when it wakes up
--
PROCEDURE stop_worker(p_worker_id IN NUMBER,
p_stop_mode IN NUMBER := G_WORKER_STOP_NORMAL) ;
--
-- Stop the task workers by worker count
--
PROCEDURE stop_workers(p_worker_count IN NUMBER := NULL,
p_stop_mode IN NUMBER := G_WORKER_STOP_NORMAL,
p_task_class_list IN VARCHAR2 DEFAULT NULL) ;
--
-- enqueue task in the AQ
--
PROCEDURE enqueue_task(p_coll_queue_rec IN OUT mgmt_coll_queue_obj,
p_priority IN NUMBER DEFAULT G_TASK_PRIORITY_DEFAULT,
p_delay_sec IN NUMBER DEFAULT NULL,
p_queue_name IN VARCHAR2 := G_TASK_QUEUE_NAME
) ;
--
--dequeue task in the AQ
--
FUNCTION dequeue_task(p_dequeue_id IN NUMBER,
p_dequeue_mode IN NUMBER,
p_task_class_list IN VARCHAR2 DEFAULT G_ALL_TASKS,
p_queue_name IN VARCHAR2 DEFAULT G_TASK_QUEUE_NAME)
RETURN mgmt_coll_queue_obj ;
-- Set the minimum interval acceptable for collections, defaults to 1 minute
PROCEDURE set_min_interval(p_interval IN NUMBER) ;
--
--Set the maximum time a worker can run continously: Default : 1 hour
--
PROCEDURE set_max_run_time_worker(p_minutes IN NUMBER) ;
--
-- Procedure to detect and clean anamolies in task job.
-- This procedure runs every hour hour or so and detects anamolies
-- and corrects them. When we run into space issues, if we rollback, then we get
-- into a infinite loop with the same aq entr being processed unsuccessfully
-- many times. If we error out the AQ entries are not created.
PROCEDURE resubmit_failed_task ;
END em_task;
/
show err