alter system set “_exadata_feature_on”=true scope=spfile;
\n shutdown immediate;
\n startup;<\/em><\/p>\nThen, you need to complete the following steps to setup Resource Manager:<\/p>\n
1. Creating a pending area:<\/p>\n
begin
\n dbms_resource_manager.create_pending_area();
\n end;
\n \/<\/p>\n
2. Create one or more resource consumer groups:<\/p>\n
begin
\n dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>’. SQL_LIMIT’,COMMENT=>’consumer group’);
\n end;
\n \/<\/p>\n
3. Create resource plans:<\/p>\n
begin
\n dbms_resource_manager.set_consumer_group_mapping(attribute => ORACLE_USER<\/em>,value => DBA1<\/em>,consumer_group =>SQL_LIMIT<\/em> );
\n dbms_resource_manager.create_plan(PLAN=> NEW_PLAN<\/em>,COMMENT=>Kill statement after exceeding total execution time<\/em>‘);
\n end;
\n \/<\/p>\n4. Create resource plan directives. The CANCEL_SQL group already exists by default:<\/p>\n
begin
\n dbms_resource_manager.create_plan_directive(
\n plan => ‘NEW_PLAN’,
\n group_or_subplan => ‘SQL_LIMIT’,
\n comment => ‘Kill statement after exceeding total execution time’,
\n switch_group=>’CANCEL_SQL’,
\n switch_time => 10,
\n switch_estimate=>false);<\/em>
\n end;<\/em>
\n \/
\n begin
\n dbms_resource_manager.create_plan_directive(PLAN=> NEW_PLAN<\/em>, GROUP_OR_SUBPLAN=>OTHER_GROUPS<\/em>,COMMENT=>leave others alone<\/em>, CPU_P1=>100 );
\n end;
\n \/<\/p>\n5. Validate and submit the pending area for plan, consumer group, and directives:
\n
\n begin
\n dbms_resource_manager.validate_pending_area();
\n end;
\n \/
\n begin
\n dbms_resource_manager.submit_pending_area();
\n end;
\n \/<\/em><\/p>\nHere you need to assign the grants and assign consumer groups to the users.<\/p>\n
6. Create a pending area for privileges, roles, and assigned users:<\/p>\n
begin
\n dbms_resource_manager.create_pending_area();
\n end;
\n \/<\/em><\/p>\n7. Grant switch privilege for resource consumer groups to users or roles.<\/p>\n
begin
\n dbms_resource_manager_privs.grant_switch_consumer_group(‘DBA1′,’SQL_LIMIT’,false);
\n end;
\n \/<\/em><\/p>\n8. Assign users to resource consumer groups.<\/p>\n
begin
\n dbms_resource_manager.set_initial_consumer_group(‘DBA1′,’SQL_LIMIT’);
\n end;
\n \/<\/em><\/p>\n9. Validate and submit the pending areas.<\/p>\n
begin
\n dbms_resource_manager.validate_pending_area();
\n end;
\n \/
\n begin
\n dbms_resource_manager.submit_pending_area();
\n end;
\n \/<\/em><\/p>\n10. Now, you have to update the Plan and again need to submit the pending area.<\/p>\n
begin
\n dbms_resource_manager.clear_pending_area;
\n dbms_resource_manager_create_pending_area;
\n end;
\n \/
\n begin<\/em>
\n dbms_resource_manager.update_plan_directive(plan=>NEW_PLAN<\/em>,group_or_subplan=>SQL_LIMIT<\/em>,new_switch_elapsed_time=>10, new_switch_for_call=>TRUE,new_switch_group=>CANCEL_SQL);
\n end;
\n \/<\/em><\/p>\n begin
\n dbms_resource_manager.validate_pending_area();
\n dbms_resource_manager.submit_pending_area;
\n end;
\n \/<\/em><\/p>\nNext steps<\/strong><\/p>\nThe preceding steps complete the Resource Manager Setup. After the complete them, all you need to do is to assign this Plan to an instance:<\/p>\n
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;<\/p>\n
Log in with the DBA1 user and issue a query that will exceed the elapsed time threshold of 10 seconds defined in the resource plan.<\/p>\n
You must execute the statement as the DBA1 user, and DBA1 must have access to the DBA views.<\/p>\n
select a.owner_name,b.product_name,c.location,d.country_code
\n from import_pr_table a, item_table b, locate_dealer_table c,country_table d;<\/em><\/p>\n ERROR at line 1:
\n ORA-00040: active time limit exceeded – call aborted<\/p>\n
Notice that in this case, the Resource Manager terminated the execution with an `ORA-00040` error.<\/p>\n
Can you find the SQL_ID of the statement? It is `3hdkutq4krg4c<\/em>`.<\/p>\nCreate SQL Quarantine<\/strong><\/p>\nYou can use the DBMS_SQLQ<\/strong> package to create a quarantine configuration for an execution plan of an SQL statement that needs to be quarantined. <\/p>\nYou can use SQL text or the SQL_ID of the statement to be quarantined, as shown in the following example:<\/p>\n
CREATE_QUARANTINE_BY_SQL_ID
\n or
\n CREATE_QUARANTINE_BY_SQL_TEXT<\/em><\/p>\n DECLARE
\n quarantine_sql VARCHAR2(30);
\n BEGIN
\n quarantine_sql := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => ‘3hdkutq4krg4c’);
\n END;
\n \/<\/em>
\n <\/p>\nAfter you create this quarantine configuration, you can specify quarantine thresholds by using the DBMS_SQLQ.ALTER_QUARANTINE<\/strong> procedure. <\/p>\n\nWith SQL Quarantine in place, it does not execute if you try to run the same SQL statement<\/p>\n
\n select a.owner_name,b.product_name,c.location,d.country_code from import_pr_table a, item_table b, locate_dealer_table c,country_table d;
\n ERROR at line 1:
\n ORA-56955: quarantined plan used<\/em><\/p>\nThe preceding error message states that the plan used for this statement is part of a quarantined plan. It canceled the query because it has crossed the threshold limit.<\/em><\/p>\nIf you check V$SQL<\/strong> view, you see two new columns: sql_quarantine<\/strong> and avoided_executions<\/strong>:<\/p>\n select sql_quarantine,avoided_executions from v$sql where sql_id=’3hdkutq4krg4c’;
\n SQL> select sql_quarantine,avoided_executions
\n 2 from v$sql where sql_id=’3hdkutq4krg4c’;<\/em><\/p>\n SQL_QUARANTINE AVOIDED_EXECUTIONS
\n ————— —————
\n SQL_QUARANTINE_3hdkutq4krg4c
\n 1 <\/em><\/p>\nConclusion<\/strong><\/p>\nThe SQL Quarantine feature helps improve the performance because it prevents the future execution of a costly, quarantined SQL statement.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL quarantine<\/p>\n","protected":false},"author":2,"featured_media":7178,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26292","content_type":"blog","created_by":"10130","authored_on":"2023-11-25T17:38:36+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Ravikant Sharma, Oracle Database Administrator, Rackspace Technology","seo_description":"Manage and limit resource use with SQL Quarantine in Oracle Resource Manager. Prevent overreaching of defined thresholds.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26292-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2021-12-01T17:38:36","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"SQL Quarantine Query Isolation | Rackspace Technology","field_topics":"","field_slug":"sql-quarantine","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7176","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/comments?post=7176"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7176\/revisions"}],"predecessor-version":[{"id":9106,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/blogs\/7176\/revisions\/9106"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media\/7178"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/media?parent=7176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/categories?post=7176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/de\/wp-json\/wp\/v2\/tags?post=7176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"quarantine](\"\/wp-content\/uploads\/2026\/blog_article_images\/26292-body-Picture1_35.png\")
<\/p>\n![\"quarantine](\"\/wp-content\/uploads\/2026\/blog_article_images\/26292-body-Picture2_26.png\")
<\/p>\n![\"quarantine](\"\/wp-content\/uploads\/2026\/blog_article_images\/26292-body-Picture3_28.png\")
<\/p>\n