oci-dbm-jobs

name

oci-dbm-jobs

description

Run SQL against OCI managed databases using OCI DB Management Jobs — no direct DB connectivity needed, DBM handles authentication. Use this skill whenever the user wants to execute SQL on a managed database, schedule data operations, check job history, monitor running jobs, run SELECT queries and retrieve results, or troubleshoot failed jobs. Triggers on: "run SQL on AIDB", "run SQL on DBMDEMO", "create a job to insert/update/delete", "execute SQL against", "schedule SQL", "run a SELECT and show me results", "query the database", "did any jobs fail", "show job history", "what jobs ran today", "monitor job run".

OCI DB Management Jobs

DBM Jobs let you execute SQL on any managed database without a direct DB connection — DBM routes through the Private Endpoint using a named credential you already set up.

---

Job types and what each needs

sql_type	Use for	Result delivery
PLSQL	Anonymous BEGIN ... END blocks — DDL, DML, procedures	None (side-effects only)
DML	INSERT / UPDATE / DELETE	Rows affected count
DDL	CREATE / DROP / ALTER	None
QUERY	SELECT statements	Object Storage bucket — required

---

Constraints to know upfront

Constraint	Detail
Named credential	Required for ADB SHARED. Must be USER_PRINCIPAL scope — not RESOURCE_PRINCIPAL
Terminal status	SUCCEEDED, FAILED, or COMPLETED — all three mean it's done
ADB job duration	Jobs on ADB typically take 30–45 minutes to show a terminal status. Poll patiently
No re-run	There is no re-run API. Create a new IMMEDIATE job to run again
QUERY results	Must be written to Object Storage. A pre-configured bucket is required before submitting

---

Workflow A — PLSQL / DML / DDL (no Object Storage needed)

1. Create and run

create_sql_job(
  name                = "my_job_YYYYMMDD",
  compartment_id      = <compartment_ocid>,
  managed_database_id = <db_ocid>,
  named_credential_id = <named_cred_ocid>,
  sql_text            = "BEGIN ... END;",
  sql_type            = "PLSQL",          -- or DML, DDL
  schedule_type       = "IMMEDIATE",
  description         = "optional"
)

Returns {"id": "ocid1.dbmgmtjob....", "lifecycle_state": "ACTIVE", ...}

2. Poll for completion

list_job_runs(compartment_id=..., job_id=<job_id>)

Watch run_status. It progresses: IN_PROGRESS → SUCCEEDED / FAILED / COMPLETED. ADB jobs take 30–45 minutes. Poll every 60 seconds; don't give up before 45 minutes.

If status is FAILED, call get_job_run(job_run_id=...) to see the error_message.

---

Workflow B — QUERY (SELECT) jobs with Object Storage results

QUERY jobs write SELECT output to Object Storage as a CSV file. You need a bucket before you submit the job.

Pre-flight: bucket and IAM policy

The bucket must already exist. If it doesn't, the user needs to create one via the OCI Console or CLI (oci os bucket create --name dbm-query-results --compartment-id <ocid>).

The DBM service also needs permission to write to the bucket. Confirm this IAM policy exists in the compartment (or tenancy):

allow service dbmgmt to manage objects in compartment <compartment-name>
  where target.bucket.name = '<bucket-name>'

Without this policy, QUERY jobs will fail with an Object Storage permission error.

Step 1 — Get the tenancy namespace

get_object_storage_namespace()

Returns {"namespace": "axcdefgh1234", ...}. Save this — it is required in every subsequent Object Storage call.

Step 2 — Submit the QUERY job

create_sql_job(
  name                 = "query_sales_YYYYMMDD",
  compartment_id       = <compartment_ocid>,
  managed_database_id  = <db_ocid>,
  named_credential_id  = <named_cred_ocid>,
  sql_text             = "SELECT * FROM wl_sales WHERE rownum <= 100",
  sql_type             = "QUERY",
  schedule_type        = "IMMEDIATE",
  result_namespace     = "<namespace from step 1>",
  result_bucket_name   = "dbm-query-results",
  result_object_prefix = "query_sales_YYYYMMDD"   -- use this same value in step 4
)

The response includes a result_location block confirming where results will be written.

Step 3 — Poll for completion

list_job_runs(compartment_id=..., job_id=<job_id>)

Same as Workflow A — wait for SUCCEEDED / FAILED / COMPLETED.

Step 4 — Read the results

get_query_job_results(
  namespace     = "<namespace>",
  bucket_name   = "dbm-query-results",
  object_prefix = "query_sales_YYYYMMDD",   -- must match result_object_prefix from step 2
  max_rows      = 500
)

Returns the CSV content up to max_rows rows. The response includes:

• object_name — the exact file path in the bucket
• content — CSV rows (header + data)
• total_lines / truncated — so you know if there's more
• If truncated=true, the user can download the full file from OCI Console → Object Storage → bucket

---

Workflow C — Check job history

get_job_execution_history(compartment_id=..., managed_database_id=...)

Returns a summary with total/succeeded/failed counts and a plain-English verdict.

For a raw list:

list_jobs(compartment_id=..., managed_database_id=...)
list_job_runs(compartment_id=..., managed_database_id=...)

---

Common failure modes

Error	Cause	Fix
password access mode not supported	Named credential uses RESOURCE_PRINCIPAL	Recreate credential with USER_PRINCIPAL scope
Feature only available for full diagnostics	DBM not fully enabled	Run oci-dbm-enable skill
resultLocation required or Invalid value for parameter resultLocation	QUERY job submitted without Object Storage params	Provide result_namespace + result_bucket_name
Object Storage permission denied (QUERY result write)	IAM policy missing for DBM service	Add: allow service dbmgmt to manage objects in compartment X where target.bucket.name='Y'
No result files found in bucket	Wrong prefix, or job hasn't completed	Confirm run_status=SUCCEEDED, check object_prefix matches exactly
Job stuck IN_PROGRESS for hours	Normal for ADB — wait up to 45 min	Keep polling
Invalid ScheduleType: RECURRING	Not a valid value	Use IMMEDIATE

---

Reporting

After any job completes, always report:

• Job name and ID
• Final run status
• Elapsed time (from time_submitted to time_updated)
• Error message if FAILED
• For QUERY jobs: the object_name of the result file, row count, and first N rows as a table

---

Demo environment values

AIDB (SQL tuning workload database)

• Compartment: ocid1.compartment.oc1..aaaaaaaac5jtulhrssnemmnzgr5tweter4hxslxnu4w5ophju6pi5hu3x54q
• Managed DB: ocid1.autonomousdatabase.oc1.phx.anyhqljtqgp2kriay3qico32iroukkboobce2suh2j4owta2xuqrbjoxudla
• Named credential: ocid1.dbmgmtnamedcredential.oc1.phx.amaaaaaaqgp2kria6ppjzla3w6y6jyq62mht3ccnrd5jtacvwnlo7s6hmyjq

DBMDEMO

• Managed DB: ocid1.autonomousdatabase.oc1.phx.anyhqljrqgp2kriabddvtbcg5ocsemiytiddszbpeskraixzbydmddestzha
• Named credential: ocid1.dbmgmtnamedcredential.oc1.phx.amaaaaaaqgp2kriarzkixaisk2ocnsawnmxgyd4feyqhgph7v6bghx24qy4q

Object Storage (if bucket exists)

• Call get_object_storage_namespace() to get the namespace — it is tenancy-specific
• Bucket name: ask the user; confirm it exists and the DBM IAM policy is in place