Let's assume that the user OBAY has connected to the database through the group NEW_GROUP, and now we want to switch the CONSUMER GROUP from NEW_GROUP to SYS_GROUP.

We will use the procedure SWITCH_CONSUMER_GROUP_FOR_USER to do this. Now, through the user OBAY, we will check if the user is connected to the database through the group NEW_GROUP.

SELECT USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='OBAY';

Now, using the procedure SWITCH_CONSUMER_GROUP_FOR_USER, we will switch the user OBAY to the group SYS_PLAN. Note that the user OBAY is currently connected to the database, so at the moment this procedure is executed, all sessions belonging to the user OBAY will be switched to the group SYS_GROUP.

DECLARE

USER VARCHAR2(200);

CONSUMER_GROUP VARCHAR2(200);

BEGIN

USER := 'OBAY';

CONSUMER_GROUP := 'SYS_GROUP';

SYS.DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_ USER ( USER, CONSUMER_GROUP );

COMMIT;

END; 

Now let's ensure that the sessions belonging to the user OBAY have been switched to the consumer group named SYS_GROUP.

SELECT USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='OBAY';

 

Did you notice how the sessions belonging to the user OBAY were switched from the group NEW_GROUP to the group SYS_GROUP?

If the user OBAY had multiple sessions connected to the database, they would all be switched to the SYS_GROUP.

But what if we wanted to switch a specific session from these sessions to the SYS_GROUP? In such a scenario, we would use the procedure

SWITCH_CONSUMER_GROUP_FOR_SESS. To use this procedure, we need to know the SESSION_ID and SESSION_SERIAL.

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='OBAY'; 

If we want to switch this session to the group NEW_GROUP, we will use the procedure SWITCH_CONSUMER_GROUP_FOR_SESS.

Note that if there were multiple sessions belonging to this user, they would not be affected by this procedure.

DECLARE

SESSION_ID NUMBER

SESSION_SERIAL NUMBER

CONSUMER_GROUP VARCHAR2(200)

BEGIN

SESSION_ID := 741

SESSION_SERIAL := 32

CONSUMER_GROUP := 'NEW_GROUP'

SYS.DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_ SESS ( SESSION_ID, SESSION_SERIAL, CONSUMER_GROUP)

COMMIT

END

Now, if you perform a query about this session, you will find that it belongs to the group NEW_GROUP.

SELECT USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='OBAY'; 

There is another procedure that performs the operation of transferring the current session from one consumer group to another. This procedure is called SWITCH_CURRENT_CONSUMER_GROUP.

This procedure belongs to the DBMS_SESSION package. Note that this procedure operates on the current session, meaning it cannot be executed from another session while we are targeting a different session.

DECLARE

NEW_CONSUMER_GROUP VARCHAR2(200);

OLD_CONSUMER_GROUP VARCHAR2(200);

INITIAL_GROUP_ON_ERROR BOOLEAN;

BEGIN

NEW_CONSUMER_GROUP := ‘NEW_GROUP’;

OLD_CONSUMER_GROUP := ‘SYS_GROUP’;

INITIAL_GROUP_ON_ERROR := FALSE;

SYS.DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP ( NEW_CONSUMER_GROUP, OLD_CONSUMER_GROUP, INITIAL_GROUP_ON_ERROR );

COMMIT;

END;