:

ORA-29381: plan/consumer_group referred to by another plan and cannot be deleted

The Problem

When attempting to delete a consumer group from your resource manager configuration, you receive the following error message:

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

PL/SQL procedure successfully completed.

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

PL/SQL procedure successfully completed.

EXEC DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP=>’REPORTING_GROUP’);

BEGIN DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP=>’REPORTING_GROUP’); END;
*
ERROR at line 1:
ORA-29381: plan/consumer_group REPORTING_GROUP referred to by another plan and cannot be deleted
ORA-06512: at “SYS.DBMS_RMIN”, line 134
ORA-06512: at “SYS.DBMS_RESOURCE_MANAGER”, line 142
ORA-06512: at line 1

The Cause

In order to remove the consumer group, you need to delete the plan directive first, as it is dependent upon the consumer group; it belongs to it.

The solution

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>’REPORTING_RESTRICTED’,group_or_subplan=>’REPORTING_GROUP’);

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

If you want to find out which data dictionary views can be used to find out about the details of your consumer groups, plans and plan directives, run the following query and look through the views:

SELECT *
FROM dict
WHERE table_name LIKE ‘%RSRC%’;

Like it, share it...

Category: Resource Manager


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *