能否在共享基础设施自治数据库（Autonomous Database Shared）的特定Schema级别设置查询执行时间限制？

Great question! You're right that the Service Console's "Set Resource Management Rules" applies to the entire database instance, but Oracle Autonomous Database Shared does support schema-level query execution time limits—you just need to use PL/SQL with the DBMS_RESOURCE_MANAGER package instead of the console's global settings. Here's a step-by-step guide:

Prerequisites #

First, you'll need administrative privileges (like the ADMINISTER_RESOURCE_MANAGER role, which is typically held by the ADMIN user in Autonomous Database).

Step 1: Create a Custom Resource Consumer Group #

This group will hold the query timeout rule for your target schema:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'SCHEMA_QUERY_TIMEOUT_GROUP',
    comment        => 'Resource group enforcing query time limits for specific schema'
  );
END;
/

Step 2: Add a Timeout Directive to the Resource Plan #

We'll attach the timeout rule to Autonomous Database's default resource plan (DEFAULT_MAINTENANCE_PLAN). Replace 300 with your desired timeout in seconds (example below sets a 5-minute limit):

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan             => 'DEFAULT_MAINTENANCE_PLAN',
    group_or_subplan => 'SCHEMA_QUERY_TIMEOUT_GROUP',
    comment          => 'Limit query execution to 5 minutes',
    MAX_EXECUTION_TIME => 300 -- Timeout in seconds
  );
END;
/

Step 3: Map Your Target Schema to the Consumer Group #

Link the schema you want to restrict to the custom resource group. Replace YOUR_TARGET_SCHEMA_NAME with the actual schema name:

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute       => DBMS_RESOURCE_MANAGER.CLIENT_USER,
    value           => 'YOUR_TARGET_SCHEMA_NAME',
    consumer_group  => 'SCHEMA_QUERY_TIMEOUT_GROUP'
  );
END;
/

Step 4: Verify the Configuration #

Check that the mapping and timeout rule are correctly applied:

-- Confirm the schema-to-group mapping
SELECT attribute, value, consumer_group 
FROM DBA_RSRC_GROUP_MAPPINGS 
WHERE consumer_group = 'SCHEMA_QUERY_TIMEOUT_GROUP';

-- Check the timeout setting in the resource plan directive
SELECT plan, group_or_subplan, max_execution_time
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE group_or_subplan = 'SCHEMA_QUERY_TIMEOUT_GROUP';

Notes #

• If you need to adjust the timeout later, use DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE instead of CREATE_PLAN_DIRECTIVE.
• Queries from the target schema that exceed the timeout will be automatically terminated with an ORA-00040: active time limit exceeded - call aborted error.
• Make sure you're using the correct resource plan for your environment—ADB Shared uses DEFAULT_MAINTENANCE_PLAN by default, but if you've created a custom plan, use that instead.

内容的提问来源于stack exchange，提问作者Engin SENEL