Re: Executing DBMS_RESOURCE_MANAGER_PRIVS in ATP

Hi Arpit,

DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP has been deprecated since 11gR1:
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-A04EBF47-3DC5-40F6-8993-7BDEA859E346 I am not aware about any other ways of setting the initial consumer group without utilizing mapping rules or using logon triggers.

PL/SQL Packages and Types Reference says that the Initial Consumer Group is set by the mapping rules (DBA_RSRC_GROUP_MAPPINGS, DBA_RSRC_MAPPING_PRIORITY):
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-A04EBF47-3DC5-40F6-8993-7BDEA859E346

> *Note: This procedure is deprecated in Release 11gR1. While the procedure
> remains available in the package, Initial Consumer Group is set by the
> session-to-consumer group mapping rules.*
>

The Administrator's guide is also in line with it and says that the initial consumer group of a session is determined by the mapping rules that you configure:
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-5562D553-06CC-4AA8-85A0-040BAF2D4005 There is no mention of DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP. In fact, that piece of the Administrator's guide is quite different from the corresponding 11gR1 counterpart that clearly mentions the INITIAL_RSRC_CONSUMER_GROUP column from *_USERS: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dbrm004.htm#ADMIN11875

Thus said, the official Oracle's way to set the initial consumer group is using the session-to-consumer group mapping rules. Although we still can use DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP, which is discouraged per the links above, Oracle seems to remove that capability in its Autonomous offerings.

Best regards,
Mikhail

On Thu, 14 Nov 2019 at 10:00, Arpit Aggarwal <arpitdba2019_at_gmail.com> wrote:

> Thanks Mikhail for the response.
>
> I understand there are only 5 consumer groups but when i see the initial
> default consumer group of a newly created user , i see it as
> "default_consumr_group".
>
> Is this different from other consumer groups that i see ?
>
>
> Thanks,
> Arpit
>
> On Tue 12 Nov, 2019, 2:53 PM Mikhail Velikikh, <mvelikikh_at_gmail.com>
> wrote:
>
>> Is there an alternative way this can be done ?
>>>
>>
>> I am disposed to think that it is intended to use one of the provided
>> services:
>>
>> https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/manage-priorities.html#GUID-80E464A7-8ED4-45BB-A7D6-E201DD4107B7
>>
>> There are mappings for them:
>> select * from dba_rsrc_group_mappings;
>>
>> The supplied plan directives by itself can be changed:
>>
>> https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/manage-cpu-shares.html#GUID-8FEE49FF-CDEE-4433-B812-0AAABA8DEC7F
>>
>> It is possible to achieve the functionality similar to setting the
>> initial consumer group, by creating a logon trigger that calls
>> DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP:
>>
>> declare
>> v_old_consumer_group varchar2(ora_max_name_len);
>> begin
>> dbms_session.switch_current_consumer_group(
>> new_consumer_group => 'TPURGENT',
>> old_consumer_group => v_old_consumer_group,
>> initial_group_on_error => false);
>> end;
>> /
>>
>> However, it is not recommended:
>>
>> https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/manage-priorities.html#GUID-80E464A7-8ED4-45BB-A7D6-E201DD4107B7
>>
>>
>>> *Note:After connecting to the database using one service, do not attempt
>>> to manually switch that connection to a different service by simply
>>> changing the consumer group of the connection. When you connect using a
>>> service, Autonomous Transaction Processing performs more actions to
>>> configure the connection than just setting its consumer group.*
>>>
>>
>> That should be due to the OLTP_LOGON trigger that executes some ALTER
>> SESSION commands depended on the service name.
>>
>>
>> Best regards,
>> Mikhail Velikikh
>>
>>
>>
>> On Tue, 12 Nov 2019 at 07:12, Arpit Aggarwal <arpitdba2019_at_gmail.com>
>> wrote:
>>
>>> I am myself a dba and on creating this ATP database from OCI, we get
>>> only admin user created which is not a super user and hence it cannot have
>>> dba priveleges.
>>>
>>>
>>> Thanks,
>>> Arpit
>>>
>>> On Tue 12 Nov, 2019, 12:01 PM Leng, <lkaing_at_gmail.com> wrote:
>>>
>>>> Has your dba granted you execute privileges on this package?
>>>>
>>>> Cheers,
>>>> Leng
>>>>
>>>> On 12 Nov 2019, at 3:39 pm, Arpit Aggarwal <arpitdba2019_at_gmail.com>
>>>> wrote:
>>>>
>>>> 
>>>> Yes Leng , understood that point but in ATP we get only ADMIN user
>>>> logins and it doesn't have sys privileges.
>>>> Is there an alternative way this can be done ?
>>>>
>>>> On Tue, 12 Nov 2019 at 01:58, Leng <lkaing_at_gmail.com> wrote:
>>>>
>>>>> The below error means the object doesn’t exist or you’re lacking
>>>>> privileges to see/use it
>>>>>
>>>>> 'SYS.DBMS_RESOURCE_MANAGER_PRIVS' must be declared
>>>>>
>>>>>
>>>>> Cheers,
>>>>> Leng
>>>>>
>>>>> > On 11 Nov 2019, at 5:22 pm, Arpit Aggarwal <arpitdba2019_at_gmail.com>
>>>>> wrote:
>>>>> >
>>>>> > 'SYS.DBMS_RESOURCE_MANAGER_PRIVS' must be declared
>>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l