Often switching off resource plan feature of Oracle database is desired.
\nUnfortunately setting parameter RESOURCE_MANAGER_PLAN to empty string does not help, because it does not disable switching to another resource plan, e.g. with dbms_scheduler.<\/p>\n
Following procedure prevents this switching.<\/p>\n
1. Creation of a dummy resource plan, e.g. TEST_PLAN:<\/p>\n
The only limitation is the number of available CPUs specified in parameter CPU_COUNT, resource manager enforces this limitation. The resource plan INTERNAL_PLAN is for internal use, so I would not use it.<\/p>\n 2. Setting of parameter resource_manager_plan:<\/p>\n This setting prevents switching to another resource plan. <\/p>\n Caveat:<\/p>\n With this setting, deletion of specified resource plan (e.g. TEST_PLAN) causes database to crash: <\/p>\n Often switching off resource plan feature of Oracle database is desired. Unfortunately setting parameter RESOURCE_MANAGER_PLAN to empty string does not help, because it does not disable switching to another resource plan, e.g. with dbms_scheduler. Following procedure prevents this switching. 1. Creation of a dummy resource plan, e.g. TEST_PLAN: BEGIN DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); DBMS_RESOURCE_MANAGER.CREATE_PLAN( plan =>’TEST_PLAN’, comment […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-12187","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\n
\nBEGIN
\n DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.CREATE_PLAN(
\n plan =>'TEST_PLAN',
\n comment => 'plan for TEST',
\n mgmt_mth => 'EMPHASIS',
\n active_sess_pool_mth => 'ACTIVE_SESS_POOL_ABSOLUTE',
\n parallel_degree_limit_mth => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE');
\n DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
\n plan =>'TEST_PLAN',
\n group_or_subplan => 'OTHER_GROUPS',
\n comment => 'dummy directive'
\n );
\n DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
\nEND;
\n\/
\n<\/code><\/p>\n
\nalter system set resource_manager_plan='FORCE:TEST_PLAN';
\n<\/code><\/p>\n
\nalter system set resource_manager_plan='FORCE:TEST_PLAN';
\nBEGIN
\n DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.DELETE_PLAN('TEST_PLAN');
\n DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
\n DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
\n END;
\n \/
\nBEGIN
\n*
\nERROR at line 1:
\nORA-03113: end-of-file on communication channel
\nProcess ID: 73473
\nSession ID: 24 Serial number: 17611
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"