Rem
Rem $Header: catsched.sql 19-jan-98.11:55:55 akalra Exp $
Rem
Rem catrm.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catrm.sql - Catalog script for dbms Resource Manager package
Rem
Rem    DESCRIPTION
Rem      Installs packages for the DBMS Resource Manager.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    akalra      06/24/99 - rename some files
Rem    akalra      11/20/98 - grant sys. privilege to export and import roles
Rem                         - set up more built-ins and their privileges
Rem    klcheung    11/17/98 - move rmexptab$ creation
Rem    akalra      08/17/98 - support for import export
Rem    akalra      06/17/98 - Allow object grant
Rem    akalra      06/12/98 - inicongroup -> defschclass
Rem    akalra      06/10/98 - Change -1 to UWORDMAXVAL
Rem    akalra      06/09/98 - Change file names
Rem    akalra      06/03/98 - Change views                                     
Rem    akalra      05/26/98 - Change and add views                             
Rem    akalra      05/22/98 - Use new interface                                
Rem    akalra      01/19/98 - Created
Rem

-- Create the library where 3GL callouts will reside
CREATE OR REPLACE LIBRARY dbms_rmgr_lib TRUSTED as STATIC
/

-- Load DBMS RESOURCE MANAGER interface packages
@@dbmsrmin.plb
@@prvtrmin.plb
@@dbmsrmad.sql
@@prvtrmad.plb
@@dbmsrmpr.sql
@@prvtrmpr.plb
@@prvtrmie.plb
@@dbmsrmpe.plb
@@prvtrmpe.plb
@@dbmsrmge.plb
@@prvtrmge.plb
@@dbmsrmpa.plb
@@prvtrmpa.plb

-- install mandatory and system managed (but non-mandatory) objects.
execute dbms_rmin.install;

-- set initial consumer group for SYS and SYSTEM to be SYS_GROUP
execute dbms_resource_manager.set_initial_consumer_group('SYSTEM','SYS_GROUP');
execute dbms_resource_manager.set_initial_consumer_group('SYS','SYS_GROUP');

-- grant system privilege to IMP_FULL_DATABASE and EXP_FULL_DATABASE
execute dbms_resource_manager_privs.grant_system_privilege('IMP_FULL_DATABASE', 'ADMINISTER_RESOURCE_MANAGER', FALSE);
execute dbms_resource_manager_privs.grant_system_privilege('EXP_FULL_DATABASE', 'ADMINISTER_RESOURCE_MANAGER', FALSE);

-- Set up export actions.  Delete existing export data
DELETE FROM exppkgobj$ where package like 'DBMS_RMGR_%'
/
DELETE FROM exppkgact$ where package like 'DBMS_RMGR_%'
/

-- package to export resource plans
INSERT INTO exppkgobj$ (package,schema,class,type#,prepost,level#)
values('DBMS_RMGR_PLAN_EXPORT', 'SYS', 1, 47, 1, 1000)
/

-- package to export consumer groups
INSERT INTO exppkgobj$ (package,schema,class,type#,prepost,level#)
values('DBMS_RMGR_GROUP_EXPORT', 'SYS', 1, 48, 1, 1000)
/

-- package to export plan directives.  This also does the procedural actions
-- work.
INSERT INTO exppkgact$ (package,schema,class,level#)
values('DBMS_RMGR_PACT_EXPORT', 'SYS', 1, 1000)
/

---------------------------------------------------------------------------------
--                              VIEWS                                          --
---------------------------------------------------------------------------------

--
-- Create the view DBA_RSRC_PLANS
--
create or replace view DBA_RSRC_PLANS
   (PLAN,NUM_PLAN_DIRECTIVES,CPU_METHOD,MAX_ACTIVE_SESS_TARGET_MTH,
    PARALLEL_DEGREE_LIMIT_MTH,COMMENTS,STATUS,MANDATORY)
as
select name,num_plan_directives,cpu_method,mast_method,pdl_method,
description,status,decode(mandatory,1,'YES','NO') from resource_plan$
/
comment on table DBA_RSRC_PLANS is
'All the resource plans'
/
comment on column DBA_RSRC_PLANS.PLAN is
'Plan name'
/
comment on column DBA_RSRC_PLANS.NUM_PLAN_DIRECTIVES is
'Number of plan directives for the plan'
/
comment on column DBA_RSRC_PLANS.CPU_METHOD is
'CPU resource allocation method for the plan'
/
comment on column DBA_RSRC_PLANS.MAX_ACTIVE_SESS_TARGET_MTH is
'maximum active sessions target resource allocation method for the plan'
/
comment on column DBA_RSRC_PLANS.PARALLEL_DEGREE_LIMIT_MTH is
'parallel degree limit resource allocation method for the plan'
/
comment on column DBA_RSRC_PLANS.COMMENTS is
'Text comment on the plan'
/
comment on column DBA_RSRC_PLANS.STATUS is
'PENDING if it is part of the pending area, ACTIVE otherwise'
/
comment on column DBA_RSRC_PLANS.MANDATORY is
'Whether the plan is mandatory'
/
drop public synonym DBA_RSRC_PLANS
/
create public synonym DBA_RSRC_PLANS for DBA_RSRC_PLANS
/
grant select on DBA_RSRC_PLANS to SELECT_CATALOG_ROLE
/

--
-- Create the view DBA_RSRC_CONSUMER_GROUPS
--
create or replace view DBA_RSRC_CONSUMER_GROUPS
   (CONSUMER_GROUP,CPU_METHOD,COMMENTS,STATUS,MANDATORY)
as
select name,cpu_method,description,status,decode(mandatory,1,'YES','NO')
from resource_consumer_group$
/
comment on table DBA_RSRC_CONSUMER_GROUPS is
'all the resource consumer groups'
/
comment on column DBA_RSRC_CONSUMER_GROUPS.CONSUMER_GROUP is
'consumer group name'
/
comment on column DBA_RSRC_CONSUMER_GROUPS.CPU_METHOD is
'CPU resource allocation method for the consumer group'
/
comment on column DBA_RSRC_CONSUMER_GROUPS.COMMENTS is
'Text comment on the consumer group'
/
comment on column DBA_RSRC_CONSUMER_GROUPS.STATUS is
'PENDING if it is part of the pending area, ACTIVE otherwise'
/
comment on column DBA_RSRC_CONSUMER_GROUPS.MANDATORY is
'Whether the consumer group is mandatory'
/
drop public synonym DBA_RSRC_CONSUMER_GROUPS
/
create public synonym DBA_RSRC_CONSUMER_GROUPS for DBA_RSRC_CONSUMER_GROUPS
/
grant select on DBA_RSRC_CONSUMER_GROUPS to SELECT_CATALOG_ROLE
/

--
-- create the view DBA_RSRC_PLAN_DIRECTIVES
--
create or replace view DBA_RSRC_PLAN_DIRECTIVES
   (PLAN, GROUP_OR_SUBPLAN, TYPE, CPU_P1, CPU_P2, CPU_P3, CPU_P4, CPU_P5,
    CPU_P6, CPU_P7, CPU_P8, MAX_ACTIVE_SESS_TARGET_P1, PARALLEL_DEGREE_LIMIT_P1,
    COMMENTS,STATUS,MANDATORY)
as
select plan,group_or_subplan,decode(is_subplan,1,'PLAN','CONSUMER_GROUP'),
decode(cpu_p1,4294967295,0,cpu_p1), decode(cpu_p2,4294967295,0,cpu_p2), 
decode(cpu_p3,4294967295,0,cpu_p3), decode(cpu_p4,4294967295,0,cpu_p4),
decode(cpu_p5,4294967295,0,cpu_p5), decode(cpu_p6,4294967295,0,cpu_p6),
decode(cpu_p7,4294967295,0,cpu_p7), decode(cpu_p8,4294967295,0,cpu_p8),
decode(max_active_sess_target_p1,4294967295,0,max_active_sess_target_p1),
decode(parallel_degree_limit_p1,4294967295,0,parallel_degree_limit_p1), 
description, status, decode(mandatory,1,'YES','NO') from resource_plan_directive$
/
comment on table DBA_RSRC_PLAN_DIRECTIVES is
'all the resource plan directives'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.PLAN is
'Name of the plan to which this directive belongs'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.GROUP_OR_SUBPLAN is
'Name of the consumer group/sub-plan referred to'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.TYPE is
'Whether GROUP_OR_SUBPLAN refers to a consumer group or a plan'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P1 is
'first parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P2 is
'second parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P3 is
'third parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P4 is
'fourth parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P5 is
'fifth parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P6 is
'sixth parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P7 is
'seventh parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.CPU_P8 is
'eight parameter for the CPU resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.max_active_sess_target_p1 is
'first parameter for the maximum active sessions target resource allocation 
method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.parallel_degree_limit_p1 is
'first parameter for the parallel degree limit resource allocation method'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.COMMENTS is
'Text comment on the plan directive'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.STATUS is
'PENDING if it is part of the pending area, ACTIVE otherwise'
/
comment on column DBA_RSRC_PLAN_DIRECTIVES.MANDATORY is
'Whether the plan directive is mandatory'
/
drop public synonym DBA_RSRC_PLAN_DIRECTIVES
/
create public synonym DBA_RSRC_PLAN_DIRECTIVES for DBA_RSRC_PLAN_DIRECTIVES
/
grant select on DBA_RSRC_PLAN_DIRECTIVES to SELECT_CATALOG_ROLE
/

--
-- create view DBA_RSRC_CONSUMER_GROUP_PRIVS
--
create or replace view DBA_RSRC_CONSUMER_GROUP_PRIVS
   (GRANTEE,GRANTED_GROUP,GRANT_OPTION,INITIAL_GROUP)
as
select ue.name, g.name, 
       decode(min(o.option$),1,'YES','NO'),
       decode(ue.defschclass,g.name,'YES','NO')
from sys.user$ ue, sys.resource_consumer_group$ g, sys.objauth$ o
where o.obj# = g.obj# and o.grantee# = ue.user#
group by ue.name, g.name, decode(ue.defschclass,g.name,'YES','NO')
/
comment on table DBA_RSRC_CONSUMER_GROUP_PRIVS is
'Switch privileges for consumer groups'
/
comment on column DBA_RSRC_CONSUMER_GROUP_PRIVS.GRANTEE is
'Grantee name'
/
comment on column DBA_RSRC_CONSUMER_GROUP_PRIVS.GRANTED_GROUP is
'consumer group granted to the grantee'
/
comment on column DBA_RSRC_CONSUMER_GROUP_PRIVS.GRANT_OPTION is
'whether the grantee can grant the privilege to others' 
/
drop public synonym DBA_RSRC_CONSUMER_GROUP_PRIVS
/
create public synonym DBA_RSRC_CONSUMER_GROUP_PRIVS for DBA_RSRC_CONSUMER_GROUP_PRIVS
/
grant select on DBA_RSRC_CONSUMER_GROUP_PRIVS to SELECT_CATALOG_ROLE
/

--
-- create view USER_RSRC_CONSUMER_GROUP_PRIVS
--
create or replace view USER_RSRC_CONSUMER_GROUP_PRIVS
   (GRANTED_GROUP,GRANT_OPTION,INITIAL_GROUP)
as
select g.name, decode(o.option$,1,'YES','NO'),
decode(u.defschclass, g.name, 'YES', 'NO')
from sys.resource_consumer_group$ g, sys.user$ u, sys.objauth$ o
where o.obj# = g.obj# and o.grantee# = u.user#
and o.grantee# = userenv('SCHEMAID')
/
comment on table USER_RSRC_CONSUMER_GROUP_PRIVS is
'Switch privileges for consumer groups for the user'
/
comment on column USER_RSRC_CONSUMER_GROUP_PRIVS.GRANTED_GROUP is
'consumer groups to which the user can switch'
/
comment on column USER_RSRC_CONSUMER_GROUP_PRIVS.GRANT_OPTION is
'whether the user can grant the privilege to others'
/
drop public synonym USER_RSRC_CONSUMER_GROUP_PRIVS
/
create public synonym USER_RSRC_CONSUMER_GROUP_PRIVS for USER_RSRC_CONSUMER_GROUP_PRIVS
/
grant select on USER_RSRC_CONSUMER_GROUP_PRIVS to PUBLIC with grant option
/

--
-- create view DBA_RSRC_MANAGER_SYSTEM_PRIVS
--
create or replace view DBA_RSRC_MANAGER_SYSTEM_PRIVS
   (GRANTEE,PRIVILEGE,ADMIN_OPTION)
as
select u.name,spm.name,decode(min(sa.option$),1,'YES','NO')
from sys.user$ u, system_privilege_map spm, sys.sysauth$ sa
where sa.grantee# = u.user# and sa.privilege# = spm.privilege
and sa.privilege# = -227 group by u.name, spm.name
/
comment on table DBA_RSRC_MANAGER_SYSTEM_PRIVS is
'system privileges for the resource manager'
/
comment on column DBA_RSRC_MANAGER_SYSTEM_PRIVS.GRANTEE is
'Grantee name'
/
comment on column DBA_RSRC_MANAGER_SYSTEM_PRIVS.PRIVILEGE is
'name of the system privilege'
/
comment on column DBA_RSRC_MANAGER_SYSTEM_PRIVS.ADMIN_OPTION is
'whether the grantee can grant the privilege to others'
/
drop public synonym DBA_RSRC_MANAGER_SYSTEM_PRIVS
/
create public synonym DBA_RSRC_MANAGER_SYSTEM_PRIVS for DBA_RSRC_MANAGER_SYSTEM_PRIVS
/
grant select on DBA_RSRC_MANAGER_SYSTEM_PRIVS to SELECT_CATALOG_ROLE
/

--
-- create view USER_RSRC_MANAGER_SYSTEM_PRIVS
--
create or replace view USER_RSRC_MANAGER_SYSTEM_PRIVS
   (PRIVILEGE,ADMIN_OPTION)
as
select spm.name,decode(min(sa.option$),1,'YES','NO')
from sys.user$ u, system_privilege_map spm, sys.sysauth$ sa
where sa.grantee# = u.user# and sa.privilege# = spm.privilege
and sa.privilege# = -227 and sa.grantee# = userenv('SCHEMAID')
group by spm.name
/
comment on table USER_RSRC_MANAGER_SYSTEM_PRIVS is
'system privileges for the resource manager for the user'
/
comment on column USER_RSRC_MANAGER_SYSTEM_PRIVS.PRIVILEGE is
'name of the system privilege'
/
comment on column USER_RSRC_MANAGER_SYSTEM_PRIVS.ADMIN_OPTION is
'whether the user can grant the privilege to others'
/
drop public synonym USER_RSRC_MANAGER_SYSTEM_PRIVS
/
create public synonym USER_RSRC_MANAGER_SYSTEM_PRIVS for USER_RSRC_MANAGER_SYSTEM_PRIVS
/
grant select on USER_RSRC_MANAGER_SYSTEM_PRIVS to PUBLIC with grant option
/
