To view package details:

To access the Customize Package page:

You may want to do this to delete or overwrite sensitive data in the files. To edit package files, you must first copy the files out of the package into a designated directory, edit the files with a text editor or other utility, and then copy the files back into the package, overwriting the original package files.

The following procedure assumes that the package is already created and contains diagnostic data.

To edit incident package files:

1. Access the Customize Package page for the desired incident package.

   See "Accessing the Customize Package Page" for instructions.

2. In the Packaging Tasks section, under the Scrub User Data heading, click Copy out Files to Edit contents.

   If prompted for host credentials, enter credentials and then click OK.

   The Copy Out Files page appears. It displays the name of the host to which you can copy files.

3. Do one of the following to specify a destination directory for the files:

   • Enter a directory path in the Destination Folder field.

   • Click the magnifying glass icon next to the Destination Folder field, and then complete the following steps:

     1. If prompted for host credentials, enter credentials for the host to which you want to copy out the files, and then click OK. (Select Save as Preferred Credential to avoid the prompt for credentials next time.)

        The Browse and Select: File or Directory window appears.

     2. Select the desired destination directory, and then click Select.

        The Browse and Select: File or Directory window closes, and the path to the selected directory appears in the Destination Folder field of the Copy Out Files page.

4. Under Files to Copy Out, select the desired files, and then click OK.

   Note:

   If you do not see the desired files, then they may be on another page. Click the Next link to view the next page. Continue clicking Next, or select from the list of file numbers (to the left of the Next link) until you see the desired files. You can then select the files and click OK.

   The Customize Package page returns, displaying a confirmation message that lists the files that were copied out.

5. Using a text editor or other utility, edit the files.

6. On the Customize Package page, in the Packaging Tasks section, under the Scrub User Data heading, click Copy in Files to Replace Contents.

   The Copy In Files page appears. It displays the files that you copied out.

7. Select the files to copy in, and then click OK.

   The files are copied into the package, overwriting the existing files. The Customize Package page returns, displaying a confirmation message that lists the files that were copied in.

To add an external file to an incident package:

1. Access the Customize Package page for the desired incident package.

   See "Accessing the Customize Package Page" for instructions.

2. Click the Files link to view the Files subpage.

   From this page, you can add and remove files to and from the package.

3. Click Add external files.

   The Add External File page appears. It displays the host name from which you may select a file.

4. Do one of the following to specify a file to add:

   • Enter the full path to the file in the File Name field.

   • Click the magnifying glass icon next to the File Name field, and then complete the following steps:

     1. If prompted for host credentials, enter credentials for the host on which the external file resides, and then click OK. (Select Save as Preferred Credential to avoid the prompt for credentials next time.)

     2. In the Browse and Select: File or Directory window, select the desired file and then click Select.

        The Browse and Select window closes, and the path to the selected file appears in the File Name field of the Add External File page.

5. Click OK.

   The Customize Package page returns, displaying the Files subpage. The selected file is now shown in the list of files.

To remove incident package files:

Most activities that you perform on a package, such as adding or removing files or creating a package zip file, are recorded in the log. You can also add your own notes to the log. This is especially useful if multiple database administrators are working with packages.

To view and update the incident package activity log:

Typically, you do so when you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "About Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor using Cloud Control:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

   See "Viewing Problems with the Support Workbench" for instructions.

2. In the Investigate and Resolve section, under the Resolve heading, click SQL Repair Advisor.

   
   Description of the illustration advisor_access1.gif

3. On the SQL Repair Advisor page, complete these steps:

   1. Modify the preset task name if desired, optionally enter a task description, modify or clear the optional time limit for the advisor task, and adjust settings to schedule the advisor to run either immediately or at a future date and time.

   2. Click Submit.

   A "Processing" page appears. After a short delay, the SQL Repair Results page appears.

   
   Description of the illustration sql_repair_advisor_results.gif

   A check mark in the SQL Patch column indicates that a recommendation is present. The absence of a check mark in this column means that the SQL Repair Advisor was unable to devise a patch for the SQL statement.

   Note:

   If the SQL Repair Results page fails to appear, then complete these steps to display it:

   1. Go to the Database Home page.

   2. From the Performance menu, select Advisors Home.

   3. On the Advisor Central page, in the Results list, locate the most recent entry for the SQL Repair Advisor.

   4. Select the entry and click View Result.

4. If a recommendation is present (there is a check mark in the SQL Patch column), then click View to view the recommendation.

   The Repair Recommendations page appears, showing the recommended patch for the statement.

5. Click Implement.

   The SQL Repair Results page returns, showing a confirmation message.

6. (Optional) Click Verify using SQL Worksheet to run the statement in the SQL worksheet and verify that the patch successfully repaired the statement.

Typically, you do so when you are notified of a critical error caused by your SQL statement and that you followed the workflow described in "About Investigating, Reporting, and Resolving a Problem".

You run the SQL Repair Advisor by creating and executing a diagnostic task using the DBMS_SQLDIAG package subprograms CREATE_DIAGNOSIS_TASK and EXECUTE_DIAGNOSIS_TASK respectively. The SQL Repair Advisor first reproduces the critical error and then tries to produce a workaround in the form of a SQL patch, which you can apply using the ACCEPT_SQL_PATCH subprogram.

To run the SQL Repair Advisor using the DBMS_SQLDIAG package subprograms:

1. Identify the problematic SQL statement

   Consider the SQL statement that gives a critical error:

   DELETE FROM t t1
   WHERE t1.a = 'a' AND
         ROWID <> (SELECT MAX(ROWID)
                   FROM t t2
                   WHERE t1.a = t2.a AND
                         t1.b = t2.b AND
                         t1.d = t2.d)
   

   You use the SQL Repair Advisor to repair this critical error.

2. Create a diagnosis task

   Run DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK. You can specify an optional task name, an optional time limit for the advisor task, and problem type. In the example below, we specify the SQL text, the task name as 'error_task' and a problem type as 'DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR'.

   DECLARE
     rep_out   CLOB;
     t_id      VARCHAR2(50);
   BEGIN
     t_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (
              sql_text => 'DELETE FROM t t1
                           WHERE t1.a = ''a'' AND
                                 ROWID <> (SELECT MAX(ROWID)
                                           FROM t t2
                                           WHERE t1.a = t2.a AND
                                                 t1.b = t2.b AND
                                                 t1.d = t2.d)',
              task_name => 'error_task',
              problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);
   

3. Execute the diagnosis task

   To execute the workaround generation and analysis phase of the SQL Repair Advisor, you run DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK with the task ID returned by the CREATE_DIAGNOSIS_TASK. After a short delay, the SQL Repair Advisor returns. As part of its execution, the SQL Repair Advisor keeps a record of its findings which can be accessed through the reporting facilities of SQL Repair Advisor.

   DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);
   

4. Generate a report for the diagnosis task

   The analysis of the diagnosis task is accessed using DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK. If the SQL Repair Advisor was able to find a workaround, it recommends a SQL Patch. A SQL Patch is similar to a SQL profile, but unlike the SQL Profile, it is used to workaround compilation or execution errors.

   rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);
   DBMS_OUTPUT.PUT_LINE ('Report : ' ||  rep_out);
   END;
   /
   

5. Apply the patch

   If a patch recommendation is present in the report, you can run DBMS_SQLDIAG.ACCEPT_SQL_PATCH to accept the patch. This procedure takes task name as an argument.

   EXECUTE DBMS_SQLDIAG.ACCEPT_SQL_PATCH(task_name => 'error_task', task_owner => 'SYS', replace => TRUE);
   

6. Test the patch

   Now that you have accepted the patch, you can rerun the SQL statement. This time, it will not give you the critical error. If you run explain plan for this statement, you will see that a SQL patch was used to generate the plan.

   DELETE FROM t t1
   WHERE t1.a = 'a' AND
         ROWID <> (SELECT max(rowid)
                   FROM t t2
                   WHERE t1.a = t2.a AND
                         t1.b = t2.b AND
                         t1.d = t2.d);
   

To view, disable, or remove a SQL patch using Cloud Control:

To disable a SQL patch using DBMS_SQLDIAG package subprogram:

Run the procedure DBMS_SQLDIAG.ALTER_SQL_PATCH by specifying the patch name to disable with the status value of DISABLED.

The following example disables the SQL patch sql_patch_12345.

EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('sql_patch_12345', 'STATUS', 'DISABLED');

To remove a SQL patch using DBMS_SQLDIAG package subprogram:

Run the procedure DBMS_SQLDIAG.DROP_SQL_PATCH by specifying the patch name to remove. The patch name can be obtained from the explain plan section or by querying the view DBA_SQL_PATCHES.

The following example removes the SQL patch sql_patch_12345.

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH('sql_patch_12345');

Patches can be exported out of one system and imported into another by using a staging table. Like with SQL diagnosis sets, the operation of inserting into the staging table is called as "pack", and the operation of creating patches from staging table data is called as "unpack".

To export and import a patch using the DBMS_SQLDIAG package subprograms:

1. Create a staging table owned by user 'SH' by calling DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH:

   EXEC DBMS_SQLDIAG.CREATE_STGTAB_SQLPATCH(
       table_name          =>  'STAGING_TABLE',
       schema_name         =>  'SH'); 
   

2. Call DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH one or more times to write SQL patch data into the staging table. In this case, copy data for all SQL patches in the DEFAULT category into a staging table owned by the current schema owner:

   EXEC DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH(
       staging_table_name  =>  'STAGING_TABLE'); 
   

3. In this case, only a single SQL patch SP_FIND_EMPLOYEE is copied into a staging table owned by the current schema owner:

   EXEC DBMS_SQLDIAG.PACK_STGTAB_SQLPATCH(
       patch_name          =>  'SP_FIND_EMPLOYEE',
       staging_table_name  =>  'STAGING_TABLE'); 
   

   The staging table can then be moved to another system using either data pump, import/export commands or using a database link.

4. Call DBMS_SQLDIAG.UNPACK_STGTAB_SQLPATCH to create SQL patches on the new system from the patch data in the staging table. In this case, change the name in the data for the SP_FIND_EMPLOYEE patch stored in the staging table to 'SP_FIND_EMP_PROD':

   exec dbms_sqldiag.remap_stgtab_sqlpatch(
      old_patch_name      =>  'SP_FIND_EMPLOYEE',
      new_patch_name      =>  'SP_FIND_EMP_PROD', 

The following example creates a quarantine configuration for an execution plan having the hash value of 3488063716 for a SQL statement having the SQL ID of 8vu7s907prbgr:

DECLARE
    quarantine_config VARCHAR2(30);
BEGIN
    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
                            SQL_ID => '8vu7s907prbgr', 
                            PLAN_HASH_VALUE => '3488063716');
END;
/

If you do not specify an execution plan or specify it as NULL, then the quarantine configuration is applied to all the execution plans of a SQL statement, except for those execution plans for which the execution plan-specific quarantine configurations are already created.

The following example creates a quarantine configuration for all the execution plans for a SQL statement having the SQL ID of 152sukb473gsk:

DECLARE
    quarantine_config VARCHAR2(30);
BEGIN
    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
                            SQL_ID => '152sukb473gsk');
END;
/

The following example creates a quarantine configuration for all the execution plans for a SQL statement 'select count(*) from emp':

DECLARE
    quarantine_config VARCHAR2(30);
BEGIN
    quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT(
                            SQL_TEXT => to_clob('select count(*) from emp'));
END;
/

The CREATE_QUARANTINE_BY_SQL_ID and CREATE_QUARANTINE_BY_SQL_TEXT functions return the name for the quarantine configuration, which can be used for specifying quarantine thresholds for an execution plan for a SQL statement using the DBMS_SQLQ.ALTER_QUARANTINE procedure.

You can specify quarantine thresholds for the following resources in a quarantine configuration using the DBMS_SQLQ.ALTER_QUARANTINE procedure:

• CPU time

• Elapsed time

• I/O in megabytes

• Number of physical I/O requests

• Number of logical I/O requests

In the following example, the quarantine threshold specified for CPU time is 5 seconds and elapsed time is 10 seconds for the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4.

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'CPU_TIME',
       PARAMETER_VALUE => '5');

    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'ELAPSED_TIME',
       PARAMETER_VALUE => '10');
END;
/

When the SQL statement is executed using the execution plan specified in this quarantine configuration, and if the Resource Manager threshold for CPU time is 5 seconds or less, or elapsed time is 10 seconds or less, then the SQL statement is not allowed to run.

Querying quarantine thresholds for a quarantine configuration

You can query a quarantine threshold for a quarantine configuration using the DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE function. The following example returns the quarantine threshold for CPU time consumption for the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

DECLARE
    quarantine_config_setting_value VARCHAR2(30);
BEGIN
    quarantine_config_setting_value := 
        DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE(
              QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
              PARAMETER_NAME  => 'CPU_TIME');
END;
/

Deleting quarantine thresholds from a quarantine configuration

You can delete a quarantine threshold from a quarantine configuration by specifying DBMS_SQLQ.DROP_THRESHOLD as the value for PARAMETER_VALUE. The following example deletes the quarantine threshold for CPU time consumption from the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'CPU_TIME',
       PARAMETER_VALUE => DBMS_SQLQ.DROP_THRESHOLD);
END;
/

The following example disables the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'ENABLED',
       PARAMETER_VALUE => 'NO');
END;
/

The following example enables the quarantine configuration having the name SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'ENABLED',
       PARAMETER_VALUE => 'YES');
END;
/

The DBA_SQL_QUARANTINE view contains the following information about each quarantine configuration:

• Quarantine configuration name
• SQL statement for which the quarantine configuration is applicable
• Hash value of the execution plan for which the quarantine configuration is applicable
• Status of the quarantine configuration (enabled or disabled)
• Status of automatic purging of the quarantine configuration (yes or no)
• Quarantine thresholds specified for the quarantine configuration:
  • CPU time
  • Elapsed time
  • I/O in megabytes
  • Number of physical I/O requests
  • Number of logical I/O requests
• Date and time when the quarantine configuration was created
• Date and time when the quarantine configuration was last executed

The following example disables automatic deletion of the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'AUTOPURGE',
       PARAMETER_VALUE => 'NO');
END;
/

The following example enables automatic deletion of the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.ALTER_QUARANTINE(
       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4',
       PARAMETER_NAME  => 'AUTOPURGE',
       PARAMETER_VALUE => 'YES');
END;
/

The following example deletes the quarantine configuration SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4:

BEGIN
    DBMS_SQLQ.DROP_QUARANTINE('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
END;
/

The following columns of the V$SQL and GV$SQL views show the quarantine information of execution plans of SQL statements:

• SQL_QUARANTINE: This column shows the name of the quarantine configuration for an execution plan of a SQL statement.

• AVOIDED_EXECUTIONS: This column shows the number of times an execution plan of a SQL statement was prevented from running after it was quarantined.

For example, you may have tested the quarantine configurations on a test database and confirmed that they have performed well. You may then want to load these quarantine configurations into a production database.

The following example describes the steps to transfer quarantine configurations from one database (source database) to another database (destination database) using the DBMS_SQLQ package subprograms:

1. Using SQL*Plus, connect to the source database as a user with the administrative privileges, and create a staging table using the DBMS_SQLQ.CREATE_STGTAB_QUARANTINE procedure.

   The following example creates a staging table named TBL_STG_QUARANTINE:

   BEGIN
     DBMS_SQLQ.CREATE_STGTAB_QUARANTINE (
       staging_table_name => 'TBL_STG_QUARANTINE');
   END;
   /
   

2. Add the quarantine configurations into the staging table, which you want to transfer to the destination database.

   The following example adds all the quarantine configurations starting with the name QUARANTINE_CONFIG_ into the staging table TBL_STG_QUARANTINE:

   DECLARE
     quarantine_configs NUMBER;
   BEGIN
     quarantine_configs := DBMS_SQLQ.PACK_STGTAB_QUARANTINE(
                               staging_table_name => 'TBL_STG_QUARANTINE',
                               name => 'QUARANTINE_CONFIG_%');
   END;
   /

   The DBMS_SQLQ.PACK_STGTAB_QUARANTINE function returns the number of quarantine configurations added to the staging table.

3. Export the staging table TBL_STG_QUARANTINE to a dump file using the Oracle Data Pump Export utility.

4. Transfer the dump file from the source database system to the destination database system.

5. On the destination database system, import the staging table TBL_STG_QUARANTINE from the dump file into the destination database using the Oracle Data Pump Import utility.

6. Using SQL*Plus, connect to the destination database as a user with the administrative privileges, and create the quarantine configurations from the imported staging table.

   The following example creates the quarantine configurations on the destination database based on all the quarantine configurations stored in the imported staging table TBL_STG_QUARANTINE:

   DECLARE
     quarantine_configs NUMBER;
   BEGIN
     quarantine_configs := DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE(
                               staging_table_name => 'TBL_STG_QUARANTINE');
   END;
   /

   The DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE function returns the number of quarantine configurations created in the destination database.

1. Using the Resource Manager, specify the execution time limit of 3 seconds for SQL statements executed by the user HR.

   The following code performs these operations by creating a complex resource plan using the DBMS_RESOURCE_MANAGER package subprograms:

   • creates a consumer group TEST_RUNAWAY_GROUP.
   • assigns the user HR to the TEST_RUNAWAY_GROUP consumer group.
   • creates a resource plan LIMIT_RESOURCE that terminates SQL statements when they exceed the execution time of 3 seconds.
   • assigns the LIMIT_RESOURCE resource plan to the TEST_RUNAWAY_GROUP consumer group.

   connect / as sysdba
   
   begin
   
     -- Create a pending area
     dbms_resource_manager.create_pending_area();
   
     -- Create a consumer group 'TEST_RUNAWAY_GROUP'
     dbms_resource_manager.create_consumer_group (
       consumer_group => 'TEST_RUNAWAY_GROUP',
       comment        => 'This consumer group limits execution time for SQL statements'
     );
   
     -- Map the sessions of the user 'HR' to the consumer group 'TEST_RUNAWAY_GROUP' 
     dbms_resource_manager.set_consumer_group_mapping(
       attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
       value          => 'HR',
       consumer_group => 'TEST_RUNAWAY_GROUP'
     );
   
     -- Create a resource plan 'LIMIT_RESOURCE'
     dbms_resource_manager.create_plan(
       plan    => 'LIMIT_RESOURCE',
       comment => 'Terminate SQL statements after exceeding total execution time'
     );
   
     -- Create a resource plan directive by assigning the 'LIMIT_RESOURCE' plan to 
     -- the 'TEST_RUNAWAY_GROUP' consumer group
     -- Specify the execution time limit of 3 seconds for SQL statements belonging to 
     -- the 'TEST_RUNAWAY_GROUP' group
     dbms_resource_manager.create_plan_directive(
       plan             => 'LIMIT_RESOURCE',
       group_or_subplan => 'TEST_RUNAWAY_GROUP',
       comment          => 'Terminate SQL statements when they exceed the' || 
                           'execution time of 3 seconds',
       switch_group     => 'CANCEL_SQL',
       switch_time      => 3,
       switch_estimate  => false
     );
   
     -- Allocate resources to the sessions not covered by the currently active plan 
     -- according to the OTHER_GROUPS directive
     dbms_resource_Manager.create_plan_directive(
       plan              => 'LIMIT_RESOURCE',
       group_or_subplan  => 'OTHER_GROUPS',
       comment           => 'Ignore'
     );
   
     -- Validate and submit the pending area
     dbms_resource_manager.validate_pending_area();
     dbms_resource_manager.submit_pending_area();
   
     -- Grant switch privilege to the 'HR' user to switch to the 'TEST_RUNAWAY_GROUP' 
     -- consumer group
     dbms_resource_manager_privs.grant_switch_consumer_group('HR',
                                                             'TEST_RUNAWAY_GROUP',
                                                             false);
     
     -- Set the initial consumer group of the 'HR' user to 'TEST_RUNAWAY_GROUP'
     dbms_resource_manager.set_initial_consumer_group('HR',
                                                      'TEST_RUNAWAY_GROUP');
   
   end;
   /
   
   -- Set the 'LIMIT_RESOURCE' plan as the top plan for the Resource Manager
   alter system set RESOURCE_MANAGER_PLAN = 'LIMIT_RESOURCE' scope = memory;
   
   -- Unlock the HR user and assign it the DBA role
   alter user hr identified by hr_user_password account unlock;
   grant dba to hr;
   
   -- Flush the shared pool
   alter system flush shared_pool;

2. Connect to the Oracle database as the HR user and run the SQL statement that exceeds the execution time limit of 3 seconds:

   select count(*)
   from employees emp1, employees emp2,
        employees emp3, employees emp4,
        employees emp5, employees emp6, 
        employees emp7, employees emp8,
        employees emp9, employees emp10
   where rownum <= 100000000;

   The SQL statement is terminated by the Resource Manager as it exceeds the execution time limit of 3 seconds and the following error message is displayed:

   ORA-00040: active time limit exceeded - call aborted

   The execution plan for the SQL statement is now added to the quarantine list, so that it is not allowed to run again.

3. Run the SQL statement again.

   Now the SQL statement should terminate immediately with the following error message, because its execution plan is quarantined:

   ORA-56955: quarantined plan used

4. View the details of the quarantined execution plan of the SQL statement by querying the v$sql and dba_sql_quarantine views.

   • Query the v$sql view. The v$sql view contains information about various statistics of the SQL statements including the quarantine statistics.

     select sql_text, plan_hash_value, avoided_executions, sql_quarantine 
     from v$sql 
     where sql_quarantine is not null;

     The output of this query is similar to the following:

     
     SQL_TEXT                               PLAN_HASH_VALUE   AVOIDED_EXECUTIONS   SQL_QUARANTINE
     ------------------------------------   ---------------   ------------------   ------------------------------------
     select count(*)                        3719017987        1                    SQL_QUARANTINE_3uuhv1u5day0yf6ed7f0c
     from employees emp1, employees emp2, 
          employees emp3, employees emp4, 
          employees emp5, employees emp6,
          employees emp7, employees emp8, 
          employees emp9, employees emp10
     where rownum <= 100000000;

     The sql_quarantine column shows the auto-generated name for the quarantine configuration for the execution plan of the SQL statement.

   • Query the dba_sql_quarantine view. The dba_sql_quarantine view contains information about the quarantine configurations of execution plans of the SQL statements.

     select sql_text, name, plan_hash_value, last_executed, enabled 
     from dba_sql_quarantine;

     The output of this query is similar to the following:

     
     SQL_TEXT                               NAME                                   PLAN_HASH_VALUE   LAST_EXECUTED                  ENABLED
     ------------------------------------   ------------------------------------   ---------------   ----------------------------   -------
     select count(*)                        SQL_QUARANTINE_3uuhv1u5day0yf6ed7f0c   3719017987        14-JAN-19 02.19.01.000000 AM   YES
     from employees emp1, employees emp2, 
          employees emp3, employees emp4, 
          employees emp5, employees emp6,
          employees emp7, employees emp8, 
          employees emp9, employees emp10
     where rownum <= 100000000;

     The name column shows the auto-generated name for the quarantine configuration for the execution plan of the SQL statement.

5. Clean up the example environment.

   The following code deletes all the database objects created for this example:

   connect / as sysdba
   
   begin
     for quarantineObj in (select name from dba_sql_quarantine) loop
       sys.dbms_sqlq.drop_quarantine(quarantineObj.name);
     end loop;
   end;
   /
   
   alter system set RESOURCE_MANAGER_PLAN = '' scope = memory;
   
   execute dbms_resource_manager.clear_pending_area();
   execute dbms_resource_manager.create_pending_area();
   execute dbms_resource_manager.delete_plan('LIMIT_RESOURCE');
   execute dbms_resource_manager.delete_consumer_group('TEST_RUNAWAY_GROUP');
   execute dbms_resource_manager.validate_pending_area();
   execute dbms_resource_manager.submit_pending_area();