Create and Monitor Jobs

You can create a SQL job to perform administrative operations on a single Managed Database or a Database Group.

1. Go to the Managed database details page for the Managed Database or the Managed databases page for the Database Group, for which you want to create the job.
2. In the left pane, click Jobs. For a Managed Database, first expand Management.
3. In the Jobs section, click the Definitions tab and then click Create job.
4. In the Create job panel:
   1. Select one of the following source options to specify how to create the job:
      • Create new: Select to create a job by specifying the SQL command and any bind variables.
      • Create from job template: Select to create a job using an Oracle-defined or user-defined job template. Select a template in the Job template drop-down list. If the compartment in which the job template resides is different from the compartment displayed, click Change compartment and select another compartment.
        Note
        
        When you select a job template, the job name, job description, SQL type, SQL command, and bind variable definitions are populated based on the selected template. You can edit the job name and description and provide values for the bind variables. You cannot change the SQL type, SQL command, or bind variable definitions.
   2. Provide the following information for the SQL job:
      1. Job name: Enter a unique name for the job.
      2. Job description: Optionally, enter a description for the job.
      3. SQL type: If you selected Create new as the source, select the SQL type. The available options are:
         • Query
         • DML
         • DDL
         • PL*SQL
      4. Choose compartment: Select the compartment in which you want to create the job. The compartment in which the Managed Database or Database Group resides is selected by default, however, you can opt to create the job in a different compartment.
      5. Timeout: Depending on the time the SQL job is likely to take, select one of the options in the drop-down list, and based on the selected option the job is prioritized.
   3. Provide schedule details for the job by selecting one of the following options:
      • One time (immediately): Select to execute the job once (immediately).
      • One time (later): Select to execute the job once (later) and specify the time when it should be executed.
      • Repeating: Select to execute a job multiple times at the specified frequency. If you want to specify an end date for the scheduled job, then select the Define end date check box.
   4. Select one of the available options in the Credential type drop-down list to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
      Note
      
      

      • If creating a job for a Database Group, you cannot use named credentials and you will have to select the New credential option in the Credential type drop-down list and specify the user name, user password secret, and role. Also, when creating a job for a Database Group, the database credentials must apply to all the databases in the Database Group.
      • When creating a scheduled job (to be executed once or multiple times), if A policy is required... message is displayed, click Add policy to view the resource principal policy that grants Managed Database resources the permission to access the secret, and automatically add it. The Oracle Cloud Infrastructure IAM service creates the policy and it's added to a collection of policies called DBMgmt_Resource_Policy in the IAM service.
   5. For a Query type job, select the Object Storage bucket in which the job results will be stored. The Oracle object storage for job results section with the Bucket name drop-down list is only displayed if you've selected the Query type job. The Object Storage bucket can be in the same compartment as the job or in another compartment, and you must have the required Object Storage service permissions to select the bucket.

      When creating a scheduled job (to be executed once or multiple times), if A policy is required... message is displayed, click Add policy to view the resource principal policy that grants Managed Database resources the permission to write the results of the scheduled job to an Object Storage bucket, and automatically add it. The IAM service creates the policy and it's added to a collection of policies called DBMgmt_Resource_Policy in the IAM service.

      For information on:

      • Object Storage service permissions, see Additional Permissions Required to Use Diagnostics & Management.
      • IAM policies, see Managing Policies.
   6. If you selected Create new as the source, click Load SQL to load the SQL command or enter it in the SQL command field. If you're entering a SQL command, then do not add a trailing ; or /.

      If you selected Create from job template as the source, review the SQL command populated from the selected template. The SQL command cannot be edited.

   7. If you selected Create new as the source, optionally add bind variables for Query, DML, and PL*SQL type jobs in the Bind variables section. If bind variables are used as placeholders that must be replaced with valid values for the statement to execute successfully, then click Add bind variable and specify the following information:
      1. Position: Select the bind variable position.
      2. Type: Select the type to specify if the bind variable is an in-bind or out-bind variable.
      3. Data type: Select the relevant data type.
      4. Array type: Enter the array type for the Array of numbers and Array of strings data types. Note that this field is not displayed if you select Number, String, or CLOB in the Data type field.
      5. Value: Enter the bind variable value.

      To add more bind variables, click Add bind variable and provide the required information.

      If you selected Create from job template as the source, enter a value in the Value field for each input bind variable defined in the selected template. The bind variable definitions cannot be changed.

      For information on bind variables, see Bind Variables and Cursors in Oracle Database SQL Tuning Guide.

   8. Optionally, click Show advanced options to add free-form or defined tags to the job. If you have the permissions required to create a job, then you also have permissions to add free-form tags. To add a defined tag, you must have permissions to use the tag namespace.

      For information on:

      • Tagging concepts and the permissions required to work with tags, see Overview of Tagging.
      • How to add a tag during resource creation, see To add a tag during resource creation.
   9. Click Create job.

dbmgmtJobExecutionsCount[1m]{managedDbId = "<OCID of the Oracle Database>", status = "Failed"}.mean()

You can monitor the runs and executions of a submitted job for a Managed Database or Database Group. A job execution is created for each of the Managed Databases associated with the job, and a job run is the sum of executions of a specific job that ran on a scheduled date.

To view the job runs and executions:

1. Go to the Managed database details or the Database Group Managed databases page.
2. In the left pane, expand Management and click Jobs.
3. In the Jobs section, click the Definitions tab.

   Ensure that the compartment in which the job was created is selected in the Compartment applied filter.

4. Click the name of the job. Alternatively, you can also click the corresponding Actions icon () and click View details.

The Job runs page is displayed and it provides the details of the job, job runs and executions, and the associated Managed Databases on which the job is executed. On this page, you can perform tasks such as editing, moving, and deleting the job and using tags. In addition, you can also monitor each job execution and its status. To do so:

1. Expand a job run, and click a job execution link.

   The job execution link is in the following format:

   <Name of job>_<Name of Managed Database>_YYYY-MM-DD'T'HH:MM:SS.SSS
2. On the Job execution output page, click Job execution details and review the details such as the job execution status, the SQL associated with the execution, and the duration of the job execution. Note that the duration is the time taken for the entire job execution workflow to complete and not just the time taken for the SQL to execute on the database.
3. For a Query type job, click Download output to download the job execution output to a .txt file.
   Note
   
   The Download output option is only available for Query type jobs and you can use this option only if you have the required Oracle Cloud Infrastructure Object Storage service permissions. For more information, see Additional Permissions Required to Use Diagnostics & Management.

You can create and manage reusable job templates to create SQL jobs for Managed Databases or Database Groups.

1. Go to the Managed database details page for a Managed Database or the Managed databases page for a Database Group.
2. In the left pane, click Jobs. For a Managed Database, first expand Management.
3. In the Jobs section, click the Job templates tab and then click Create job template.
4. In the Create job template panel:
   1. Provide the following information for the job template:
      1. Enter a name for the job template.
      2. Optionally, enter a description for the job template.
      3. Select the SQL type.
      4. Select the compartment in which you want to create the job template.
   2. Click Load SQL to load the SQL command or enter it in the SQL command field.
   3. If the SQL command uses bind variables, add the corresponding definitions in the Bind variables section. Click Add bind variable and specify the following information:
      1. Position: Select the bind variable position.
      2. Type: The available option is IN, which specifies an input bind variable.
      3. Data type: Select the relevant data type.
      4. Array type: Enter the array type for the Array of numbers and Array of strings data types. Note that this field is not displayed if you select Number, String, or CLOB in the Data type field.
      5. Default value: Enter a default value for the bind variable.

      To add more bind variables, click Add bind variable and provide the required information.

      You can specify default values for input bind variables in the template and provide or update their values when creating a job from the template.

      For information on bind variables, see Bind Variables and Cursors in Oracle Database SQL Tuning Guide.

   4. Optionally, click Show advanced options to add free-form or defined tags to the job template. If you have the permissions required to create a job template, then you also have permissions to add free-form tags. To add a defined tag, you must have permissions to use the tag namespace.

      For information on:

      • Tagging concepts and the permissions required to work with tags, see Overview of Tagging.
      • How to add a tag during resource creation, see To add a tag during resource creation.
   5. Click Create job template.