Rem
Rem $Header: dbmsrmad.sql 16-feb-2000.15:59:05 wixu Exp $
Rem
Rem dbmsrmad.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999, 2000. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmsrmad.sql - DBMS Resource Manager package for administrators.
Rem
Rem    DESCRIPTION
Rem      Specification for the resource manager package.
Rem
Rem    NOTES
Rem    
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    wixu        02/16/00 - wixu_bug_1177932_fix
Rem    arhee       09/03/99 - add create_simple_plan
Rem    akalra      04/07/99 - Add switch_plan
Rem    akalra      06/01/98 - Insert comment about max_active_sess_target_p1   
Rem    akalra      05/18/98 - Change interface
Rem    akalra      01/19/98 - Created
Rem

CREATE OR REPLACE PACKAGE dbms_resource_manager AUTHID CURRENT_USER AS

  --
  -- create a new resource plan
  --
  -- Input arguments:
  --   plan                       - name of resource plan
  --   cpu_mth                    - allocation method for CPU resources
  --   max_active_sess_target_mth - allocation method for max. active sessions
  --   parallel_degree_limit_mth  - allocation method for degree of parallelism
  --   comment                    - user's comment
  --
  PROCEDURE create_plan(plan IN VARCHAR2,
                        comment IN VARCHAR2,
                        cpu_mth IN VARCHAR2 DEFAULT 'EMPHASIS',
                        max_active_sess_target_mth IN VARCHAR2 
                        DEFAULT 'MAX_ACTIVE_SESS_ABSOLUTE',
                        parallel_degree_limit_mth IN VARCHAR2 DEFAULT 
                        'PARALLEL_DEGREE_LIMIT_ABSOLUTE');
 
  --
  -- update an existing resource plan. NULL arguments leave the resource plan
  -- unchanged in the dictionary.
  --
  -- Input arguments:
  --   plan                           - name of resource plan
  --   new_comment                    - new user's comment
  --   new_cpu_mth                    - name of new allocation method for CPU 
  --                                    resources
  --   new_max_active_sess_target_mth - name of new method for max. active 
  --                                    sessions
  --   new_parallel_degree_limit_mth  - name of new method for degree of 
  --                                    parallelism
  --
  PROCEDURE update_plan(plan IN VARCHAR2,
                        new_comment IN VARCHAR2 DEFAULT NULL,
                        new_cpu_mth IN VARCHAR2 DEFAULT NULL,
                        new_max_active_sess_target_mth IN VARCHAR2 
		        DEFAULT NULL,
                        new_parallel_degree_limit_mth IN VARCHAR2
			DEFAULT NULL);

  --
  -- delete an existing resource plan
  --
  -- Input arguments:
  --   plan        - name of resource plan to delete
  --
  PROCEDURE delete_plan(plan IN VARCHAR2);


  --
  -- delete an existing resource plan cascade
  --
  -- Input arguments:
  --   plan        - name of plan
  --
  PROCEDURE delete_plan_cascade(plan IN VARCHAR2);


  --
  -- create a new resource consumer group
  --
  -- Input arguments:
  --   consumer_group - name of consumer group
  --   comment        - user's comment
  --   cpu_mth        - name of CPU resource allocation method.

  --
  PROCEDURE create_consumer_group(consumer_group IN VARCHAR2,
                                  comment IN VARCHAR2,
                                  cpu_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN');
               
  --
  -- update an existing resource consumer group
  --
  -- Input arguments:
  --   consumer_group - name of consumer group
  --   new_comment    - new user's comment
  --   new_cpu_mth    - name of new method for CPU resource allocation
  --
  PROCEDURE update_consumer_group(consumer_group IN VARCHAR2,
                                  new_comment IN VARCHAR2 DEFAULT NULL,
                                  new_cpu_mth IN VARCHAR2 DEFAULT NULL);
 
  --
  -- delete an existing resource consumer group
  --
  -- Input arguments:
  --   consumer_group - name of consumer group to be deleted
  --
  PROCEDURE delete_consumer_group(consumer_group IN VARCHAR2);

 --
 -- create a new resource plan directive
 --
 -- Input arguments:
 --   plan                      - name of resource plan
 --   group_or_subplan          - name of consumer group or subplan
 --   comment                   - comment for the plan directive
 --   cpu_p1                    - first parameter for the CPU resource 
 --                               allocation method
 --   cpu_p2                    - second parameter for the CPU resource
 --                               allocation method
 --   cpu_p3                    - third parameter for the CPU resource 
 --                               allocation method
 --   cpu_p4                    - fourth parameter for the CPU resource
 --                               allocation method
 --   cpu_p5                    - fifth parameter for the CPU resource
 --                               allocation method
 --   cpu_p6                    - sixth parameter for the CPU resource
 --                               allocation method
 --   cpu_p7                    - seventh parameter for the CPU resource
 --                               allocation method
 --   cpu_p8                    - eighth parameter for the CPU resource  
 --                               allocation method
 --   max_active_sess_target_p1 - first parameter for the max. active sessions
 --                               allocation method
 --                               (RESERVED FOR FUTURE USE)
 --   parallel_degree_limit_p1  - first parameter for the degree of parallelism
 --                               allocation method
 -- 
 PROCEDURE create_plan_directive(plan IN VARCHAR2,
                                 group_or_subplan IN VARCHAR2,
                                 comment IN VARCHAR2,
                                 cpu_p1 IN NUMBER DEFAULT NULL,
                                 cpu_p2 IN NUMBER DEFAULT NULL,
                                 cpu_p3 IN NUMBER DEFAULT NULL,
                                 cpu_p4 IN NUMBER DEFAULT NULL,
                                 cpu_p5 IN NUMBER DEFAULT NULL,
                                 cpu_p6 IN NUMBER DEFAULT NULL,
                                 cpu_p7 IN NUMBER DEFAULT NULL,
                                 cpu_p8 IN NUMBER DEFAULT NULL,
                                 max_active_sess_target_p1 IN NUMBER 
                                 DEFAULT 1000000,
                                 parallel_degree_limit_p1 IN NUMBER 
                                 DEFAULT 1000000);

 --
 -- update a plan directive. A plan directive is specified by the plan
 -- and group_or_subplan.
 --
 -- Input arguments:
 --   plan                          -  name of resource plan
 --   group_or_subplan              -  name of group or subplan 
 --   new_comment                   -  comment for the plan directive
 --   new_cpu_p1                    -  first parameter for the CPU allocation
 --                                    method
 --   new_cpu_p2                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p3                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p4                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p5                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p6                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p7                    -  parameter for the CPU allocation
 --                                    method
 --   new_cpu_p8                    -  parameter for the CPU allocation
 --                                    method
 --   new_max_active_sess_target_p1 -  first parameter for the max. active 
 --                                    sessions allocation method
 --                                    (RESERVED FOR FUTURE USE)
 --   new_parallel_degree_limit_p1  -  first parameter for the degree of 
 --                                    parallelism allocation method
 -- 
 PROCEDURE update_plan_directive(plan IN VARCHAR2,
                             group_or_subplan IN VARCHAR2,
                             new_comment IN VARCHAR2 DEFAULT NULL,
                             new_cpu_p1 IN NUMBER DEFAULT NULL,
                             new_cpu_p2 IN NUMBER DEFAULT NULL,
                             new_cpu_p3 IN NUMBER DEFAULT NULL,
                             new_cpu_p4 IN NUMBER DEFAULT NULL,
                             new_cpu_p5 IN NUMBER DEFAULT NULL,
                             new_cpu_p6 IN NUMBER DEFAULT NULL,
                             new_cpu_p7 IN NUMBER DEFAULT NULL,
                             new_cpu_p8 IN NUMBER DEFAULT NULL,
                             new_max_active_sess_target_p1 IN NUMBER 
                             DEFAULT NULL,
                             new_parallel_degree_limit_p1 IN NUMBER 
                             DEFAULT NULL);

 --
 -- delete a plan directive. A plan directive is uniquely specified by the plan
 -- and group_or_subplan
 --
 -- Input arguments:
 --   plan                  -  name of resource plan
 --   group_or_subplan      -  name of group or subplan 
 -- 
 PROCEDURE delete_plan_directive(plan IN VARCHAR2,
                                 group_or_subplan IN VARCHAR2);

 --
 -- create a pending area. Creates a temporary workspace to make changes.
 --
 -- Input arguments: None
 --
 PROCEDURE create_pending_area;


 --
 -- clear the pending area. Discards all the changes in the pending area
 -- and renders it inactive.
 --
 -- Input arguments: None
 --
 PROCEDURE clear_pending_area;

 --
 -- validate the pending area. Validates all the changes made in the pending
 -- area.
 --
 -- Input arguments: None
 --
 PROCEDURE validate_pending_area;


 --
 -- submit the  pending area. Commits all the changes if they are valid and
 -- renders the pending area inactive.  If not, leaves the pending area 
 -- unchanged.
 --
 -- Input arguments: None
 --
 PROCEDURE submit_pending_area;

 --
 -- set the initial consumer group of a user.
 --
 -- Input arguments:
 --   user           - name of the user
 --   consumer_group - the user's initial consumer group
 --
 PROCEDURE set_initial_consumer_group(user IN VARCHAR2,
                                      consumer_group IN VARCHAR2);
 
 --
 -- switch the consumer group for all currently logged on users with the
 -- given name
 --
 -- Input arguments:
 --   user           - name of the user
 --   consumer_group - name of the consumer group to switch to
 --
 PROCEDURE switch_consumer_group_for_user(user IN VARCHAR2,
                                          consumer_group IN VARCHAR2);
 
 --
 -- switch the consumer group for a session.
 --
 -- Input arguments:
 --   session_id     - SID column from the view V$SESSION
 --   serial         - SERIAL# column from the view V$SESSION
 --   consumer_group - name of the consumer group to switch to
 --
 PROCEDURE switch_consumer_group_for_sess(session_id IN NUMBER,
                                          session_serial IN NUMBER,
                                          consumer_group IN VARCHAR2);

 --
 -- switch to the specified plan. This makes the specified plan active.
 --
 -- Input arguments:
 --   plan_name      - name of plan to switch to
 --
 PROCEDURE switch_plan(plan_name IN VARCHAR2);

 --
 -- create a simple plan that implicitly has SYS_GROUP at 100% at level 1
 -- and OTHER_GROUPS at 100% at level 3, while the specified input groups 
 -- are all at level 2.
 --
 -- Input arguments:
 --   simple_plan     - name of plan to be created
 --   consumer_group1 - name of consumer group
 --   group1_cpu      - percentage for group
 --   consumer_group2 - name of consumer group
 --   group2_cpu      - percentage for group
 --   consumer_group3 - name of consumer group
 --   group3_cpu      - percentage for group
 --   consumer_group4 - name of consumer group
 --   group4_cpu      - percentage for group
 --   consumer_group5 - name of consumer group
 --   group5_cpu      - percentage for group
 --   consumer_group6 - name of consumer group
 --   group6_cpu      - percentage for group
 --   consumer_group7 - name of consumer group
 --   group7_cpu      - percentage for group
 --   consumer_group8 - name of consumer group
 --   group8_cpu      - percentage for group

 PROCEDURE create_simple_plan (simple_plan IN VARCHAR2 DEFAULT NULL,
                               consumer_group1 IN VARCHAR2 DEFAULT NULL, 
                               group1_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group2 IN VARCHAR2 DEFAULT NULL, 
                               group2_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group3 IN VARCHAR2 DEFAULT NULL, 
                               group3_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group4 IN VARCHAR2 DEFAULT NULL, 
                               group4_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group5 IN VARCHAR2 DEFAULT NULL, 
                               group5_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group6 IN VARCHAR2 DEFAULT NULL, 
                               group6_cpu IN NUMBER DEFAULT NULL, 
                               consumer_group7 IN VARCHAR2 DEFAULT NULL, 
                               group7_cpu IN NUMBER DEFAULT NULL,  
                               consumer_group8 IN VARCHAR2 DEFAULT NULL, 
                               group8_cpu IN NUMBER DEFAULT NULL);

END dbms_resource_manager;
/
DROP PUBLIC SYNONYM dbms_resource_manager
/
CREATE PUBLIC SYNONYM dbms_resource_manager FOR sys.dbms_resource_manager
/
GRANT EXECUTE ON dbms_resource_manager TO public
/