/*
|| Database Resource Management Listing 1
||
|| Contains:
|| - An example of setting up a simple plan
|| - Useful queries
||   for demonstration of Oracle Database Resource Management (DRM) features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of
|| Oracle Database Resource Management (DRM) and should be carefully proofread
|| before executing it against any existing Oracle database to insure
|| that no potential damage can occur.
||
*/

CONNECT SYS / AS SYSDBA;
 
-----
-- Listing 1.1: Create a simple DRM plan using
-- DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN
-----
BEGIN
	DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
        simple_plan => 'PEAKTIME'
       ,consumer_group1 => 'OLTP'
       ,group1_cpu => 70
       ,consumer_group2 => 'DSS'
       ,group2_cpu => 20
       ,consumer_group3 => 'ADHOC'
       ,group3_cpu => 10
   );
END;
/
 
-----
-- Listing 1.2: Show all resource consumer groups
-----
COL consumer_group      FORMAT A24  HEADING 'Consumer Group'
COL cpu_method          FORMAT A18  HEADING 'CPU Method'
COL status              FORMAT A10  HEADING 'Status'
COL mandatory           FORMAT A06  HEADING 'Manda-|tory?'
COL comments            FORMAT A32  HEADING 'Comments'
SELECT
     consumer_group
    ,cpu_method
    ,status
    ,mandatory
    ,comments
  FROM dba_rsrc_consumer_groups
;
 
-----
-- Listing 1.3: Show all resource plans
-----
COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL num_plan_directives       FORMAT 9999 HEADING '# of|Plan|Dirs'
COL cpu_method                FORMAT A18  HEADING 'CPU Method'
COL active_sess_pool_mth      FORMAT A32  HEADING 'Active|Session|Pool|Method'
COL parallel_degree_limit_mth FORMAT A32  HEADING 'Parallel|Limit|Method'
COL queueing_mth              FORMAT A18  HEADING 'Queueing|Method'
COL status                    FORMAT A10  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A32  HEADING 'Comments'
SELECT
     plan
    ,num_plan_directives
    ,cpu_method
    ,active_sess_pool_mth
    ,parallel_degree_limit_mth
    ,queueing_mth
    ,status
    ,mandatory
  FROM dba_rsrc_plans
;
 
-----
-- Listing 1.4: Show all resource plan directives
-----
COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL group_or_subplan          FORMAT A12  HEADING 'Group or|SubPlan'
COL type                      FORMAT A15  HEADING 'Type'
COL cpu_p1                    FORMAT 999  HEADING 'CPU|1 %'
COL cpu_p2                    FORMAT 999  HEADING 'CPU|2 %'
COL cpu_p3                    FORMAT 999  HEADING 'CPU|3 %'
COL status                    FORMAT A08  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A24  HEADING 'Comments'

SELECT
     plan
    ,group_or_subplan
    ,type
    ,cpu_p1
    ,cpu_p2
    ,cpu_p3
    ,status
  FROM dba_rsrc_plan_directives
 WHERE plan = 'PEAKTIME'
;

-----
-- Listing 1.4: Show users' assigned resource consumer groups
-----
COL initial_rsrc_consumer_group  FORMAT A24  HEADING 'Resource|Consumer|Group'
COL username                     FORMAT A12  HEADING 'User Name'
SELECT
	 initial_rsrc_consumer_group,
    username
  FROM dba_users
 ORDER BY 1,2
;