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.*
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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx> wrote:
Has your dba granted you execute privileges on this package?
Cheers,
Leng
On 12 Nov 2019, at 3:39 pm, Arpit Aggarwal <arpitdba2019@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>wrote:
'SYS.DBMS_RESOURCE_MANAGER_PRIVS' must be declared
