Skip to content

Instantly share code, notes, and snippets.

@germanviscuso

germanviscuso/DBMS_METRIC_v0.3_user_guide.md

Created September 5, 2026 06:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save germanviscuso/48ea5f85e514fc65f351b280889a76ad to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save germanviscuso/48ea5f85e514fc65f351b280889a76ad to your computer and use it in GitHub Desktop.
Download ZIP
DBMS_METRIC
Raw
dbms_metric_v0.3.sql
-- ============================================================================
-- DBMS_METRIC v0.3 — Full Deployment Script
--
-- Registry-driven publisher of CUSTOM METRICS to OCI Monitoring, from inside
-- Oracle Autonomous AI Database. A customer registers a SELECT statement, a
-- metric name and (once, at install) a namespace. A scheduler job turns the
-- rows into PostMetricData JSON and POSTs it, signed, to the OCI telemetry
-- ingestion endpoint every 60 seconds.
--
-- Target: Autonomous AI Database — Dedicated (26ai / 23.26+). Also works on
-- Serverless; the shipped gap-filler metrics use only DBA_ views.
--
-- Run in order:
-- PART A — as ADMIN (schema, privileges, existence oracle)
-- PART B — as ADMIN (tables, seed registry, package)
-- PART C — as METRIC_EXPORTER (signing credential — you run this, not ADMIN)
-- PART D — as ADMIN (configure, verify, schedule)
--
-- CHANGELOG
-- v0.2 -> v0.3 (everything here came out of a real first-user run)
-- + check_credential() - answers "does OCI accept who we say we are?" on its
-- own. 200 and 404 both prove the signature was verified; only 401
-- (ORA-20401) is a failure, and it now prints the four things that can be
-- wrong instead of an ORA number to decode.
-- + reset_to_shipped() - canary on, other built-ins off; reports the custom
-- metrics and the added grants it deliberately did NOT touch.
-- ! c_version MOVED FROM THE SPEC INTO THE BODY. It was in the spec, so a
-- body-only patch left the package reporting a stale version - which is
-- exactly what happened between v0.1 and v0.2.
-- ! test_publish() now recognises ORA-20401 as a rejected signature rather
-- than falling through to "check the credential exists", which sent the
-- first user looking in entirely the wrong place.
-- ! list_metrics() resolves the effective resource group and marks it
-- "(default)" instead of showing a bare NULL. A NULL there left you unable
-- to tell which resource group a metric would publish under, and that is
-- the top reason a metric looks missing in Metrics Explorer.
-- + health_summary() reports spec and body compile times, so a partial
-- patch is visible directly.
-- + start_push() now VALIDATES the interval against min_schedule_secs /
-- max_schedule_secs (defaults 60s and 3600s, both in metric_config).
-- Cadence was always configurable; nothing stopped you asking for 1s,
-- which costs money and buys nothing because OCI aggregates custom
-- metrics at a one-minute minimum. health_summary() shows the interval
-- and the allowed range.
-- ~ every EXEC in this script is now an explicit BEGIN ... END; block:
-- EXEC is a client-side shorthand and swallows an adjacent comment line,
-- producing a baffling PLS-00103 on a pasted block.
--
-- Lineage: this is DBMS_NEWRELIC's registry engine (auto-derived dimensions via
-- DBMS_SQL.DESCRIBE_COLUMNS, dryrun-before-register, forensic push log, pipelined
-- diagnostics) with the OCI transport from the ADB-D ORDS Layer B exporter.
--
-- ----------------------------------------------------------------------------
-- WHAT THIS IS FOR — read before seeding your registry
--
-- OCI already publishes 50+ metrics for this database in the reserved namespace
-- oci_autonomous_database (CPU, sessions, storage, IOPS, latency, availability).
-- Re-publishing those here duplicates data you already have AND bills you for
-- custom metric ingestion. DBMS_METRIC is for what OCI does NOT give you:
-- application state, business invariants, and database facts outside the
-- platform metric set.
--
-- That is why only ONE metric ships enabled (the canary). The four gap-filler
-- metrics ship DISABLED, and their views are deliberately NOT granted — see
-- the note in PART A.6.
-- ============================================================================
-- ############################################################################
-- ## ##
-- ## PART A — as ADMIN ##
-- ## ##
-- ############################################################################
SET SERVEROUTPUT ON
-- ----------------------------------------------------------------------------
-- A.1 Teardown (nuclear: drops the user and everything it owns, including the
-- DBMS_CLOUD credential and the scheduler job)
-- ----------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP USER metric_exporter CASCADE';
DBMS_OUTPUT.PUT_LINE('User METRIC_EXPORTER dropped.');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1918 THEN
DBMS_OUTPUT.PUT_LINE('User METRIC_EXPORTER did not exist — skipping.');
ELSE
RAISE;
END IF;
END;
/
-- ----------------------------------------------------------------------------
-- A.2 Create the exporter user
-- Choose your own password; if you front it with a Vault secret, store it
-- there. Nobody needs to log in as this user except once, for PART C.
-- ----------------------------------------------------------------------------
CREATE USER metric_exporter IDENTIFIED BY "ChangeMe#Metric12345!"
DEFAULT TABLESPACE data
QUOTA UNLIMITED ON data;
-- ----------------------------------------------------------------------------
-- A.3 System privileges
-- ----------------------------------------------------------------------------
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
CREATE SEQUENCE, CREATE JOB, CREATE TYPE
TO metric_exporter;
GRANT EXECUTE ON DBMS_CLOUD TO metric_exporter;
GRANT EXECUTE ON DBMS_SCHEDULER TO metric_exporter;
GRANT EXECUTE ON DBMS_LOB TO metric_exporter;
GRANT EXECUTE ON DBMS_SQL TO metric_exporter;
-- ----------------------------------------------------------------------------
-- A.4 The ONE data grant the package needs for itself.
-- V$PDBS.CLOUD_IDENTITY carries region, tenancy, compartment and database
-- OCID as JSON — this is how the exporter discovers its own identity and
-- its ingestion endpoint. Nothing is hardcoded, so the same script
-- installs unchanged in any region or tenancy.
--
-- Fixed views are SYS-owned and must be granted SYS-qualified. V$PDBS is a
-- public synonym for SYS.V_$PDBS; granting on the synonym name does not work.
-- ----------------------------------------------------------------------------
GRANT SELECT ON SYS.V_$PDBS TO metric_exporter;
-- ----------------------------------------------------------------------------
-- A.5 Existence oracle (ADMIN-owned, definer rights)
--
-- WHY THIS EXISTS. Oracle deliberately returns the same ORA-00942 for
-- "the object does not exist" and "the object exists but you may not see
-- it" — no information leak. To turn a failed metric registration into an
-- actionable GRANT statement, the package needs to tell those two apart.
--
-- Granting DBA_OBJECTS to the exporter would do it, but that leaks the
-- whole object namespace to anyone who can execute the package. This
-- function answers exactly one question — "does OWNER.NAME exist, and what
-- kind of thing is it?" — and supports no enumeration.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION admin.metric_object_kind(
p_owner IN VARCHAR2,
p_name IN VARCHAR2
) RETURN VARCHAR2 AUTHID DEFINER IS
l_kind VARCHAR2(30);
BEGIN
SELECT MIN(object_type) INTO l_kind
FROM dba_objects
WHERE owner = p_owner
AND object_name = p_name
AND object_type IN ('TABLE','VIEW','MATERIALIZED VIEW','SYNONYM');
RETURN l_kind; -- NULL = does not exist at all
END;
/
GRANT EXECUTE ON admin.metric_object_kind TO metric_exporter;
-- ----------------------------------------------------------------------------
-- A.6 NO OTHER DATA GRANTS ARE SHIPPED — this is deliberate.
--
-- A registered metric's SQL runs with METRIC_EXPORTER's privileges, and
-- roles are DISABLED inside definer-rights PL/SQL. So every object a
-- customer metric reads needs a DIRECT grant to METRIC_EXPORTER. Granting
-- a set of views up front would only hide that fact until the customer
-- registered their first metric against their own tables.
--
-- Instead, the four shipped gap-filler metrics are disabled and ungranted.
-- Enabling one produces the exact GRANT statement needed:
--
-- EXEC metric_exporter.dbms_metric.enable_metric('invalid_objects');
-- ORA-20142: metric SQL references SYS.DBA_OBJECTS (VIEW), which
-- METRIC_EXPORTER cannot read.
-- Ask the owner or ADMIN to run:
-- GRANT SELECT ON SYS.DBA_OBJECTS TO METRIC_EXPORTER;
-- A grant via a ROLE will NOT work ...
--
-- Run the printed line as ADMIN, enable again, done. The customer learns
-- the grant model on a metric where a mistake costs nothing, before trying
-- it on their own application tables.
-- ----------------------------------------------------------------------------
-- ############################################################################
-- ## ##
-- ## PART B — as ADMIN ##
-- ## (tables, seed registry and package, schema-qualified) ##
-- ## ##
-- ############################################################################
-- ----------------------------------------------------------------------------
-- B.1 Configuration (exactly one row)
--
-- NAMESPACE IS INSTALL-WIDE, NOT PER-METRIC. The IAM policy that lets this
-- database publish is scoped with
-- where target.metrics.namespace = '<your namespace>'
-- so a per-metric namespace would mean editing IAM every time someone adds
-- a metric. One namespace keeps that policy a single static string.
-- For segmentation, use RESOURCE_GROUP — it is per-metric, free, and needs
-- no IAM change at all.
-- ----------------------------------------------------------------------------
CREATE TABLE metric_exporter.metric_config (
config_id NUMBER PRIMARY KEY,
namespace VARCHAR2(64), -- set by configure()
resource_group VARCHAR2(64) DEFAULT 'db' NOT NULL, -- default for new metrics
credential_name VARCHAR2(128) DEFAULT 'OCI_METRICS_CRED' NOT NULL,
compartment_ocid VARCHAR2(255), -- NULL = self-discover
schedule_secs NUMBER DEFAULT 60 NOT NULL,
batch_atomicity VARCHAR2(16) DEFAULT 'NON_ATOMIC' NOT NULL,
max_streams_call NUMBER DEFAULT 50 NOT NULL, -- OCI per-call cap
max_streams_metric NUMBER DEFAULT 200 NOT NULL, -- cardinality guardrail
min_schedule_secs NUMBER DEFAULT 60 NOT NULL, -- push cadence floor
max_schedule_secs NUMBER DEFAULT 3600 NOT NULL, -- push cadence ceiling
canary_ok_at TIMESTAMP, -- gates start_push
enabled CHAR(1) DEFAULT 'Y' NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
CONSTRAINT metric_cfg_atom_chk CHECK (batch_atomicity IN ('ATOMIC','NON_ATOMIC')),
CONSTRAINT metric_cfg_enab_chk CHECK (enabled IN ('Y','N'))
);
INSERT INTO metric_exporter.metric_config (config_id) VALUES (1);
COMMIT;
-- ----------------------------------------------------------------------------
-- B.2 Push log — forensic capture of every POST and every response.
-- This is what turns "it says 200 but there is no data" into a diagnosis.
-- ----------------------------------------------------------------------------
CREATE TABLE metric_exporter.metric_push_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
push_kind VARCHAR2(128), -- 'BATCH:n' | 'METRIC:<name>' | 'CANARY' | 'CONFIG'
http_status NUMBER,
failed_count NUMBER, -- failedMetricsCount from the 200 response
stream_count NUMBER,
err_code VARCHAR2(16),
err_text VARCHAR2(4000),
payload_len NUMBER,
request_body CLOB,
response_body VARCHAR2(4000),
pushed_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- ----------------------------------------------------------------------------
-- B.3 Metric registry
--
-- AUTO-DISCOVERY CONTRACT — the whole customer-facing convention:
-- * source_sql MUST emit a column named VALUE -> the metric value
-- * it MAY emit a column named TS (DATE/TIMESTAMP) -> the datapoint time
-- * EVERY other column becomes an OCI DIMENSION on the datapoint
-- No PL/SQL to write, no label list to declare.
--
-- resourceId (this database's OCID) and dbName are injected automatically
-- on every metric, which is what lets you correlate a custom metric with
-- the platform metrics for the same database.
-- ----------------------------------------------------------------------------
CREATE TABLE metric_exporter.metric_registry (
metric_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
metric_name VARCHAR2(128) NOT NULL UNIQUE,
source_sql CLOB NOT NULL,
unit VARCHAR2(64) DEFAULT 'count' NOT NULL,
resource_group VARCHAR2(64), -- NULL = config default
description VARCHAR2(500),
enabled CHAR(1) DEFAULT 'Y' NOT NULL,
is_builtin NUMBER(1) DEFAULT 0 NOT NULL, -- 1 = shipped; disable, never remove
created_by VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
CONSTRAINT metric_reg_enabled_chk CHECK (enabled IN ('Y','N')),
CONSTRAINT metric_reg_builtin_chk CHECK (is_builtin IN (0,1))
);
-- ----------------------------------------------------------------------------
-- B.4 Seed metrics
--
-- One canary, enabled: proves the whole path end to end and is the thing
-- test_publish() sends. Four gap-fillers, DISABLED: each is a single-table
-- COUNT(*) with one predicate, so its correctness can be verified by
-- running the same SELECT by hand and comparing the number to the chart.
-- None of them duplicates anything in oci_autonomous_database.
-- ----------------------------------------------------------------------------
INSERT INTO metric_exporter.metric_registry
(metric_name, source_sql, unit, description, enabled, is_builtin)
VALUES ('canary',
q'[SELECT 42 AS value FROM dual]',
'count',
'Diagnostic heartbeat — must always read 42. If this stops arriving, the exporter is not publishing.',
'Y', 1);
INSERT INTO metric_exporter.metric_registry
(metric_name, source_sql, unit, description, enabled, is_builtin)
VALUES ('invalid_objects',
q'[SELECT COUNT(*) AS value FROM dba_objects WHERE status = 'INVALID']',
'count',
'Count of INVALID objects database-wide. A step change usually means a deployment broke a dependency. Needs: GRANT SELECT ON SYS.DBA_OBJECTS TO METRIC_EXPORTER;',
'N', 1);
INSERT INTO metric_exporter.metric_registry
(metric_name, source_sql, unit, description, enabled, is_builtin)
VALUES ('invalid_objects_by_owner',
q'[SELECT owner, COUNT(*) AS value FROM dba_objects WHERE status = 'INVALID' GROUP BY owner]',
'count',
'Same as invalid_objects, split by schema — demonstrates auto-derived dimensions. Naturally sparse: only owners WITH invalid objects produce a stream.',
'N', 1);
INSERT INTO metric_exporter.metric_registry
(metric_name, source_sql, unit, description, enabled, is_builtin)
VALUES ('unusable_indexes',
q'[SELECT COUNT(*) AS value FROM dba_indexes WHERE status = 'UNUSABLE']',
'count',
'Count of UNUSABLE indexes. Needs: GRANT SELECT ON SYS.DBA_INDEXES TO METRIC_EXPORTER;',
'N', 1);
INSERT INTO metric_exporter.metric_registry
(metric_name, source_sql, unit, description, enabled, is_builtin)
VALUES ('failed_scheduler_jobs_5m',
q'[SELECT COUNT(*) AS value FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
AND actual_start_date > SYSTIMESTAMP - INTERVAL '5' MINUTE]',
'count',
'Scheduler job failures in the last 5 minutes. Alarm with .max() so a single failure is not averaged away. Needs: GRANT SELECT ON SYS.DBA_SCHEDULER_JOB_RUN_DETAILS TO METRIC_EXPORTER;',
'N', 1);
COMMIT;
-- ----------------------------------------------------------------------------
-- B.5 Package specification
-- All TYPEs are declared here, not in the body: local type declarations in
-- a package body fail when the package is created cross-schema from ADMIN.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE metric_exporter.dbms_metric AUTHID DEFINER AS
-- v0.3: c_version lives in the BODY, not here. A version constant in the spec
-- goes stale the moment anyone patches only the body — which happened to this
-- package between v0.1 and v0.2 and reported the wrong version for hours.
-- Read it through version() instead.
-- === engine ===============================================================
PROCEDURE push_metrics;
-- === bootstrap ============================================================
-- configure(): validates the namespace, stores it, and prints the IAM policy
-- with every OCID already filled in. Run once at install.
PROCEDURE configure(
p_namespace IN VARCHAR2,
p_credential IN VARCHAR2 DEFAULT 'OCI_METRICS_CRED',
p_resource_group IN VARCHAR2 DEFAULT 'db');
PROCEDURE print_policy;
PROCEDURE set_namespace(p_namespace IN VARCHAR2, p_force IN BOOLEAN DEFAULT FALSE);
PROCEDURE set_credential(p_credential IN VARCHAR2);
-- test_publish(): sends the canary and returns a decoded verdict, not a
-- status code. Must succeed once before start_push will arm the scheduler.
-- check_credential(): does OCI accept who we say we are? Answers that on its
-- own, independent of any metrics policy, so a signing problem and an
-- authorisation problem can never be confused again.
FUNCTION check_credential RETURN VARCHAR2;
FUNCTION test_publish RETURN VARCHAR2;
PROCEDURE start_push(p_seconds IN NUMBER DEFAULT NULL);
PROCEDURE stop_push;
-- === registry management ==================================================
TYPE t_check_row IS RECORD (item VARCHAR2(64), result VARCHAR2(4000));
TYPE t_check_tab IS TABLE OF t_check_row;
TYPE t_dryrun_row IS RECORD (stream_num NUMBER, json_text VARCHAR2(32767));
TYPE t_dryrun_tab IS TABLE OF t_dryrun_row;
TYPE t_metric_row IS RECORD (
metric_name VARCHAR2(128), unit VARCHAR2(64), resource_group VARCHAR2(64),
enabled CHAR(1), is_builtin NUMBER, description VARCHAR2(500),
created_by VARCHAR2(128), created_at TIMESTAMP);
TYPE t_metric_tab IS TABLE OF t_metric_row;
-- check_sql(): the "will this work?" front door. Parses in the SAME
-- environment the scheduler uses (definer rights, roles disabled), so a
-- verdict here is authoritative — including missing grants.
FUNCTION check_sql(p_source_sql IN CLOB) RETURN t_check_tab PIPELINED;
FUNCTION metric_dryrun(
p_metric_name IN VARCHAR2,
p_source_sql IN CLOB,
p_unit IN VARCHAR2 DEFAULT 'count',
p_resource_group IN VARCHAR2 DEFAULT NULL) RETURN t_dryrun_tab PIPELINED;
PROCEDURE add_metric(
p_metric_name IN VARCHAR2,
p_source_sql IN CLOB,
p_unit IN VARCHAR2 DEFAULT 'count',
p_description IN VARCHAR2 DEFAULT NULL,
p_resource_group IN VARCHAR2 DEFAULT NULL,
p_enabled IN BOOLEAN DEFAULT TRUE);
PROCEDURE enable_metric (p_metric_name IN VARCHAR2);
PROCEDURE disable_metric(p_metric_name IN VARCHAR2);
PROCEDURE remove_metric (p_metric_name IN VARCHAR2);
FUNCTION list_metrics RETURN t_metric_tab PIPELINED;
-- reset_to_shipped(): back to the as-installed registry state — canary on,
-- every other built-in off. Reports what it could not undo.
PROCEDURE reset_to_shipped;
-- === diagnostics ==========================================================
TYPE t_push_row IS RECORD (
pushed_at TIMESTAMP, push_kind VARCHAR2(128), http_status NUMBER,
failed_count NUMBER, stream_count NUMBER, payload_len NUMBER,
response VARCHAR2(400));
TYPE t_push_tab IS TABLE OF t_push_row;
TYPE t_err_row IS RECORD (
pushed_at TIMESTAMP, metric_name VARCHAR2(128),
err_code VARCHAR2(16), err_text VARCHAR2(1000));
TYPE t_err_tab IS TABLE OF t_err_row;
TYPE t_health_row IS RECORD (item VARCHAR2(64), value VARCHAR2(400));
TYPE t_health_tab IS TABLE OF t_health_row;
TYPE t_job_row IS RECORD (
job_name VARCHAR2(128), enabled VARCHAR2(8), state VARCHAR2(32),
repeat_interval VARCHAR2(128), last_start TIMESTAMP, next_run TIMESTAMP);
TYPE t_job_tab IS TABLE OF t_job_row;
FUNCTION recent_pushes (p_minutes NUMBER DEFAULT 15) RETURN t_push_tab PIPELINED;
FUNCTION metric_errors (p_minutes NUMBER DEFAULT 30) RETURN t_err_tab PIPELINED;
FUNCTION failed_metrics(p_minutes NUMBER DEFAULT 30) RETURN t_push_tab PIPELINED;
FUNCTION health_summary(p_minutes NUMBER DEFAULT 30) RETURN t_health_tab PIPELINED;
FUNCTION job_status RETURN t_job_tab PIPELINED;
FUNCTION version RETURN VARCHAR2;
FUNCTION last_payload RETURN CLOB;
PROCEDURE prune_log(p_keep_days NUMBER DEFAULT 14);
END dbms_metric;
/
-- ----------------------------------------------------------------------------
-- B.6 Package body
-- ----------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY metric_exporter.dbms_metric AS
-- v0.3: the version constant lives here, with the code it describes, so a
-- body-only patch can never report a stale version.
c_version CONSTANT VARCHAR2(16) := 'v0.3';
c_max_dims CONSTANT PLS_INTEGER := 20; -- OCI: dimensions per metric group
c_job CONSTANT VARCHAR2(30) := 'METRIC_PUSH_JOB';
TYPE t_kv IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
-- ==========================================================================
-- Small helpers
-- ==========================================================================
FUNCTION json_esc(p_in IN VARCHAR2) RETURN VARCHAR2 IS
l VARCHAR2(32767) := p_in;
BEGIN
l := REPLACE(l, '\', '\\');
l := REPLACE(l, '"', '\"');
l := REPLACE(l, CHR(13), '\r');
l := REPLACE(l, CHR(10), '\n');
l := REPLACE(l, CHR(9), '\t');
RETURN l;
END json_esc;
-- Oracle's FM-prefixed format model leaves a trailing decimal point on whole
-- numbers: TO_CHAR(42,'FM9999999999999990.9999999999') = '42.' which is not
-- valid JSON. This exact bug caused New Relic to silently drop every metric
-- in a sibling package. RTRIM is the fix; do not remove it.
FUNCTION num_to_json(p_val IN NUMBER) RETURN VARCHAR2 IS
BEGIN
IF p_val IS NULL THEN RETURN 'null'; END IF;
RETURN RTRIM(TO_CHAR(p_val, 'FM9999999999999999990.9999999999'), '.');
END num_to_json;
-- OCI dimension keys: printable ASCII, no spaces, <=256 chars.
FUNCTION dim_key(p_in IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN SUBSTR(REGEXP_REPLACE(LOWER(p_in), '[^a-z0-9_.]', '_'), 1, 256);
END dim_key;
FUNCTION cfg_namespace RETURN VARCHAR2 IS
l VARCHAR2(64);
BEGIN
SELECT namespace INTO l FROM metric_config WHERE config_id = 1;
RETURN l;
END cfg_namespace;
PROCEDURE cloud_identity(
p_region OUT VARCHAR2, p_comp OUT VARCHAR2,
p_dbocid OUT VARCHAR2, p_dbname OUT VARCHAR2, p_tenancy OUT VARCHAR2) IS
l_ci CLOB;
l_ovr VARCHAR2(255);
BEGIN
SELECT cloud_identity INTO l_ci FROM v$pdbs WHERE ROWNUM = 1;
p_region := JSON_VALUE(l_ci, '$.REGION');
p_comp := JSON_VALUE(l_ci, '$.COMPARTMENT_OCID');
p_dbocid := JSON_VALUE(l_ci, '$.DATABASE_OCID');
p_dbname := JSON_VALUE(l_ci, '$.DATABASE_NAME');
p_tenancy := JSON_VALUE(l_ci, '$.TENANT_OCID');
SELECT compartment_ocid INTO l_ovr FROM metric_config WHERE config_id = 1;
IF l_ovr IS NOT NULL THEN p_comp := l_ovr; END IF;
END cloud_identity;
-- ==========================================================================
-- Grant diagnosis
--
-- On 26ai, ORA-00942 names the fully-qualified RESOLVED object, so public
-- synonyms identify themselves (v$session -> SYS.V_$SESSION, acd_v$sysmetric
-- -> C##CLOUD$SERVICE.ACD_V$SYSMETRIC). Unauthorized and nonexistent produce
-- IDENTICAL text, so we ask the existence oracle which one it is.
--
-- Degrades safely: if the message cannot be parsed (non-English NLS, future
-- message change) we return the raw ORA-00942 and say so, rather than
-- guessing.
-- ==========================================================================
FUNCTION grant_hint(p_errmsg IN VARCHAR2) RETURN VARCHAR2 IS
l_owner VARCHAR2(128);
l_name VARCHAR2(128);
l_kind VARCHAR2(30);
l_verb VARCHAR2(16) := 'SELECT';
l_note VARCHAR2(600);
l_me VARCHAR2(128) := SYS_CONTEXT('USERENV','CURRENT_USER');
BEGIN
l_owner := REGEXP_SUBSTR(p_errmsg, '"([^"]+)"\."([^"]+)"', 1, 1, NULL, 1);
l_name := REGEXP_SUBSTR(p_errmsg, '"([^"]+)"\."([^"]+)"', 1, 1, NULL, 2);
IF l_owner IS NULL OR l_name IS NULL THEN
RETURN 'Could not identify the object from the error text. Original: ' || p_errmsg;
END IF;
BEGIN
l_kind := admin.metric_object_kind(l_owner, l_name);
EXCEPTION
WHEN OTHERS THEN l_kind := 'UNKNOWN'; -- PART A.5 not installed
END;
IF l_kind IS NULL THEN
RETURN 'metric SQL references ' || l_owner || '.' || l_name ||
', which does not exist. Check the spelling and the owner.' || CHR(10) ||
' Original error: ' || p_errmsg;
END IF;
IF l_owner LIKE 'C##CLOUD$SERVICE' AND l_name LIKE 'ACD\_V$%' ESCAPE '\' THEN
l_verb := 'ALL';
l_note := ' Note: ACD_ cross-container views require GRANT ALL, not GRANT SELECT.';
ELSIF l_name LIKE 'V\_$%' ESCAPE '\' OR l_name LIKE 'GV\_$%' ESCAPE '\' THEN
l_note := ' Note: you wrote ' || REPLACE(l_name, '_$', '$') ||
' (a public synonym); the grant must name the underlying view ' ||
l_owner || '.' || l_name || '.';
END IF;
RETURN 'metric SQL references ' || l_owner || '.' || l_name ||
' (' || l_kind || '), which ' || l_me || ' cannot read.' || CHR(10) ||
' Ask the owner or ADMIN to run:' || CHR(10) ||
' GRANT ' || l_verb || ' ON ' || l_owner || '.' || l_name ||
' TO ' || l_me || ';' || CHR(10) ||
CASE WHEN l_note IS NOT NULL THEN l_note || CHR(10) ELSE '' END ||
' A grant via a ROLE will NOT work — roles are disabled inside this' || CHR(10) ||
' package. It must be a direct grant. Parsing stops at the first' || CHR(10) ||
' unreadable object, so fix this one and re-run check_sql.' || CHR(10) ||
' Original error: ' || p_errmsg;
END grant_hint;
-- ==========================================================================
-- Cursor preparation, shared by push_metrics / metric_dryrun / check_sql.
-- Locates VALUE (required) and TS (optional); everything else is a dimension.
-- ==========================================================================
PROCEDURE prep_cursor(
p_sql IN CLOB,
p_cursor OUT INTEGER,
p_cols OUT NOCOPY DBMS_SQL.DESC_TAB,
p_col_count OUT INTEGER,
p_value_col OUT INTEGER,
p_ts_col OUT INTEGER,
p_ts_kind OUT PLS_INTEGER,
p_val_num IN OUT NOCOPY NUMBER,
p_dim_buf IN OUT NOCOPY VARCHAR2,
p_ts_date IN OUT NOCOPY DATE,
p_ts_stamp IN OUT NOCOPY TIMESTAMP,
p_ts_tz IN OUT NOCOPY TIMESTAMP WITH TIME ZONE) IS
l_dims PLS_INTEGER := 0;
BEGIN
p_value_col := 0;
p_ts_col := 0;
p_ts_kind := 0;
p_cursor := DBMS_SQL.OPEN_CURSOR;
BEGIN
DBMS_SQL.PARSE(p_cursor, p_sql, DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS THEN
DECLARE
l_msg VARCHAR2(4000) := SQLERRM;
l_cod NUMBER := SQLCODE;
BEGIN
BEGIN DBMS_SQL.CLOSE_CURSOR(p_cursor); EXCEPTION WHEN OTHERS THEN NULL; END;
IF l_cod = -942 THEN
RAISE_APPLICATION_ERROR(-20142,
'' || grant_hint(l_msg));
END IF;
RAISE_APPLICATION_ERROR(-20141,
'metric SQL failed to parse. ' || l_msg);
END;
END;
DBMS_SQL.DESCRIBE_COLUMNS(p_cursor, p_col_count, p_cols);
FOR i IN 1 .. p_col_count LOOP
IF UPPER(p_cols(i).col_name) = 'VALUE' THEN
p_value_col := i;
DBMS_SQL.DEFINE_COLUMN(p_cursor, i, p_val_num);
ELSIF UPPER(p_cols(i).col_name) = 'TS' THEN
p_ts_col := i;
IF p_cols(i).col_type = 12 THEN -- DATE
DBMS_SQL.DEFINE_COLUMN(p_cursor, i, p_ts_date);
ELSIF p_cols(i).col_type = 180 THEN -- TIMESTAMP
p_ts_is_ts := TRUE;
DBMS_SQL.DEFINE_COLUMN(p_cursor, i, p_ts_stamp);
ELSE
DBMS_SQL.CLOSE_CURSOR(p_cursor);
RAISE_APPLICATION_ERROR(-20150,
'column TS must be DATE or TIMESTAMP. Wrap it: '||
'CAST(your_column AS TIMESTAMP) AS ts');
END IF;
ELSE
-- resourceId and dbName are injected on every metric; a customer column
-- of the same name would be silently overwritten, so reject it here.
IF LOWER(p_cols(i).col_name) IN ('resourceid','dbname') THEN
DBMS_SQL.CLOSE_CURSOR(p_cursor);
RAISE_APPLICATION_ERROR(-20143,
'column "' || p_cols(i).col_name || '" collides with a '||
'dimension DBMS_METRIC injects automatically. Alias it in your SQL '||
'(e.g. ' || p_cols(i).col_name || ' AS ' ||
LOWER(p_cols(i).col_name) || '_label).');
END IF;
l_dims := l_dims + 1;
DBMS_SQL.DEFINE_COLUMN(p_cursor, i, p_dim_buf, 4000);
END IF;
END LOOP;
IF p_value_col = 0 THEN
DBMS_SQL.CLOSE_CURSOR(p_cursor);
RAISE_APPLICATION_ERROR(-20140,
'source_sql must emit a column named VALUE — it is the metric '||
'value. Every other column (except an optional TS) becomes a dimension.');
END IF;
-- +2 for the injected resourceId and dbName
IF l_dims + 2 > c_max_dims THEN
DBMS_SQL.CLOSE_CURSOR(p_cursor);
RAISE_APPLICATION_ERROR(-20144,
'' || l_dims || ' dimension columns + 2 injected exceeds the '||
'OCI limit of ' || c_max_dims || ' per metric group. Reduce the columns '||
'your SELECT returns.');
END IF;
END prep_cursor;
-- ==========================================================================
-- Harvest one registry row into stream JSON fragments.
--
-- Rows sharing the same dimension set collapse into ONE stream carrying
-- several datapoints — the correct OCI shape, and it keeps the 50-stream
-- per-call cap counting distinct dimension combinations rather than rows.
-- ==========================================================================
PROCEDURE harvest(
p_metric_name IN VARCHAR2,
p_sql IN CLOB,
p_unit IN VARCHAR2,
p_rgroup IN VARCHAR2,
p_ns IN VARCHAR2,
p_comp IN VARCHAR2,
p_dbocid IN VARCHAR2,
p_dbname IN VARCHAR2,
p_streams OUT NOCOPY t_kv,
p_skipped OUT NUMBER) IS
l_cur INTEGER;
l_cols DBMS_SQL.DESC_TAB;
l_ncols INTEGER;
l_vcol INTEGER;
l_tscol INTEGER;
l_ts_kind PLS_INTEGER;
l_val NUMBER;
l_buf VARCHAR2(4000);
l_tsd DATE;
l_tst TIMESTAMP;
l_dummy INTEGER;
l_dims VARCHAR2(32767);
l_key VARCHAR2(32767);
l_ts_txt VARCHAR2(40);
l_ts_utc TIMESTAMP;
l_now TIMESTAMP := SYS_EXTRACT_UTC(SYSTIMESTAMP);
l_pts t_kv;
l_dimjson t_kv;
BEGIN
p_skipped := 0;
prep_cursor(p_sql, l_cur, l_cols, l_ncols, l_vcol, l_tscol, l_ts_kind,
l_val, l_buf, l_tsd, l_tst, l_tstz);
l_dummy := DBMS_SQL.EXECUTE(l_cur);
WHILE DBMS_SQL.FETCH_ROWS(l_cur) > 0 LOOP
DBMS_SQL.COLUMN_VALUE(l_cur, l_vcol, l_val);
IF l_val IS NULL THEN
p_skipped := p_skipped + 1; -- never emit a null datapoint
CONTINUE;
END IF;
-- ---- timestamp: per-row if TS was supplied, else push time ----------
IF l_tscol > 0 THEN
IF l_ts_kind = 3 THEN
DBMS_SQL.COLUMN_VALUE(l_cur, l_tscol, l_tstz);
l_ts_utc := SYS_EXTRACT_UTC(l_tstz);
ELSIF l_ts_kind = 2 THEN
DBMS_SQL.COLUMN_VALUE(l_cur, l_tscol, l_tst);
l_ts_utc := l_tst;
ELSE
DBMS_SQL.COLUMN_VALUE(l_cur, l_tscol, l_tsd);
l_ts_utc := CAST(l_tsd AS TIMESTAMP);
END IF;
IF l_ts_utc IS NULL
OR l_ts_utc < l_now - INTERVAL '2' HOUR
OR l_ts_utc > l_now + INTERVAL '10' MINUTE THEN
-- OCI rejects the whole batch for one out-of-window datapoint, so
-- drop the row here rather than poisoning everything else.
p_skipped := p_skipped + 1;
CONTINUE;
END IF;
ELSE
l_ts_utc := l_now;
END IF;
l_ts_txt := TO_CHAR(l_ts_utc, 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
-- ---- dimensions ------------------------------------------------------
l_dims := '"resourceId":"' || p_dbocid || '","dbName":"' || p_dbname || '"';
FOR i IN 1 .. l_ncols LOOP
IF i != l_vcol AND i != l_tscol THEN
DBMS_SQL.COLUMN_VALUE(l_cur, i, l_buf);
-- OCI rejects empty dimension values outright: drop the key for this
-- row instead of emitting "".
IF l_buf IS NOT NULL AND LENGTH(TRIM(l_buf)) > 0 THEN
l_dims := l_dims || ',"' || dim_key(l_cols(i).col_name) || '":"' ||
json_esc(SUBSTR(l_buf, 1, 512)) || '"';
END IF;
END IF;
END LOOP;
l_key := l_dims;
IF l_pts.EXISTS(l_key) THEN
l_pts(l_key) := l_pts(l_key) || ',';
ELSE
l_pts(l_key) := NULL;
l_dimjson(l_key) := l_dims;
END IF;
l_pts(l_key) := l_pts(l_key) ||
'{"timestamp":"' || l_ts_txt || '","value":' || num_to_json(l_val) || '}';
END LOOP;
DBMS_SQL.CLOSE_CURSOR(l_cur);
-- ---- assemble one metric object per distinct dimension set -------------
l_key := l_dimjson.FIRST;
WHILE l_key IS NOT NULL LOOP
p_streams(l_key) :=
'{"namespace":"' || p_ns ||
'","compartmentId":"'|| p_comp ||
'","resourceGroup":"'|| p_rgroup ||
'","name":"' || p_metric_name ||
'","dimensions":{' || l_dimjson(l_key) ||
'},"metadata":{"unit":"' || json_esc(p_unit) ||
'"},"datapoints":[' || l_pts(l_key) || ']}';
l_key := l_dimjson.NEXT(l_key);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF l_cur IS NOT NULL THEN
BEGIN DBMS_SQL.CLOSE_CURSOR(l_cur); EXCEPTION WHEN OTHERS THEN NULL; END;
END IF;
RAISE;
END harvest;
-- ==========================================================================
-- POST one batch and log it.
-- ==========================================================================
PROCEDURE send_batch(
p_body IN CLOB,
p_kind IN VARCHAR2,
p_streams IN NUMBER) IS
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_cred VARCHAR2(128);
l_uri VARCHAR2(512);
l_resp DBMS_CLOUD_TYPES.RESP;
l_status NUMBER;
l_text VARCHAR2(4000);
l_failed NUMBER;
l_blob BLOB;
l_doff NUMBER := 1;
l_soff NUMBER := 1;
l_lang NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
l_warn NUMBER := 0;
BEGIN
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
SELECT credential_name INTO l_cred FROM metric_config WHERE config_id = 1;
l_uri := 'https://telemetry-ingestion.' || l_region ||
'.oraclecloud.com/20180401/metrics';
DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
DBMS_LOB.CONVERTTOBLOB(l_blob, p_body, DBMS_LOB.LOBMAXSIZE, l_doff, l_soff,
NLS_CHARSET_ID('AL32UTF8'), l_lang, l_warn);
BEGIN
l_resp := DBMS_CLOUD.SEND_REQUEST(
credential_name => l_cred,
uri => l_uri,
method => DBMS_CLOUD.METHOD_POST,
headers => JSON_OBJECT('Content-Type' VALUE 'application/json'),
body => l_blob);
l_status := DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_resp);
l_text := SUBSTR(DBMS_CLOUD.GET_RESPONSE_TEXT(l_resp), 1, 4000);
-- A 200 does NOT mean the data landed. OCI reports per-stream rejections
-- in the body; without this, a schema mistake looks like success forever.
BEGIN
l_failed := TO_NUMBER(JSON_VALUE(l_text, '$.failedMetricsCount'));
EXCEPTION WHEN OTHERS THEN l_failed := NULL; END;
INSERT INTO metric_push_log
(push_kind, http_status, failed_count, stream_count, payload_len,
request_body, response_body)
VALUES (p_kind, l_status, l_failed, p_streams,
DBMS_LOB.GETLENGTH(l_blob), p_body, l_text);
EXCEPTION
WHEN OTHERS THEN
-- Non-2xx surfaces as ORA-2040x, not as a status code.
DECLARE
v_code VARCHAR2(16) := 'ORA' || TO_CHAR(SQLCODE);
v_msg VARCHAR2(4000) := SUBSTR(SQLERRM, 1, 4000);
BEGIN
INSERT INTO metric_push_log
(push_kind, http_status, stream_count, err_code, err_text,
payload_len, request_body)
VALUES (p_kind, -1, p_streams, v_code, v_msg,
DBMS_LOB.GETLENGTH(l_blob), p_body);
END;
END;
DBMS_LOB.FREETEMPORARY(l_blob);
COMMIT;
END send_batch;
-- ==========================================================================
-- ENGINE
-- ==========================================================================
PROCEDURE push_metrics IS
l_ns VARCHAR2(64);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_rg_dflt VARCHAR2(64);
l_cap NUMBER;
l_guard NUMBER;
l_streams t_kv;
l_key VARCHAR2(32767);
l_batch CLOB;
l_count PLS_INTEGER := 0;
l_batches PLS_INTEGER := 0;
l_total PLS_INTEGER := 0;
l_skipped NUMBER;
BEGIN
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,']';
SELECT namespace, resource_group, max_streams_call, max_streams_metric
INTO l_ns, l_rg_dflt, l_cap, l_guard
FROM metric_config WHERE config_id = 1 AND enabled = 'Y';
IF l_ns IS NULL THEN
RAISE_APPLICATION_ERROR(-20101,
'no namespace configured. Run dbms_metric.configure(''your_namespace'') first.');
END IF;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
DBMS_LOB.CREATETEMPORARY(l_batch, TRUE);
FOR r IN (SELECT metric_name, source_sql, unit,
NVL(resource_group, l_rg_dflt) AS rgroup
FROM metric_registry
WHERE enabled = 'Y'
ORDER BY metric_id) LOOP
BEGIN
l_streams.DELETE;
harvest(r.metric_name, r.source_sql, r.unit, r.rgroup,
l_ns, l_comp, l_dbocid, l_dbname, l_streams, l_skipped);
IF l_streams.COUNT > l_guard THEN
RAISE_APPLICATION_ERROR(-20145,
'metric produced ' || l_streams.COUNT ||
' streams, over the guardrail of ' || l_guard ||
'. Reduce cardinality or raise max_streams_metric.');
END IF;
l_key := l_streams.FIRST;
WHILE l_key IS NOT NULL LOOP
IF l_count = l_cap THEN -- flush at the OCI cap
l_batches := l_batches + 1;
send_batch('{"metricData":[' || l_batch || '],"batchAtomicity":"NON_ATOMIC"}',
'BATCH:' || l_batches, l_count);
DBMS_LOB.TRIM(l_batch, 0);
l_count := 0;
END IF;
IF l_count > 0 THEN DBMS_LOB.APPEND(l_batch, TO_CLOB(',')); END IF;
DBMS_LOB.APPEND(l_batch, TO_CLOB(l_streams(l_key)));
l_count := l_count + 1;
l_total := l_total + 1;
l_key := l_streams.NEXT(l_key);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Per-metric isolation: one bad query never blocks the batch.
DECLARE
v_code VARCHAR2(16) := 'ORA' || TO_CHAR(SQLCODE);
v_msg VARCHAR2(4000) := SUBSTR(SQLERRM, 1, 4000);
BEGIN
INSERT INTO metric_push_log (push_kind, err_code, err_text, payload_len)
VALUES ('METRIC:' || r.metric_name, v_code, v_msg, 0);
COMMIT;
END;
END;
END LOOP;
IF l_count > 0 THEN
l_batches := l_batches + 1;
send_batch('{"metricData":[' || l_batch || '],"batchAtomicity":"NON_ATOMIC"}',
'BATCH:' || l_batches, l_count);
ELSIF l_total = 0 THEN
INSERT INTO metric_push_log (push_kind, err_code, err_text, payload_len)
VALUES ('BATCH', 'NO_METRICS', 'No enabled metric produced data', 0);
COMMIT;
END IF;
DBMS_LOB.FREETEMPORARY(l_batch);
END push_metrics;
-- ==========================================================================
-- BOOTSTRAP
-- ==========================================================================
PROCEDURE validate_ns(p_ns IN VARCHAR2) IS
BEGIN
IF p_ns IS NULL OR NOT REGEXP_LIKE(p_ns, '^[A-Za-z][A-Za-z0-9_]*$') THEN
RAISE_APPLICATION_ERROR(-20102,
'namespace must start with a letter and contain only letters, '||
'digits and underscores (got "' || p_ns || '").');
END IF;
IF LOWER(p_ns) LIKE 'oci\_%' ESCAPE '\' OR LOWER(p_ns) LIKE 'oracle\_%' ESCAPE '\' THEN
RAISE_APPLICATION_ERROR(-20103,
'oci_ and oracle_ are reserved namespace prefixes in OCI Monitoring.');
END IF;
IF LENGTH(p_ns) > 64 THEN
RAISE_APPLICATION_ERROR(-20104,
'namespace is limited to 64 characters.');
END IF;
END validate_ns;
PROCEDURE print_policy IS
l_ns VARCHAR2(64);
l_cred VARCHAR2(128);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
PROCEDURE p(s VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(s); END;
BEGIN
SELECT namespace, credential_name INTO l_ns, l_cred
FROM metric_config WHERE config_id = 1;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
p('============================================================================');
p('DBMS_METRIC ' || c_version || ' — IAM setup for namespace "' || l_ns || '"');
p('============================================================================');
p('Database : ' || l_dbname || ' Region: ' || l_region);
p('DB OCID : ' || l_dbocid);
p('Compartm.: ' || l_comp);
p('Tenancy : ' || l_tenancy);
p('');
p('This database cannot publish until ONE of the following exists in IAM.');
p('Both are written with the compartment OCID rather than its name: a policy');
p('living inside its own compartment resolves a bare name as a non-existent');
p('CHILD compartment, and fails with an opaque 404 NotAuthorizedOrNotFound.');
p('');
p('-- OPTION 1 — resource principal (preferred; no keys to manage) ------------');
p('-- In the database, as ADMIN:');
p('-- EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();');
p('-- EXEC metric_exporter.dbms_metric.set_credential(''OCI$RESOURCE_PRINCIPAL'');');
p('-- Dynamic group matching rule:');
p('ALL { resource.id = ''' || l_dbocid || ''' }');
p('-- Policy:');
p('Allow dynamic-group <your-dynamic-group> to use metrics');
p(' in compartment id ' || l_comp);
p(' where target.metrics.namespace = ''' || l_ns || '''');
p('');
p('-- OPTION 2 — API signing key on a least-privilege user -------------------');
p('Allow group <your-publisher-group> to use metrics');
p(' in compartment id ' || l_comp);
p(' where target.metrics.namespace = ''' || l_ns || '''');
p('-- then, connected as METRIC_EXPORTER, create the credential named ' || l_cred);
p('-- (see PART C of the deployment script).');
p('');
p('-- Your operations team, to read the metric and alarm on it ---------------');
p('Allow group <ops-group> to read metrics in compartment id ' || l_comp);
p('Allow group <ops-group> to manage alarms in compartment id ' || l_comp);
p('Allow group <ops-group> to manage ons-topics in compartment id ' || l_comp);
p('');
p('IAM takes a minute or two to propagate. Then verify with:');
p(' SELECT metric_exporter.dbms_metric.test_publish() FROM dual;');
p('============================================================================');
END print_policy;
PROCEDURE configure(
p_namespace IN VARCHAR2,
p_credential IN VARCHAR2 DEFAULT 'OCI_METRICS_CRED',
p_resource_group IN VARCHAR2 DEFAULT 'db') IS
BEGIN
validate_ns(p_namespace);
UPDATE metric_config
SET namespace = p_namespace,
credential_name = p_credential,
resource_group = NVL(p_resource_group, 'db'),
canary_ok_at = NULL -- re-verify after any change
WHERE config_id = 1;
COMMIT;
print_policy;
END configure;
PROCEDURE set_namespace(p_namespace IN VARCHAR2, p_force IN BOOLEAN DEFAULT FALSE) IS
l_ok TIMESTAMP;
BEGIN
validate_ns(p_namespace);
SELECT canary_ok_at INTO l_ok FROM metric_config WHERE config_id = 1;
IF l_ok IS NOT NULL AND NOT p_force THEN
RAISE_APPLICATION_ERROR(-20105,
'this database has already published to its current namespace. '||
'Changing it orphans the existing series and invalidates the IAM policy. '||
'Re-run with p_force => TRUE if that is what you intend.');
END IF;
UPDATE metric_config SET namespace = p_namespace, canary_ok_at = NULL
WHERE config_id = 1;
COMMIT;
print_policy;
END set_namespace;
PROCEDURE set_credential(p_credential IN VARCHAR2) IS
BEGIN
UPDATE metric_config SET credential_name = p_credential, canary_ok_at = NULL
WHERE config_id = 1;
COMMIT;
END set_credential;
-- Decoded verdict, not a status code: the customer should never have to
-- interpret an OCI error to know whether their policy is live.
-- ==========================================================================
-- check_credential: is the credential accepted by OCI at all?
--
-- Probes a cheap Identity endpoint with the same credential the exporter uses.
-- The response code carries more information than it looks:
-- 200 OCI verified the signature and returned the object.
-- 404 OCI VERIFIED THE SIGNATURE, identified us, and then said
-- NotAuthorizedOrNotFound. Reaching that answer at all proves
-- authentication succeeded - a bad signature never gets this far, it
-- gets a 401. Common for users in a custom identity domain, whose
-- records do not live under the legacy /users/ path.
-- 401 ORA-20401 - the signature or the identity is wrong. The only failure.
-- ==========================================================================
FUNCTION check_credential RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_cred VARCHAR2(128);
l_user VARCHAR2(255);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_resp DBMS_CLOUD_TYPES.RESP;
l_status NUMBER;
l_err VARCHAR2(4000);
l_me VARCHAR2(128) := SYS_CONTEXT('USERENV','CURRENT_USER');
BEGIN
SELECT credential_name INTO l_cred FROM metric_config WHERE config_id = 1;
BEGIN
SELECT username INTO l_user FROM user_credentials
WHERE credential_name = l_cred;
EXCEPTION
WHEN NO_DATA_FOUND THEN
COMMIT;
RETURN 'MISSING - credential "' || l_cred || '" does not exist in the ' ||
l_me || ' schema. DBMS_CLOUD always creates a credential in the ' ||
'schema of the session that runs CREATE_CREDENTIAL, so it must be ' ||
'created while connected as ' || l_me || ', not as ADMIN.';
END;
IF l_cred = 'OCI$RESOURCE_PRINCIPAL' THEN
COMMIT;
RETURN 'RESOURCE PRINCIPAL in use - nothing to check here; the token is ' ||
'managed by the database. Run test_publish() to verify the policy.';
END IF;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
BEGIN
l_resp := DBMS_CLOUD.SEND_REQUEST(
credential_name => l_cred,
uri => 'https://identity.' || l_region ||
'.oci.oraclecloud.com/20160918/users/' || l_user,
method => DBMS_CLOUD.METHOD_GET);
l_status := DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(l_resp);
EXCEPTION
WHEN OTHERS THEN
l_err := SUBSTR(SQLERRM, 1, 4000);
END;
COMMIT;
IF l_status IN (200, 404) OR l_err LIKE '%ORA-20404%' THEN
RETURN 'OK - OCI verified the signature for credential "' || l_cred ||
'" as user ' || l_user || '. This does NOT prove you may publish ' ||
'to namespace "' || cfg_namespace ||
'" - run test_publish() for that.';
ELSIF l_err LIKE '%ORA-20401%' OR l_status IN (401, 403) THEN
RETURN 'FAILED - OCI rejected the signature. Exactly one of these is wrong, '||
'and they must all come from the SAME OCI profile:' || CHR(10) ||
' * user_ocid does not own the key with this fingerprint - very '||
'common when your user lives in a custom identity domain and you '||
'reused an OCID from somewhere else. Stored user: ' || l_user || CHR(10) ||
' * the fingerprint does not match the private key' || CHR(10) ||
' * the private key was never converted to PKCS#8:' || CHR(10) ||
' openssl pkcs8 -topk8 -nocrypt -in key.pem, then strip the' || CHR(10) ||
' BEGIN/END lines and all newlines so it is ONE line' || CHR(10) ||
' * the pasted key line was truncated' || CHR(10) ||
' Compare all three against ONE profile in ~/.oci/config.' || CHR(10) ||
' Raw error: ' || NVL(l_err, 'HTTP ' || l_status);
ELSE
RETURN 'UNCLEAR - status ' || NVL(TO_CHAR(l_status), 'none') ||
'. Raw error: ' || NVL(l_err, '(none)');
END IF;
END check_credential;
-- ==========================================================================
-- reset_to_shipped: put the registry back to its as-installed state.
-- Non-destructive by design - it disables rather than deletes, and reports
-- what it deliberately left alone so nothing vanishes behind your back.
-- ==========================================================================
PROCEDURE reset_to_shipped IS
l_n PLS_INTEGER := 0;
l_me VARCHAR2(128) := SYS_CONTEXT('USERENV','CURRENT_USER');
BEGIN
UPDATE metric_registry SET enabled = 'N'
WHERE metric_name != 'canary' AND is_builtin = 1;
UPDATE metric_registry SET enabled = 'Y' WHERE metric_name = 'canary';
COMMIT;
DBMS_OUTPUT.PUT_LINE('Registry reset: canary enabled, all other built-ins disabled.');
FOR r IN (SELECT metric_name FROM metric_registry WHERE is_builtin = 0) LOOP
IF l_n = 0 THEN
DBMS_OUTPUT.PUT_LINE('Custom metrics left in place (remove_metric to delete):');
END IF;
DBMS_OUTPUT.PUT_LINE(' - ' || r.metric_name);
l_n := l_n + 1;
END LOOP;
l_n := 0;
-- NOTE: ALL_TAB_PRIVS exposes TABLE_SCHEMA, not OWNER (unlike DBA_TAB_PRIVS).
FOR r IN (SELECT table_schema, table_name, privilege FROM all_tab_privs
WHERE grantee = l_me
AND NOT (table_schema = 'SYS' AND table_name IN
('V_$PDBS','DBMS_LOB','DBMS_SQL','DBMS_SCHEDULER'))
AND table_name NOT IN ('DBMS_CLOUD','METRIC_OBJECT_KIND')
ORDER BY table_schema, table_name) LOOP
IF l_n = 0 THEN
DBMS_OUTPUT.PUT_LINE('Grants added since install (revoke as ADMIN if unwanted):');
END IF;
DBMS_OUTPUT.PUT_LINE(' REVOKE ' || r.privilege || ' ON ' || r.table_schema ||
'.' || r.table_name || ' FROM ' || l_me || ';');
l_n := l_n + 1;
END LOOP;
END reset_to_shipped;
FUNCTION test_publish RETURN VARCHAR2 IS
-- Writes to the push log and stamps canary_ok_at, so it must be autonomous:
-- otherwise SELECT dbms_metric.test_publish() FROM dual raises ORA-14551
-- ("cannot perform a DML operation inside a query").
PRAGMA AUTONOMOUS_TRANSACTION;
l_ns VARCHAR2(64);
l_rg VARCHAR2(64);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_streams t_kv;
l_skipped NUMBER;
l_key VARCHAR2(32767);
l_status NUMBER;
l_failed NUMBER;
l_resp VARCHAR2(4000);
BEGIN
SELECT namespace, resource_group INTO l_ns, l_rg
FROM metric_config WHERE config_id = 1;
IF l_ns IS NULL THEN
RETURN 'NOT CONFIGURED — run dbms_metric.configure(''your_namespace'') first.';
END IF;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
harvest('canary', 'SELECT 42 AS value FROM dual', 'count', l_rg,
l_ns, l_comp, l_dbocid, l_dbname, l_streams, l_skipped);
l_key := l_streams.FIRST;
send_batch('{"metricData":[' || l_streams(l_key) || '],"batchAtomicity":"NON_ATOMIC"}',
'CANARY', 1);
SELECT http_status, failed_count, SUBSTR(response_body, 1, 400)
INTO l_status, l_failed, l_resp
FROM (SELECT * FROM metric_push_log
WHERE push_kind = 'CANARY' ORDER BY log_id DESC FETCH FIRST 1 ROWS ONLY);
IF l_status = 200 AND NVL(l_failed, 0) = 0 THEN
UPDATE metric_config SET canary_ok_at = SYSTIMESTAMP WHERE config_id = 1;
COMMIT;
RETURN 'OK — namespace "' || l_ns || '" is live in compartment ' || l_comp ||
'. Metrics Explorer will show it within a minute or two ' ||
'(set Resource group = ' || l_rg || '). You may now start_push.';
ELSIF l_status = 200 AND l_failed > 0 THEN
RETURN 'PARTIAL — OCI accepted the request but rejected ' || l_failed ||
' stream(s). This is a payload problem, not a policy problem. ' ||
'See: SELECT * FROM TABLE(dbms_metric.failed_metrics(10)); Response: ' || l_resp;
ELSIF l_resp LIKE '%NotAuthorizedOrNotFound%' OR l_status IN (401, 404) THEN
RETURN 'NOT AUTHORIZED — the IAM policy is missing, names the wrong ' ||
'compartment, or has not propagated yet. Re-run print_policy, apply it, ' ||
'wait two minutes, try again. Response: ' || l_resp;
ELSIF l_status = 400 THEN
RETURN 'BAD REQUEST — auth succeeded (a 400 means OCI got past signing) but ' ||
'the payload was rejected. This is a bug to report, not a policy issue. ' ||
'Response: ' || l_resp;
ELSE
RETURN 'FAILED — status ' || NVL(TO_CHAR(l_status), 'none') ||
'. Check the credential exists in the METRIC_EXPORTER schema. Response: ' || l_resp;
END IF;
END test_publish;
PROCEDURE start_push(p_seconds IN NUMBER DEFAULT NULL) IS
l_ok TIMESTAMP;
l_secs NUMBER;
l_min NUMBER;
l_max NUMBER;
BEGIN
SELECT canary_ok_at, NVL(p_seconds, schedule_secs),
min_schedule_secs, max_schedule_secs
INTO l_ok, l_secs, l_min, l_max
FROM metric_config WHERE config_id = 1;
-- Push cadence is the customer's choice, but not an unbounded one.
-- BELOW THE FLOOR (default 60s): OCI aggregates custom metrics at a
-- one-minute minimum, so publishing every second buys no extra resolution
-- at all - it just multiplies billable ingestion and burns the per-tenancy
-- PostMetricData transaction budget. Every datapoint inside the same minute
-- collapses to one aggregated value anyway.
-- ABOVE THE CEILING (default 3600s): alarms evaluated over short windows
-- start seeing "no data" between pushes, which reads as a broken exporter.
IF l_secs IS NULL OR l_secs != TRUNC(l_secs) THEN
RAISE_APPLICATION_ERROR(-20107,
'push interval must be a whole number of seconds (got ' ||
NVL(TO_CHAR(l_secs), 'NULL') || ').');
END IF;
IF l_secs < l_min THEN
RAISE_APPLICATION_ERROR(-20107,
'push interval of ' || l_secs || 's is below the ' || l_min ||
's floor. OCI aggregates custom metrics at one-minute minimum, so a '||
'faster cadence adds cost and API traffic without adding resolution. '||
'If you have a genuine reason, raise it deliberately: UPDATE '||
'metric_config SET min_schedule_secs = <n> WHERE config_id = 1;');
END IF;
IF l_secs > l_max THEN
RAISE_APPLICATION_ERROR(-20107,
'push interval of ' || l_secs || 's is above the ' || l_max ||
's ceiling. Alarms over short windows will see gaps between pushes and '||
'report no data. To go slower anyway: UPDATE metric_config SET '||
'max_schedule_secs = <n> WHERE config_id = 1;');
END IF;
-- Refuse to arm a job that will only log failures. Without this the usual
-- outcome is a scheduler writing 404s every 60 seconds for a week while
-- everyone assumes it works.
IF l_ok IS NULL THEN
RAISE_APPLICATION_ERROR(-20106,
'no successful test publish on record. Run '||
'SELECT dbms_metric.test_publish() FROM dual; and fix what it reports first.');
END IF;
BEGIN DBMS_SCHEDULER.DROP_JOB(c_job, force => TRUE);
EXCEPTION WHEN OTHERS THEN NULL; END;
DBMS_SCHEDULER.CREATE_JOB(
job_name => c_job,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN metric_exporter.dbms_metric.push_metrics; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=SECONDLY;INTERVAL=' || l_secs,
enabled => TRUE,
comments => 'DBMS_METRIC — publishes registered metrics to OCI Monitoring');
UPDATE metric_config SET schedule_secs = l_secs WHERE config_id = 1;
COMMIT;
END start_push;
PROCEDURE stop_push IS
BEGIN
BEGIN DBMS_SCHEDULER.DROP_JOB(c_job, force => TRUE);
EXCEPTION WHEN OTHERS THEN NULL; END;
END stop_push;
-- ==========================================================================
-- REGISTRY MANAGEMENT
-- ==========================================================================
FUNCTION check_sql(p_source_sql IN CLOB) RETURN t_check_tab PIPELINED IS
l_cur INTEGER;
l_cols DBMS_SQL.DESC_TAB;
l_ncols INTEGER;
l_vcol INTEGER;
l_tscol INTEGER;
l_ts_kind PLS_INTEGER;
l_val NUMBER;
l_buf VARCHAR2(4000);
l_tsd DATE;
l_tst TIMESTAMP;
l_dims VARCHAR2(4000);
l_streams t_kv;
l_skipped NUMBER;
l_ns VARCHAR2(64);
l_rg VARCHAR2(64);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_guard NUMBER;
l_fail VARCHAR2(4000) := NULL;
v t_check_row;
BEGIN
-- NOTE: PIPE ROW cannot live in a nested subprogram (PLS-00629), so the
-- emit pattern used by the sibling packages is inlined here.
BEGIN
prep_cursor(p_source_sql, l_cur, l_cols, l_ncols, l_vcol, l_tscol,
l_ts_kind, l_val, l_buf, l_tsd, l_tst, l_tstz);
DBMS_SQL.CLOSE_CURSOR(l_cur);
EXCEPTION
WHEN OTHERS THEN
l_fail := SUBSTR(SQLERRM, 1, 4000);
END;
IF l_fail IS NOT NULL THEN
v.item := 'verdict'; v.result := 'FAILED'; PIPE ROW (v);
v.item := 'error'; v.result := l_fail; PIPE ROW (v);
RETURN;
END IF;
v.item := 'verdict';
v.result := 'parses OK in the exporter''s own environment (roles disabled)';
PIPE ROW (v);
v.item := 'value column'; v.result := l_cols(l_vcol).col_name; PIPE ROW (v);
v.item := 'timestamp column';
v.result := CASE WHEN l_tscol > 0 THEN l_cols(l_tscol).col_name || ' (per-row)'
ELSE 'none - datapoints stamped at push time' END;
PIPE ROW (v);
FOR i IN 1 .. l_ncols LOOP
IF i != l_vcol AND i != l_tscol THEN
l_dims := l_dims || ', ' || dim_key(l_cols(i).col_name);
END IF;
END LOOP;
v.item := 'dimensions (auto)'; v.result := 'resourceId, dbName' || l_dims;
PIPE ROW (v);
SELECT namespace, resource_group, max_streams_metric
INTO l_ns, l_rg, l_guard FROM metric_config WHERE config_id = 1;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
harvest('check', p_source_sql, 'count', l_rg, NVL(l_ns, 'unconfigured'),
l_comp, l_dbocid, l_dbname, l_streams, l_skipped);
v.item := 'streams produced';
v.result := l_streams.COUNT ||
CASE WHEN l_streams.COUNT > l_guard
THEN ' - OVER the guardrail of ' || l_guard ELSE '' END;
PIPE ROW (v);
v.item := 'rows skipped';
v.result := l_skipped ||
CASE WHEN l_skipped > 0
THEN ' (null value, or timestamp outside the -2h/+10min window)'
ELSE '' END;
PIPE ROW (v);
RETURN;
END check_sql;
FUNCTION metric_dryrun(
p_metric_name IN VARCHAR2,
p_source_sql IN CLOB,
p_unit IN VARCHAR2 DEFAULT 'count',
p_resource_group IN VARCHAR2 DEFAULT NULL) RETURN t_dryrun_tab PIPELINED IS
l_ns VARCHAR2(64);
l_rg VARCHAR2(64);
l_region VARCHAR2(64);
l_comp VARCHAR2(255);
l_dbocid VARCHAR2(255);
l_dbname VARCHAR2(128);
l_tenancy VARCHAR2(255);
l_streams t_kv;
l_skipped NUMBER;
l_key VARCHAR2(32767);
l_n NUMBER := 0;
v t_dryrun_row;
BEGIN
IF NOT REGEXP_LIKE(p_metric_name, '^[A-Za-z][A-Za-z0-9._$-]*$') THEN
RAISE_APPLICATION_ERROR(-20146,
'metric name must start with a letter and contain only '||
'letters, digits, dots, underscores, hyphens and dollar signs.');
END IF;
SELECT namespace, NVL(p_resource_group, resource_group) INTO l_ns, l_rg
FROM metric_config WHERE config_id = 1;
cloud_identity(l_region, l_comp, l_dbocid, l_dbname, l_tenancy);
harvest(p_metric_name, p_source_sql, p_unit, l_rg,
NVL(l_ns, 'unconfigured'), l_comp, l_dbocid, l_dbname,
l_streams, l_skipped);
l_key := l_streams.FIRST;
WHILE l_key IS NOT NULL LOOP
l_n := l_n + 1;
v.stream_num := l_n;
v.json_text := l_streams(l_key);
PIPE ROW (v);
l_key := l_streams.NEXT(l_key);
END LOOP;
RETURN;
END metric_dryrun;
PROCEDURE add_metric(
p_metric_name IN VARCHAR2,
p_source_sql IN CLOB,
p_unit IN VARCHAR2 DEFAULT 'count',
p_description IN VARCHAR2 DEFAULT NULL,
p_resource_group IN VARCHAR2 DEFAULT NULL,
p_enabled IN BOOLEAN DEFAULT TRUE) IS
l_n NUMBER := 0;
l_guard NUMBER;
BEGIN
-- Validate by building the real payload. Zero streams is acceptable: a
-- metric can legitimately be empty at registration time (e.g. a COUNT of
-- failures on a healthy database).
FOR r IN (SELECT stream_num FROM TABLE(
metric_dryrun(p_metric_name, p_source_sql, p_unit, p_resource_group))) LOOP
l_n := l_n + 1;
END LOOP;
SELECT max_streams_metric INTO l_guard FROM metric_config WHERE config_id = 1;
IF l_n > l_guard THEN
RAISE_APPLICATION_ERROR(-20145,
'this SQL produces ' || l_n || ' streams, over the guardrail of ' ||
l_guard || '. Every stream is billable custom-metric ingestion — reduce the '||
'cardinality of your dimension columns, or raise max_streams_metric knowingly.');
END IF;
INSERT INTO metric_registry
(metric_name, source_sql, unit, resource_group, description, enabled, is_builtin)
VALUES (p_metric_name, p_source_sql, p_unit, p_resource_group, p_description,
CASE WHEN p_enabled THEN 'Y' ELSE 'N' END, 0);
COMMIT;
END add_metric;
-- Re-validates rather than just flipping a flag: a metric registered while a
-- grant existed, and enabled after it was revoked, must fail loudly here.
PROCEDURE enable_metric(p_metric_name IN VARCHAR2) IS
l_sql CLOB;
l_unit VARCHAR2(64);
l_rg VARCHAR2(64);
l_n NUMBER := 0;
BEGIN
BEGIN
SELECT source_sql, unit, resource_group INTO l_sql, l_unit, l_rg
FROM metric_registry WHERE metric_name = p_metric_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20120,
'metric "' || p_metric_name || '" is not in the registry.');
END;
FOR r IN (SELECT stream_num FROM TABLE(
metric_dryrun(p_metric_name, l_sql, l_unit, l_rg))) LOOP
l_n := l_n + 1;
END LOOP;
UPDATE metric_registry SET enabled = 'Y' WHERE metric_name = p_metric_name;
COMMIT;
END enable_metric;
PROCEDURE disable_metric(p_metric_name IN VARCHAR2) IS
BEGIN
UPDATE metric_registry SET enabled = 'N' WHERE metric_name = p_metric_name;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20120,
'metric "' || p_metric_name || '" is not in the registry.');
END IF;
COMMIT;
END disable_metric;
PROCEDURE remove_metric(p_metric_name IN VARCHAR2) IS
l_builtin NUMBER;
BEGIN
BEGIN
SELECT is_builtin INTO l_builtin
FROM metric_registry WHERE metric_name = p_metric_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20120,
'metric "' || p_metric_name || '" is not in the registry.');
END;
IF l_builtin = 1 THEN
RAISE_APPLICATION_ERROR(-20121,
'"' || p_metric_name || '" is a built-in metric. It can be '||
'disabled but not removed.');
END IF;
DELETE FROM metric_registry WHERE metric_name = p_metric_name;
COMMIT;
END remove_metric;
FUNCTION list_metrics RETURN t_metric_tab PIPELINED IS
v t_metric_row;
l_dflt VARCHAR2(64);
BEGIN
-- v0.3: a NULL resource_group in the registry means "inherit the configured
-- default", and that is what gets published — but showing a bare NULL here
-- left you unable to tell which resource group a metric would actually use,
-- which is the single most common reason a metric looks missing in the
-- console. Resolve it and say so.
SELECT resource_group INTO l_dflt FROM metric_config WHERE config_id = 1;
FOR r IN (SELECT metric_name, unit, resource_group, enabled, is_builtin,
description, created_by, created_at
FROM metric_registry ORDER BY is_builtin DESC, metric_name) LOOP
v.metric_name := r.metric_name;
v.unit := r.unit;
v.resource_group := NVL(r.resource_group, l_dflt) ||
CASE WHEN r.resource_group IS NULL
THEN ' (default)' ELSE '' END;
v.enabled := r.enabled;
v.is_builtin := r.is_builtin;
v.description := r.description;
v.created_by := r.created_by;
v.created_at := r.created_at;
PIPE ROW (v);
END LOOP;
RETURN;
END list_metrics;
-- ==========================================================================
-- DIAGNOSTICS
-- ==========================================================================
FUNCTION recent_pushes(p_minutes NUMBER DEFAULT 15) RETURN t_push_tab PIPELINED IS
v t_push_row;
BEGIN
FOR r IN (SELECT pushed_at, push_kind, http_status, failed_count, stream_count,
payload_len, SUBSTR(NVL(response_body, err_text), 1, 400) AS response
FROM metric_push_log
WHERE pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes, 'MINUTE')
AND push_kind NOT LIKE 'METRIC:%'
ORDER BY log_id DESC) LOOP
v.pushed_at := r.pushed_at; v.push_kind := r.push_kind;
v.http_status := r.http_status; v.failed_count := r.failed_count;
v.stream_count := r.stream_count; v.payload_len := r.payload_len;
v.response := r.response;
PIPE ROW (v);
END LOOP;
RETURN;
END recent_pushes;
FUNCTION metric_errors(p_minutes NUMBER DEFAULT 30) RETURN t_err_tab PIPELINED IS
v t_err_row;
BEGIN
FOR r IN (SELECT pushed_at, SUBSTR(push_kind, 8) AS metric_name,
err_code, SUBSTR(err_text, 1, 1000) AS err_text
FROM metric_push_log
WHERE push_kind LIKE 'METRIC:%'
AND pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes, 'MINUTE')
ORDER BY log_id DESC) LOOP
v.pushed_at := r.pushed_at; v.metric_name := r.metric_name;
v.err_code := r.err_code; v.err_text := r.err_text;
PIPE ROW (v);
END LOOP;
RETURN;
END metric_errors;
-- Batches that OCI accepted (200) but partially rejected. This is the silent
-- failure mode: without looking here, a schema mistake looks like success.
FUNCTION failed_metrics(p_minutes NUMBER DEFAULT 30) RETURN t_push_tab PIPELINED IS
v t_push_row;
BEGIN
FOR r IN (SELECT pushed_at, push_kind, http_status, failed_count, stream_count,
payload_len, SUBSTR(response_body, 1, 400) AS response
FROM metric_push_log
WHERE NVL(failed_count, 0) > 0
AND pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes, 'MINUTE')
ORDER BY log_id DESC) LOOP
v.pushed_at := r.pushed_at; v.push_kind := r.push_kind;
v.http_status := r.http_status; v.failed_count := r.failed_count;
v.stream_count := r.stream_count; v.payload_len := r.payload_len;
v.response := r.response;
PIPE ROW (v);
END LOOP;
RETURN;
END failed_metrics;
FUNCTION health_summary(p_minutes NUMBER DEFAULT 30) RETURN t_health_tab PIPELINED IS
v t_health_row;
l_ns VARCHAR2(64); l_rg VARCHAR2(64); l_cred VARCHAR2(128); l_ok TIMESTAMP;
l_n NUMBER; l_min NUMBER; l_max NUMBER;
BEGIN
SELECT namespace, resource_group, credential_name, canary_ok_at
INTO l_ns, l_rg, l_cred, l_ok FROM metric_config WHERE config_id = 1;
v.item := 'version'; v.value := c_version; PIPE ROW (v);
v.item := 'namespace'; v.value := NVL(l_ns,'(not configured)');PIPE ROW (v);
v.item := 'resource group'; v.value := l_rg; PIPE ROW (v);
v.item := 'credential'; v.value := l_cred; PIPE ROW (v);
SELECT schedule_secs, min_schedule_secs, max_schedule_secs
INTO l_n, l_min, l_max FROM metric_config WHERE config_id = 1;
v.item := 'push interval';
v.value := l_n || 's (allowed ' || l_min || '-' || l_max || 's)';
PIPE ROW (v);
v.item := 'last successful test_publish';
v.value := NVL(TO_CHAR(l_ok,'YYYY-MM-DD HH24:MI:SS'),'never'); PIPE ROW (v);
SELECT COUNT(*) INTO l_n FROM metric_registry WHERE enabled = 'Y';
v.item := 'metrics enabled'; v.value := TO_CHAR(l_n); PIPE ROW (v);
SELECT COUNT(*) INTO l_n FROM metric_registry WHERE enabled = 'N';
v.item := 'metrics disabled'; v.value := TO_CHAR(l_n); PIPE ROW (v);
SELECT COUNT(*) INTO l_n FROM metric_push_log
WHERE push_kind LIKE 'BATCH%' AND http_status = 200
AND pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes,'MINUTE');
v.item := 'batches accepted (window)'; v.value := TO_CHAR(l_n); PIPE ROW (v);
SELECT NVL(SUM(failed_count),0) INTO l_n FROM metric_push_log
WHERE pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes,'MINUTE');
v.item := 'streams rejected by OCI (window)'; v.value := TO_CHAR(l_n); PIPE ROW (v);
SELECT COUNT(*) INTO l_n FROM metric_push_log
WHERE push_kind LIKE 'METRIC:%'
AND pushed_at > SYSTIMESTAMP - NUMTODSINTERVAL(p_minutes,'MINUTE');
v.item := 'metric errors (window)'; v.value := TO_CHAR(l_n); PIPE ROW (v);
RETURN;
END health_summary;
FUNCTION job_status RETURN t_job_tab PIPELINED IS
v t_job_row;
BEGIN
FOR r IN (SELECT job_name, enabled, state, repeat_interval,
CAST(last_start_date AS TIMESTAMP) AS last_start,
CAST(next_run_date AS TIMESTAMP) AS next_run
FROM user_scheduler_jobs WHERE job_name = c_job) LOOP
v.job_name := r.job_name; v.enabled := r.enabled; v.state := r.state;
v.repeat_interval := r.repeat_interval;
v.last_start := r.last_start; v.next_run := r.next_run;
PIPE ROW (v);
END LOOP;
RETURN;
END job_status;
FUNCTION version RETURN VARCHAR2 IS
BEGIN RETURN c_version; END version;
FUNCTION last_payload RETURN CLOB IS
l CLOB;
BEGIN
SELECT request_body INTO l FROM (
SELECT request_body FROM metric_push_log
WHERE request_body IS NOT NULL ORDER BY log_id DESC FETCH FIRST 1 ROWS ONLY);
RETURN l;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END last_payload;
PROCEDURE prune_log(p_keep_days NUMBER DEFAULT 14) IS
BEGIN
DELETE FROM metric_push_log
WHERE pushed_at < SYSTIMESTAMP - NUMTODSINTERVAL(p_keep_days, 'DAY');
COMMIT;
END prune_log;
END dbms_metric;
/
-- ############################################################################
-- ## ##
-- ## PART C — as METRIC_EXPORTER ##
-- ## ##
-- ############################################################################
--
-- The signing credential MUST live in the package owner's schema — that is the
-- definer, and it is the identity DBMS_CLOUD resolves the credential against at
-- runtime. A credential sitting in ADMIN is invisible to the package. So this
-- one step requires connecting as METRIC_EXPORTER.
--
-- SKIP THIS ENTIRELY if you are using a resource principal:
-- (as ADMIN) EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
-- EXEC metric_exporter.dbms_metric.set_credential('OCI$RESOURCE_PRINCIPAL');
--
-- Otherwise, flatten your API signing key to one unencrypted PKCS#8 line:
-- openssl pkcs8 -topk8 -nocrypt -in api_key.pem | grep -v -- '-----' | tr -d '\n'
-- A passphrase-protected key will not work.
--
-- BEGIN
-- DBMS_CLOUD.CREATE_CREDENTIAL(
-- credential_name => 'OCI_METRICS_CRED',
-- user_ocid => 'ocid1.user.oc1..<user>',
-- tenancy_ocid => 'ocid1.tenancy.oc1..<tenancy>',
-- private_key => '<single-line PKCS#8 body>',
-- fingerprint => '<aa:bb:cc:...>');
-- END;
-- /
-- SELECT credential_name, enabled FROM user_credentials
-- WHERE credential_name = 'OCI_METRICS_CRED';
--
-- Rotation helper — an expired key is the most common reason an exporter goes
-- quiet, so make rotating it one call rather than a hand-edited block:
--
-- CREATE OR REPLACE PROCEDURE metric_exporter.reset_metrics_cred(
-- p_user_ocid IN VARCHAR2, p_tenancy_ocid IN VARCHAR2,
-- p_fingerprint IN VARCHAR2, p_private_key IN CLOB) AUTHID DEFINER AS
-- BEGIN
-- BEGIN DBMS_CLOUD.DROP_CREDENTIAL('OCI_METRICS_CRED');
-- EXCEPTION WHEN OTHERS THEN NULL; END;
-- DBMS_CLOUD.CREATE_CREDENTIAL(
-- credential_name => 'OCI_METRICS_CRED',
-- user_ocid => p_user_ocid, tenancy_ocid => p_tenancy_ocid,
-- private_key => p_private_key, fingerprint => p_fingerprint);
-- END;
-- /
-- ############################################################################
-- ## ##
-- ## PART D — as ADMIN ##
-- ## configure, verify, schedule ##
-- ## ##
-- ############################################################################
-- ----------------------------------------------------------------------------
-- D.1 Choose the namespace — INTERACTIVE (SQLcl / SQL*Plus only)
--
-- ACCEPT is a client-side command. It does nothing in Database Actions,
-- through an MCP client, or in an automated provisioning pipeline — use
-- D.2 there instead.
-- ----------------------------------------------------------------------------
-- ACCEPT ns CHAR PROMPT 'Metric namespace [letters, digits, underscore; not oci_/oracle_]: '
-- ACCEPT ok CHAR PROMPT 'Use "&ns" for ALL metrics published by this database? (yes/no): '
-- BEGIN
-- IF LOWER('&ok') != 'yes' THEN
-- RAISE_APPLICATION_ERROR(-20001, 'Install aborted — re-run and choose a namespace.');
-- END IF;
-- metric_exporter.dbms_metric.configure(p_namespace => '&ns');
-- END;
-- /
-- ----------------------------------------------------------------------------
-- D.2 Choose the namespace — NON-INTERACTIVE (Database Actions, MCP, pipelines)
-- Prints the IAM policy with every OCID already filled in.
-- ----------------------------------------------------------------------------
SET SERVEROUTPUT ON
BEGIN
metric_exporter.dbms_metric.configure(p_namespace => 'adbd_custom_metric');
END;
/
-- ----------------------------------------------------------------------------
-- D.3 Apply the printed IAM policy in the OCI console, wait ~2 minutes, then
-- ask the database whether it worked. This returns a verdict in English,
-- not a status code.
-- ----------------------------------------------------------------------------
SELECT metric_exporter.dbms_metric.test_publish() AS verdict FROM dual;
-- ----------------------------------------------------------------------------
-- D.4 Arm the scheduler. Refuses until D.3 has succeeded once.
-- ----------------------------------------------------------------------------
BEGIN metric_exporter.dbms_metric.start_push(60); END;
/
-- ----------------------------------------------------------------------------
-- D.5 Verify
-- ----------------------------------------------------------------------------
SELECT * FROM TABLE(metric_exporter.dbms_metric.health_summary(30));
SELECT * FROM TABLE(metric_exporter.dbms_metric.recent_pushes(10));
SELECT * FROM TABLE(metric_exporter.dbms_metric.metric_errors(10));
SELECT * FROM TABLE(metric_exporter.dbms_metric.list_metrics());
SELECT * FROM TABLE(metric_exporter.dbms_metric.job_status());
-- On the OCI side:
-- oci monitoring metric list --compartment-id <comp> --namespace <ns> --query 'data[*].name'
-- oci monitoring metric-data summarize-metrics-data --region <region> \
-- --compartment-id <comp> --namespace <ns> --resource-group db \
-- --query-text "canary[1m].max()" --start-time <iso> --end-time <iso>
-- Remember --resource-group on the read: without it you get zero rows and exit code 0.
-- ----------------------------------------------------------------------------
-- D.6 Registering your own metric — the intended workflow
-- ----------------------------------------------------------------------------
-- 1. Does it work at all, in the exporter's environment?
-- SELECT * FROM TABLE(metric_exporter.dbms_metric.check_sql(
-- q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]'));
--
-- If a grant is missing you get the exact GRANT line to run, and a reminder
-- that a grant through a ROLE will not work here.
--
-- 2. What exactly would be sent?
-- SELECT * FROM TABLE(metric_exporter.dbms_metric.metric_dryrun(
-- 'orders_by_status',
-- q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]'));
--
-- 3. Register it (re-validates internally, then persists):
-- BEGIN metric_exporter.dbms_metric.add_metric(
-- p_metric_name => 'orders_by_status',
-- p_source_sql => q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]',
-- p_unit => 'count',
-- p_description => 'Open orders by status');
-- END;
-- /
--
-- It flows on the next scheduler tick — no recompile, no restart.
-- ----------------------------------------------------------------------------
-- TEARDOWN
-- ----------------------------------------------------------------------------
-- BEGIN metric_exporter.dbms_metric.stop_push; END;
-- /
-- DROP USER metric_exporter CASCADE;
-- DROP FUNCTION admin.metric_object_kind;
-- Published metric data is not removed; it ages out under Monitoring retention.
Raw
DBMS_METRIC_v0.3_user_guide.md

DBMS_METRIC v0.3 — Complete User Guide

Publish any SQL query as a custom OCI Monitoring metric, from inside Oracle Autonomous AI Database.

You register a SELECT. A scheduler job inside the database turns its rows into OCI PostMetricData JSON every 60 seconds and posts it, signed, to the OCI telemetry ingestion endpoint. The result is a first-class OCI metric: it charts in Metrics Explorer, alarms through OCI Monitoring, exports through Service Connector Hub, and is pulled by any third-party observability tool that reads the OCI Monitoring API.

No agent. No compute instance. No ORDS. Nothing to operate outside the database.

This guide assumes nothing. It covers creating the OCI user, group, API key and policy from scratch, installing the package, and every command you will ever run against it. If you already have some of this, skip ahead using the contents.

Validated on: Autonomous AI Database — Dedicated, Oracle AI Database 26ai (23.26.2.0.0), us-ashburn-1. Also expected to work on Serverless; the shipped metrics use only DBA_ views.

Contents

  • 1. What you need before you start
  • 2. Concepts you must understand first
  • 3. Step 1 — Choose your authentication path
  • 4. Step 2 — OCI setup, Option A: resource principal
  • 5. Step 3 — OCI setup, Option B: API signing key
  • 6. Step 4 — Install the package
  • 7. Step 5 — Choose your namespace
  • 8. Step 6 — Create the signing credential
  • 9. Step 7 — Verify with a test publish
  • 10. Step 8 — Start the scheduler
  • 11. Step 9 — Confirm the data in OCI
  • 12. Registering your own metric
  • 13. The built-in metrics
  • 14. Grants: the one thing that will trip you up
  • 15. Building alarms
  • 16. Diagnostics — which function to reach for
  • 17. Day-two operations
  • 18. Troubleshooting
  • 19. Complete API reference
  • 20. Limits, cost and cardinality
  • 21. Uninstalling
  • 22. Appendix — how it works inside
  • 23. What changed in v0.3

1. What you need before you start
A database Autonomous AI Database (Dedicated or Serverless), 19c or later. 26ai recommended — the grant diagnosis in §14 is far more precise there.
ADMIN access to it For the one-time install. You will run SQL as ADMIN, and once as the new METRIC_EXPORTER user.
An OCI account with IAM rights To create a policy. If you are a tenancy administrator you already have everything; §4 and §5 tell you exactly what to create otherwise.
A SQL client Database Actions (SQL worksheet in the OCI console) is enough for everything except one step. SQLcl or SQL Developer with the database wallet is needed for §8, because you must connect as a specific user.
The script dbms_metric_v0.3.sql.
Optional: the OCI CLI Only for the verification commands in §11 and the alarm commands in §15. Everything can be done in the console instead.

Note: the database needs outbound network access to https://telemetry-ingestion.<region>.oraclecloud.com. On a standard Autonomous AI Database this works out of the box; DBMS_CLOUD reaches OCI service endpoints without any ACL or gateway configuration on your part.

2. Concepts you must understand first

Five ideas. Get these and the rest of the guide is mechanical.

2.1 The registry contract

Every metric is a row in a table holding a SELECT statement. That statement must obey one rule:

It must return a column named VALUE. That is the number OCI stores. It may return a column named TS (a date or timestamp). That is the time of the datapoint. Every other column it returns becomes an OCI dimension.

That is the entire contract. There is no label list to declare and no PL/SQL to write.

SELECT COUNT(*) AS value FROM app.orders          -- one number, no dimensions
SELECT status, COUNT(*) AS value FROM app.orders  -- one number per status,
  GROUP BY status                                 --   dimensioned by "status"

2.2 Namespace — one per database, chosen at install

A metric namespace is the top-level bucket OCI files your metrics under, alongside built-in ones like oci_autonomous_database. You choose yours once, at install, and every metric this database publishes goes into it.

It is one-per-database rather than one-per-metric on purpose: the IAM policy that authorises publishing is scoped to a namespace, so a per-metric namespace would mean editing IAM every time somebody adds a metric.

Rules OCI enforces: starts with a letter, contains only letters, digits and underscores, and cannot begin with oci_ or oracle_ (reserved). The package validates this before it stores anything.

You do not create the namespace anywhere. OCI creates it implicitly on the first successful publish.

2.3 Resource group — your free segmentation axis

A resource group is a label attached to each metric, used to filter when reading. DBMS_METRIC defaults it to db and lets you set it per metric.

Use it wherever you might have reached for a second namespace — sales_app, db_health, batch — because changing it needs no IAM change at all.

Remember this or you will lose an hour: when reading a metric back, you must pass the resource group. oci monitoring metric-data summarize-metrics-data without --resource-group returns zero rows and exit code 0 — no error, no warning. Same in the console: set the Resource group field or the metric appears absent.

2.4 Dimensions and streams

A dimension is a key/value label on a datapoint. DBMS_METRIC injects two on every metric automatically:

  • resourceId — this database's OCID. This is what lets you correlate your custom metric with the platform metrics for the same database.
  • dbName — the database name, which is what you will scope alarms by.

Your own columns are added alongside. A stream is one unique combination of metric name plus dimension values. orders_by_status over four statuses is four streams.

Streams matter because OCI caps a single API call at 50 streams, and because each stream is billable ingestion. §20 covers cost.

2.5 A note on EXEC before you copy anything

Every example in this guide uses an explicit block:

BEGIN dbms_metric.some_call('arg'); END;
/

rather than the EXEC shorthand. EXEC is a client-side command that SQLcl and Database Actions expand into BEGIN <rest of line>; END;. If a comment ends up on the same logical line — which happens routinely when you paste a block — the comment swallows the END; and you get a baffling PLS-00103: Encountered the symbol "end-of-file". The block form is immune to it.

2.6 Push, not scrape

The database opens the connection itself, and it opens it inward — to OCI Monitoring, in your own region and compartment, under your own IAM. Nothing polls it, nothing is exposed: no endpoint to secure, no listener, no inbound firewall rule. Your telemetry never leaves your tenancy for a third-party endpoint.

Because the push starts inside the database rather than arriving through the components in front of it, it keeps reporting when the application tier, the REST layer, or the network path a scraper would have used is the thing that has failed. Note the boundary, though: the publisher is the database, so a database that is down publishes nothing. Pair a value alarm on the canary with an active liveness check if you need to detect that case — see §15.

3. Step 1 — Choose your authentication path

OCI will not accept metrics from an anonymous caller. The database must prove who it is. There are two ways, and you pick one now because it changes the next two sections.

Option A — Resource principal Option B — API signing key
How it works The database authenticates as itself, using a token OCI manages A dedicated IAM user's private key, stored in a database credential, signs each request
Keys to manage None One private key, which you must rotate on your own schedule
Setup Enable in the database, create a dynamic group, write a policy Create a user, a group, a key pair, upload the public key, write a policy
Availability Autonomous AI Database Serverless; check whether your Dedicated instance has it yet Everywhere
Recommendation Use this if you can. It removes key handling entirely Use this if resource principal is not available to you

Check quickly whether Option A is available to you — run this as ADMIN:

BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
  DBMS_OUTPUT.PUT_LINE('Resource principal enabled — use Option A.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Not available here — use Option B. ' || SQLERRM);
END;
/

If it succeeds, go to §4. If it errors, go to §5.

You can switch later: it is one call to set_credential, plus the matching policy.

4. Step 2 — OCI setup, Option A: resource principal

Do these in the OCI console. You need the database OCID, which the package prints for you in §7 — or take it from the database's console page (Oracle Database → Autonomous Database → your database → OCID → Copy).

4.1 Enable it in the database

As ADMIN:

BEGIN DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(); END;
/

This creates a credential named OCI$RESOURCE_PRINCIPAL whose token auto-refreshes. It is reversible with DBMS_CLOUD_ADMIN.DISABLE_RESOURCE_PRINCIPAL().

4.2 Create a dynamic group

A dynamic group is how IAM refers to a resource (your database) rather than a person.

Console: Identity & Security → Domains → (your domain) → Dynamic groups → Create dynamic group.

  • Name: dg-adb-metrics (any name; you will use it in the policy)
  • Matching rule:
ALL { resource.id = 'ocid1.autonomousdatabase.oc1.<region>.<your-db-ocid>' }

CLI equivalent:

oci iam dynamic-group create \
  --name dg-adb-metrics \
  --description "ADB instances allowed to publish custom metrics" \
  --matching-rule "ALL {resource.id = 'ocid1.autonomousdatabase.oc1.<region>.<db-ocid>'}"

4.3 Write the publish policy

Console: Identity & Security → Policies → Create Policy → Show manual editor.

Create it in the compartment holding the database (or in the tenancy root).

Allow dynamic-group dg-adb-metrics to use metrics
  in compartment id ocid1.compartment.oc1..<your-compartment-ocid>
  where target.metrics.namespace = '<your-namespace>'

Substitute your compartment OCID and the namespace you intend to choose in §7. use metrics is the aggregate verb that includes publishing; the namespace condition keeps the grant tight.

Write the compartment as in compartment id <ocid>, not by name. A policy that lives inside the compartment it targets resolves a bare name as a non-existent child compartment. The result is a bare 404 NotAuthorizedOrNotFound that looks nothing like a policy problem, and it costs people hours.

4.4 Let your team read and alarm

Publishing and reading are separate permissions. Your operations group needs:

Allow group <ops-group> to read metrics in compartment id ocid1.compartment.oc1..<comp>
Allow group <ops-group> to manage alarms in compartment id ocid1.compartment.oc1..<comp>
Allow group <ops-group> to manage ons-topics in compartment id ocid1.compartment.oc1..<comp>

The last one is for notification topics, which alarms need in order to page anyone.

IAM takes a minute or two to propagate. Now go to §6; you can skip §5 entirely.

5. Step 3 — OCI setup, Option B: API signing key

Five things to create. Take them in order.

5.1 Create a dedicated user

Do not reuse your own account. A service user with exactly one permission is the right posture.

Console: Identity & Security → Domains → (your domain) → Users → Create user.

  • Username: svc-adb-metrics
  • Email: any valid address (required by the form)
  • Untick "create an IAM credential" prompts — this user never logs in.

CLI:

oci iam user create \
  --name svc-adb-metrics \
  --description "Publishes ADB custom metrics to OCI Monitoring"

Note the user OCID it returns.

5.2 Create a group and put the user in it

Policies attach to groups, not users.

Console: Groups → Create group → name it grp-adb-metrics → open it → Add user to group → pick svc-adb-metrics.

CLI:

oci iam group create --name grp-adb-metrics --description "ADB metric publishers"
oci iam group add-user --user-id <user-ocid> --group-id <group-ocid>

5.3 Generate an API signing key pair

On your own machine. The private key never leaves it except to go into the database credential.

mkdir -p ~/.oci && cd ~/.oci
openssl genrsa -out adb_metrics_key.pem 2048
chmod 600 adb_metrics_key.pem
openssl rsa -pubout -in adb_metrics_key.pem -out adb_metrics_key_public.pem

Do not add a passphrase — DBMS_CLOUD cannot use a passphrase-protected key.

5.4 Upload the public key and note the fingerprint

Console: Users → svc-adb-metrics → API keys → Add API key → Paste public key → paste the contents of adb_metrics_key_public.pem → Add.

The console then shows the fingerprint, something like 01:6f:d1:20:fa:90:.... Copy it.

CLI:

oci iam user api-key upload --user-id <user-ocid> --key-file ~/.oci/adb_metrics_key_public.pem

Verify it is active later with:

oci iam user api-key list --user-id <user-ocid> --query 'data[*].fingerprint'

5.5 Write the policy

Console: Identity & Security → Policies → Create Policy → Show manual editor.

Allow group grp-adb-metrics to use metrics
  in compartment id ocid1.compartment.oc1..<your-compartment-ocid>
  where target.metrics.namespace = '<your-namespace>'

Same warning as §4.3: use in compartment id <ocid>, never the bare name.

And the read side for your team:

Allow group <ops-group> to read metrics in compartment id ocid1.compartment.oc1..<comp>
Allow group <ops-group> to manage alarms in compartment id ocid1.compartment.oc1..<comp>
Allow group <ops-group> to manage ons-topics in compartment id ocid1.compartment.oc1..<comp>

You now have four values you will need in §8. Write them down:

Value Where it came from
User OCID §5.1
Tenancy OCID Console → Profile menu → Tenancy, or oci iam compartment list --all
Fingerprint §5.4
Private key file §5.3 (~/.oci/adb_metrics_key.pem)

6. Step 4 — Install the package

Open dbms_metric_v0.3.sql. Connect as ADMIN — Database Actions SQL worksheet is fine.

Run PART A and PART B of the script, in order. Together they create:

Object What it is
User METRIC_EXPORTER An unprivileged schema that owns everything. Change the password in the script before running.
Grants CREATE SESSION/TABLE/PROCEDURE/JOB, execute on DBMS_CLOUD, DBMS_SCHEDULER, DBMS_LOB, DBMS_SQL, and SELECT on SYS.V_$PDBS — that last one is the only data grant shipped, and §14 explains why.
ADMIN.METRIC_OBJECT_KIND A tiny lookup function that makes the grant diagnosis in §14 exact.
METRIC_CONFIG One row of configuration.
METRIC_REGISTRY Your metrics. Seeded with a canary and four disabled built-ins.
METRIC_PUSH_LOG Every request and every response, kept for forensics.
DBMS_METRIC The package: spec and body.

Confirm it landed:

SELECT object_name, object_type, status
FROM   all_objects
WHERE  owner = 'METRIC_EXPORTER'
AND    object_type IN ('PACKAGE','PACKAGE BODY','TABLE')
ORDER  BY object_type, object_name;

You want two VALID rows for DBMS_METRIC and three tables. If the package body is INVALID, see §18.

Note on schema qualification. Everything in this guide calls metric_exporter.dbms_metric.<something>, so you can run it all as ADMIN. If you connect as METRIC_EXPORTER itself, drop the prefix.

7. Step 5 — Choose your namespace

This is the decision you make once. Everything this database publishes lands here.

Pick something identifying and stable: adbd_custom_metric, payments_db, myapp_metrics.

Interactive (SQLcl or SQL*Plus only)

ACCEPT ns CHAR PROMPT 'Metric namespace [letters, digits, underscore; not oci_/oracle_]: '
ACCEPT ok CHAR PROMPT 'Use "&ns" for ALL metrics published by this database? (yes/no): '
BEGIN
  IF LOWER('&ok') != 'yes' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Aborted — re-run and choose a namespace.');
  END IF;
  metric_exporter.dbms_metric.configure(p_namespace => '&ns');
END;
/

ACCEPT is a client-side command. It does nothing in Database Actions or through an MCP client — use the direct call there.

Direct (Database Actions, automation, anywhere)

SET SERVEROUTPUT ON
BEGIN
  metric_exporter.dbms_metric.configure(p_namespace => 'adbd_custom_metric');
END;
/

If you chose Option A in §3, add the credential name:

BEGIN
  metric_exporter.dbms_metric.configure(
    p_namespace  => 'adbd_custom_metric',
    p_credential => 'OCI$RESOURCE_PRINCIPAL');
END;
/

What it prints

configure validates the namespace, stores it, then prints the complete IAM setup with every OCID already filled in — your database OCID, compartment OCID, tenancy OCID, both auth options, and the ops read policies. Real output:

============================================================================
DBMS_METRIC v0.3 - IAM setup for namespace "adbd_custom_metric"
============================================================================
Database : MYDB01   Region: us-ashburn-1
DB OCID  : ocid1.autonomousdatabase.oc1.iad.anuw...
Compartm.: ocid1.compartment.oc1..aaaa...
Tenancy  : ocid1.tenancy.oc1..aaaa...
...
Allow dynamic-group <your-dynamic-group> to use metrics
  in compartment id ocid1.compartment.oc1..aaaa...
  where target.metrics.namespace = 'adbd_custom_metric'
...

Copy the block matching your chosen option into the OCI console policy editor, fill in your group or dynamic-group name, and save it. You can reprint it any time:

SET SERVEROUTPUT ON
BEGIN metric_exporter.dbms_metric.print_policy; END;
/

8. Step 6 — Create the signing credential

Skip this entire section if you chose Option A (resource principal). You already have OCI$RESOURCE_PRINCIPAL; just make sure §7 pointed at it, or run:

BEGIN metric_exporter.dbms_metric.set_credential('OCI$RESOURCE_PRINCIPAL'); END;
/

For Option B, read on.

8.1 Why you must connect as METRIC_EXPORTER

DBMS_CLOUD.CREATE_CREDENTIAL always creates the credential in the schema of the user running it. The package runs with definer's rights as METRIC_EXPORTER, so that is the only schema where it can find the credential. A credential created by ADMIN is invisible to it, and you will get ORA-20004: Credential ... does not exist.

This is the one step you cannot do from an ADMIN session.

How to connect as METRIC_EXPORTER: use SQLcl or SQL Developer with the same database wallet you use for ADMIN, but username METRIC_EXPORTER and the password you set in PART A of the script. In Database Actions, an ADMIN can grant the user access to Database Actions first, but SQLcl is quicker.

8.2 Flatten the private key

DBMS_CLOUD wants the key as a single line of PKCS#8 with the header and footer stripped:

openssl pkcs8 -topk8 -nocrypt -in ~/.oci/adb_metrics_key.pem | grep -v -- '-----' | tr -d '\n'

Copy the entire output. It is one long line with no spaces.

8.3 Create it

Connected as METRIC_EXPORTER:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_METRICS_CRED',
    user_ocid       => 'ocid1.user.oc1..<your-user-ocid>',
    tenancy_ocid    => 'ocid1.tenancy.oc1..<your-tenancy-ocid>',
    private_key     => '<the single line from 8.2>',
    fingerprint     => '01:6f:d1:20:fa:90:...');
END;
/

SELECT credential_name, enabled FROM user_credentials
WHERE  credential_name = 'OCI_METRICS_CRED';

The name must match what is in metric_config.credential_name — OCI_METRICS_CRED by default.

8.4 Install the rotation helper (recommended)

An expired or rotated key is the single most common reason an exporter like this goes quiet. Make rotation one call. Still as METRIC_EXPORTER:

CREATE OR REPLACE PROCEDURE metric_exporter.reset_metrics_cred(
  p_user_ocid    IN VARCHAR2,
  p_tenancy_ocid IN VARCHAR2,
  p_fingerprint  IN VARCHAR2,
  p_private_key  IN CLOB) AUTHID DEFINER AS
BEGIN
  BEGIN DBMS_CLOUD.DROP_CREDENTIAL('OCI_METRICS_CRED');
  EXCEPTION WHEN OTHERS THEN NULL; END;
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_METRICS_CRED',
    user_ocid       => p_user_ocid,
    tenancy_ocid    => p_tenancy_ocid,
    private_key     => p_private_key,
    fingerprint     => p_fingerprint);
END;
/

After any rotation, re-run §9 before walking away.

9. Step 7 — Verify: credential first, then publish

9.1 Is the credential itself accepted?

Before testing the metrics path, check the identity in isolation:

SELECT dbms_metric.check_credential() AS credential FROM dual;

This probes an Identity endpoint with the same credential the exporter uses, and it separates the two issues that are otherwise easy to conflate:

Verdict Meaning
OK OCI verified your signature. It does not prove you may publish — that is the next step.
MISSING No such credential in this schema. You created it as ADMIN; redo §8 connected as METRIC_EXPORTER.
FAILED OCI rejected the signature, and the message lists the four things that can be wrong.
RESOURCE PRINCIPAL in use Nothing to check; go straight to the publish test.

The FAILED case is worth understanding, because it is the most common first-run problem and the most misleading. ORA-20401 means the signature was rejected — almost always because user_ocid does not own the key with that fingerprint. That happens constantly when your user lives in a custom identity domain: the OCID from an older profile, or one copied from a colleague, is paired with your current key. All three values must come from the same profile in ~/.oci/config.

A useful detail encoded in check_credential: a 404 counts as success. OCI returns 401 for a bad signature but 404 NotAuthorizedOrNotFound once it has verified you and then failed to find or authorise the object — so reaching a 404 proves authentication worked. Users in a custom identity domain always get 404 here, because their records do not live under the legacy /users/ path.

9.2 Can this database publish?

This is the moment of truth. Back as ADMIN (or still as METRIC_EXPORTER — either works):

SELECT metric_exporter.dbms_metric.test_publish() AS verdict FROM dual;

It publishes a single canary datapoint (value 42) and returns a verdict in English, not a status code. You will get one of these:

Verdict starts with What it means What to do
OK Published. Namespace is live. Go to §10.
NOT AUTHORIZED The IAM policy is missing, names the wrong compartment, or has not propagated. Re-run print_policy, compare it to what you actually created, wait two minutes, retry. Check you used in compartment id <ocid>.
FAILED - status -1 ... Credential ... does not exist The credential is missing, or it is in the wrong schema. Redo §8 connected as METRIC_EXPORTER.
BAD REQUEST Authentication succeeded — a 400 means OCI got past signing — but the payload was rejected. This is a bug in the package, not your setup. Capture last_payload and report it.
PARTIAL OCI accepted the call but rejected some streams. SELECT * FROM TABLE(metric_exporter.dbms_metric.failed_metrics(10));
NOT CONFIGURED You skipped §7. Run configure.

A successful test publish is recorded. The scheduler will not start without one — see §10.

10. Step 8 — Start the scheduler

BEGIN metric_exporter.dbms_metric.start_push(60); END;
/

The argument is the push interval in seconds and it is entirely yours to choose — 300 for five minutes, 900 for fifteen, whatever suits the signal. A slow-moving business metric does not need a 60-second cadence.

It is validated, though, against min_schedule_secs and max_schedule_secs in metric_config (defaults 60 and 3600):

BEGIN dbms_metric.start_push(300); END;   -- 5 minutes: fine
/
BEGIN dbms_metric.start_push(1); END;     -- ORA-20107
/

Why there is a floor. OCI aggregates custom metrics at a one-minute minimum, so every datapoint inside the same minute collapses into a single aggregated value. Pushing every second therefore buys no extra resolution at all — it just multiplies billable ingestion and eats into the per-tenancy PostMetricData transaction budget. The floor stops you paying for resolution that a one-minute aggregation cannot expose.

Why there is a ceiling. Past an hour, alarms evaluated over short windows start seeing gaps between pushes and report "no data", which reads exactly like an exporter that has stopped.

Both are your defaults to change, not hard limits — the error tells you how:

UPDATE metric_config SET min_schedule_secs = 30 WHERE config_id = 1;
COMMIT;
BEGIN dbms_metric.start_push(30); END;    -- now accepted
/

Fractional intervals are rejected outright (start_push(90.5)), since DBMS_SCHEDULER takes whole seconds.

start_push refuses if §9 has never succeeded, raising ORA-20106. This is deliberate. Without the check, the classic outcome is a job quietly logging authorisation errors every 60 seconds for a week while everybody assumes monitoring is working.

Confirm it is running and pushing:

SELECT * FROM TABLE(metric_exporter.dbms_metric.job_status());
SELECT * FROM TABLE(metric_exporter.dbms_metric.recent_pushes(10));

You want consecutive rows with HTTP_STATUS = 200 and FAILED_COUNT = 0, roughly one per minute.

Why one minute matters: nearly the entire built-in oci_autonomous_database namespace on Dedicated is emitted every five minutes. A metric you publish yourself is bounded only by OCI's one-second minimum publish frequency and one-minute minimum aggregation. For fault detection, that difference is much of the value.

To stop:

BEGIN metric_exporter.dbms_metric.stop_push; END;
/

11. Step 9 — Confirm the data in OCI

In the console

Observability & Management → Monitoring → Metrics Explorer.

  1. Compartment: the one holding your database.
  2. Metric namespace: your namespace. If it is not in the dropdown, no datapoint has landed yet — wait a minute.
  3. Resource group: db (or whatever you configured). Leave this blank and you will see nothing.
  4. Metric name: canary.
  5. Interval: 1m, Statistic: Max.
  6. Click Update Chart — the editor does not refresh on its own.

You should see a flat line at 42.

From the CLI

# what metrics exist in the namespace?
oci monitoring metric list \
  --compartment-id <compartment-ocid> \
  --namespace adbd_custom_metric \
  --query 'data[*].name'

# are datapoints actually arriving?
oci monitoring metric-data summarize-metrics-data \
  --region us-ashburn-1 \
  --compartment-id <compartment-ocid> \
  --namespace adbd_custom_metric \
  --resource-group db \
  --query-text "canary[1m].max()" \
  --start-time 2026-09-05T00:00:00Z \
  --end-time   2026-09-05T01:00:00Z

Reading a metric that has dimensions

canary has no dimensions of its own, so it draws one line. A metric like orders_by_status (dimensioned by status and region) needs one more decision, and the console's controls are easy to misread.

The Dimension name/value pair is a FILTER, not a split. Setting status = PENDING narrows the chart to that one stream; it does not draw a line per status. Three ways to get what you want:

You want Do this
One line per stream Delete the dimension filter (the ✕), leave Aggregate metric streams OFF. You get every combination — 3 statuses × 2 regions = 6 lines.
One line per status Advanced/MQL mode: orders_by_status[1m].max().groupBy(status)
One specific stream Add both dimensions as filters: status = PENDING, region = EMEA
One combined line Turn Aggregate metric streams ON

Set Statistic to Max, not Mean, for counters. The job pushes about once a minute, so two pushes can land in the same one-minute bucket and Mean averages them — a value stepping from 10 to 50 renders as a smeared ~30 instead of a clean jump. Same rule as the alarm table in §15.

Two CLI traps worth knowing now rather than later:

  • --resource-group is required on the read. Omit it and you get zero rows with exit code 0.
  • --query-text carries the MQL, not --query. --query is the CLI's global JMESPath output filter; it will accept your MQL string and silently do nothing useful with it.

12. Registering your own metric

The intended workflow is three commands. Do them in order; each one saves you from a mistake the next would have made expensive.

12.1 Check it

SELECT item, result FROM TABLE(metric_exporter.dbms_metric.check_sql(
  q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]'));

Typical good output:

verdict            parses OK in the exporter's own environment (roles disabled)
value column       VALUE
timestamp column   none - datapoints stamped at push time
dimensions (auto)  resourceId, dbName, status
streams produced   4
rows skipped       0

This verdict is authoritative, and that is the point of the command. check_sql parses your SQL inside the package — with definer's rights and roles disabled — which is exactly the environment the scheduler will run it in. SQL that works in your session can still fail there (see §14). If it works here, it works at push time.

If something is wrong you get verdict = FAILED and a specific error, including the exact GRANT statement when a privilege is missing.

12.2 See exactly what would be sent

SELECT stream_num, json_text FROM TABLE(metric_exporter.dbms_metric.metric_dryrun(
  'orders_by_status',
  q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]'));

One row per stream, each the literal JSON object that would go to OCI. Nothing is sent and nothing is registered.

{"namespace":"adbd_custom_metric","compartmentId":"ocid1.compartment...",
 "resourceGroup":"db","name":"orders_by_status",
 "dimensions":{"resourceId":"ocid1.autonomousdatabase...","dbName":"MYDB01","status":"SHIPPED"},
 "metadata":{"unit":"count"},
 "datapoints":[{"timestamp":"2026-09-05T01:06:19Z","value":42}]}

12.3 Register it

BEGIN
  metric_exporter.dbms_metric.add_metric(
    p_metric_name    => 'orders_by_status',
    p_source_sql     => q'[SELECT status, COUNT(*) AS value FROM app.orders GROUP BY status]',
    p_unit           => 'count',
    p_description    => 'Open orders by status',
    p_resource_group => 'sales_app',   -- optional; defaults to the configured group
    p_enabled        => TRUE);
END;
/

add_metric re-runs the dry run internally before it persists anything, so a metric that would fail cannot get into the registry. It flows on the next scheduler tick — no recompile, no restart.

Zero rows at registration is fine and accepted: a COUNT of error conditions on a healthy database is legitimately empty today.

12.4 Per-row timestamps

If your query knows when each measurement happened, return it as a column named TS:

SELECT region, reading AS value, measured_at AS ts
FROM   sensor_readings
WHERE  measured_at > SYSTIMESTAMP - INTERVAL '5' MINUTE
  • DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are all accepted. SYSTIMESTAMP works.
  • Zone-aware values are converted to UTC. A naive DATE or TIMESTAMP is taken as UTC — convert it yourself if your column stores local time.
  • OCI only accepts datapoints less than 2 hours in the past and less than 10 minutes in the future. Rows outside that window are dropped and counted in rows skipped, rather than being sent — one bad timestamp would otherwise cause OCI to reject the whole batch.
  • Rows sharing the same dimensions collapse into one stream with several datapoints, which is both the correct shape and cheaper.

12.5 Naming rules

Metric names must start with a letter and contain only letters, digits, dots, underscores, hyphens and dollar signs. orders.by_status and myapp_success_rate are both fine.

13. The built-in metrics

Five metrics ship in the registry. Only the canary is enabled.

Metric SQL Enabled Needs grant
canary SELECT 42 AS value FROM dual Yes none
invalid_objects count of DBA_OBJECTS where STATUS='INVALID' No SYS.DBA_OBJECTS
invalid_objects_by_owner same, GROUP BY owner No SYS.DBA_OBJECTS
unusable_indexes count of DBA_INDEXES where STATUS='UNUSABLE' No SYS.DBA_INDEXES
failed_scheduler_jobs_5m failed scheduler runs in the last 5 minutes No SYS.DBA_SCHEDULER_JOB_RUN_DETAILS

Why so few, and why disabled? The oci_autonomous_database namespace already publishes 40+ metrics for this database — CPU, sessions, storage, IOPS, latency, availability — at no cost to you. Re-publishing those here would duplicate data you already have and bill you for custom ingestion. These four sit outside the platform metric set, and each is a single-table COUNT(*) with one predicate, so you can verify any value by running the same SELECT by hand.

Enabling one is the recommended first exercise, because it teaches the grant model on a metric where a mistake costs nothing:

BEGIN metric_exporter.dbms_metric.enable_metric('invalid_objects'); END;
/

That will fail with ORA-20142 and hand you the exact GRANT. Read §14, run the grant as ADMIN, and enable again.

Keep the canary enabled. It is your proof the pipeline is alive — if canary stops arriving in OCI, the exporter is down, and that is something you can alarm on.

14. Grants: the one thing that will trip you up

The rule

Your metric's SQL runs inside the package, with definer's rights as METRIC_EXPORTER. In definer's-rights PL/SQL, roles are disabled. Therefore:

Every object your metric reads needs a direct GRANT to METRIC_EXPORTER. A grant through a role will not work, no matter how many roles you pile on.

This is why the package ships with almost no data grants. Granting a handful of views up front would only hide the rule until you registered your first metric against your own tables.

What it looks like when it goes wrong

SELECT item, result FROM TABLE(metric_exporter.dbms_metric.check_sql(
  q'[SELECT COUNT(*) AS value FROM dba_objects WHERE status='INVALID']'));
verdict  FAILED
error    ORA-20142: metric SQL references SYS.DBA_OBJECTS (VIEW), which
         METRIC_EXPORTER cannot read.
           Ask the owner or ADMIN to run:
               GRANT SELECT ON SYS.DBA_OBJECTS TO METRIC_EXPORTER;
           A grant via a ROLE will NOT work - roles are disabled inside this
           package. It must be a direct grant. Parsing stops at the first
           unreadable object, so fix this one and re-run check_sql.
           Original error: ORA-00942: table or view "SYS"."DBA_OBJECTS" does not exist

Run the printed line as ADMIN, then re-run check_sql or enable_metric. That is the whole loop.

Three details the diagnosis handles for you

  • Public synonyms. Write v$session and the grant you need is on SYS.V_$SESSION — a different name. The message gives you the resolved one.
  • ACD_ cross-container views on Dedicated are owned by C##CLOUD$SERVICE, which nobody would guess, and they need GRANT ALL rather than GRANT SELECT. Both are handled.
  • Typos. If the object does not exist anywhere, you get ORA-20143 telling you to check spelling and owner, instead of a misleading grant suggestion.

Granting your own tables

If the metric reads your application's tables, the table owner runs:

GRANT SELECT ON app.orders TO METRIC_EXPORTER;

Grant only what the metric reads. METRIC_EXPORTER should never accumulate broad privileges.

One object at a time

Parsing stops at the first object it cannot resolve, so a query touching three ungranted tables takes three fix-and-retry cycles. The message says so; it is not giving you an exhaustive list.

15. Building alarms

A metric nobody alarms on is a chart nobody looks at.

15.1 Create a notification topic first

Alarms need somewhere to send. Console: Developer Services → Notifications → Create Topic, then Create Subscription (email, Slack, PagerDuty, or a function).

oci ons topic create --name adb-metric-alarms --compartment-id <comp-ocid>
oci ons subscription create --topic-id <topic-ocid> --compartment-id <comp-ocid> \
  --protocol EMAIL --subscription-endpoint you@example.com

Confirm the email subscription from your inbox or it will never fire.

15.2 Create the alarm

Console: Monitoring → Alarm Definitions → Create Alarm.

Switch to advanced mode and type the MQL query. This matters:

A custom metric is invisible in the guided alarm builder until its first datapoint arrives. In advanced/MQL mode you can type the query, save the alarm in a "no data" state, and it arms itself when data lands. Otherwise you are stuck in a chicken-and-egg loop.

CLI:

oci monitoring alarm create \
  --compartment-id "$COMP" --metric-compartment-id "$COMP" \
  --namespace adbd_custom_metric --resource-group db \
  --display-name "Orders backlog too high" \
  --query-text 'orders_by_status[1m]{dbName="MYDB01",status="PENDING"}.max() > 500' \
  --severity CRITICAL --pending-duration PT5M \
  --destinations "[\"$TOPIC\"]" --is-enabled true

Four things in that command decide whether the alarm ever fires:

  1. --resource-group db. Your metrics are published with a resource group; an alarm that omits it matches no stream and stays silent forever.
  2. --query-text, not --query. As in §11.
  3. {dbName="..."} scopes the alarm to one database. In a compartment holding several, an unscoped alarm pages every team for one database's fault.
  4. Severity and pending duration decide whether people trust it. PT5M means the condition must persist for five minutes.

15.3 When will it actually fire, and where do I look?

The alarm evaluates continuously, but the notification is not instant. With --pending-duration PT1M the condition must hold for a full minute, and OCI's own evaluation adds a little more — expect the state change within about two to three minutes of the data being present.

Check the alarm's state, not your inbox. The authoritative answer is:

oci monitoring alarm-status list-alarms-status \
  --compartment-id <compartment-ocid> \
  --query 'data[].{name:"display-name",status:status,ts:"timestamp-triggered"}'
  • FIRING but no email → the notification subscription is still PENDING. Confirm it from the address you subscribed: oci ons subscription list --compartment-id <comp> --query 'data[].{endpoint:endpoint,state:"lifecycle-state"}'
  • OK when you expect FIRING → the query matches no stream. Check resource group first, then the dbName value, then max() vs mean().

When it does fire, the notification names the exact stream that breached:

"totalMetricsFiring": 1,
"dimensions": [{"region":"EMEA","status":"PENDING","dbName":"...","resourceId":"..."}],
"metricValues": [{"orders_by_status[1m]{...}.max()":"50.00"}]

That is the practical argument for putting dimensions on a metric — an alarm that says "EMEA/PENDING is at 50" is actionable; one that says "orders are high" is not. The dedupeKey in the payload is what stops OCI re-paging you every minute while the condition persists: you get one message per state transition, and a matching FIRING_TO_OK when it clears.

15.4 Choosing the statistic

This is where alarms quietly succeed or fail.

Metric shape Use Why
A counter (errors, retries, backlog) max() or sum() Preserves a single bad sample instead of averaging it away
A 1/0 fault bit min() The worst sample in the window is the one you care about
A ratio from many samples mean() Grades the severity instead of treating one blip as an outage
Latency mean() with a long pending duration A mean is easily dragged by one outlier; require persistence rather than a looser threshold

The behaviour to internalise: the job pushes about once a minute, so two pushes can land in the same one-minute bucket. With mean(), a brief fault that put a 0 next to a healthy 1 reads as 0.5 — which slips under a < 1 threshold and never pages. That is why fault bits use min() and counters use max().

15.5 Alarm on the canary

Your most valuable alarm may be the one that tells you the exporter itself died:

canary[5m]{dbName="MYDB01"}.max() < 42

Design for one thing, though: a gap in a series is ambiguous. "No data" and "no traffic" look identical in any metrics system, so an alarm on the canary's value confirms the payload is well formed, but it cannot by itself tell you the scheduler has stopped. Cover that with job_status in your regular operational review, or by watching for the alarm entering its "no data" state.

16. Diagnostics — which function to reach for

All are pipelined; select from them with TABLE(...).

Question Command
Is the whole thing healthy? Start here. SELECT * FROM TABLE(metric_exporter.dbms_metric.health_summary(30));
Are pushes succeeding? SELECT * FROM TABLE(metric_exporter.dbms_metric.recent_pushes(15));
Did a specific metric's SQL break? SELECT * FROM TABLE(metric_exporter.dbms_metric.metric_errors(30));
Did OCI accept the call but reject some data? SELECT * FROM TABLE(metric_exporter.dbms_metric.failed_metrics(30));
Is the scheduler running? SELECT * FROM TABLE(metric_exporter.dbms_metric.job_status());
What is registered? SELECT * FROM TABLE(metric_exporter.dbms_metric.list_metrics());
What exactly did we send last? SELECT metric_exporter.dbms_metric.last_payload() FROM dual;
Which version is installed? SELECT metric_exporter.dbms_metric.version() FROM dual;

health_summary in one screen: version, namespace, resource group, credential name, the push interval and its allowed range, when the last test publish succeeded, how many metrics are enabled and disabled, batches accepted in the window, streams OCI rejected, and metric errors.

The one that catches a silent issue

failed_metrics is the most important function here. OCI can return HTTP 200 while rejecting individual streams, reporting the count in the response body. Without looking, a schema mistake looks like success indefinitely. The package parses failedMetricsCount on every push and surfaces it — but you have to look, or alarm on it.

17. Day-two operations

Change the push interval — recreates the job at the new cadence:

BEGIN metric_exporter.dbms_metric.start_push(300); END;   -- every 5 minutes
/

Rejected with ORA-20107 outside min_schedule_secs–max_schedule_secs (60–3600 by default). health_summary shows the current interval and the allowed range, so you never have to go looking for it.

Pause and resume

BEGIN metric_exporter.dbms_metric.stop_push; END;
/
BEGIN metric_exporter.dbms_metric.start_push; END;
/

Force a push right now

BEGIN metric_exporter.dbms_metric.push_metrics; END;
/

Rotate the API key — as METRIC_EXPORTER, using the helper from §8.4, then re-run §9.

Switch to resource principal later

BEGIN DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL(); END;
/
BEGIN metric_exporter.dbms_metric.set_credential('OCI$RESOURCE_PRINCIPAL'); END;
/
SELECT metric_exporter.dbms_metric.test_publish() FROM dual;

Add the matching dynamic group and policy from §4 first. set_credential clears the recorded verification, so you must pass §9 again before start_push will work.

Enable, disable, remove metrics

BEGIN metric_exporter.dbms_metric.enable_metric('invalid_objects'); END;
/
BEGIN metric_exporter.dbms_metric.disable_metric('invalid_objects'); END;
/
BEGIN metric_exporter.dbms_metric.remove_metric('orders_by_status'); END;
/

Built-in metrics can be disabled but not removed (ORA-20121). enable_metric re-validates the SQL rather than just flipping a flag, so a metric whose grant was revoked is caught loudly at enable time rather than quietly at push time.

Reset to the as-installed state — useful before a demo or after experimenting:

SET SERVEROUTPUT ON
BEGIN dbms_metric.reset_to_shipped; END;
/

It enables the canary, disables every other built-in, and then reports what it did not touch: your custom metrics (still registered — use remove_metric) and every grant added since install, printed as ready-to-run REVOKE statements. It never deletes anything, so nothing disappears behind your back.

Prune the push log — it keeps full request and response bodies:

-- keep 14 days
BEGIN metric_exporter.dbms_metric.prune_log(14); END;
/

Worth scheduling monthly if you run at 60-second cadence.

Change the namespace — rarely a good idea; it orphans the existing series and invalidates your IAM policy:

BEGIN metric_exporter.dbms_metric.set_namespace('new_ns', p_force => TRUE); END;
/

It refuses without p_force once the database has published, and reprints the new policy.

Adjust the cardinality guardrail

UPDATE metric_exporter.metric_config SET max_streams_metric = 500 WHERE config_id = 1;
COMMIT;

Default is 200 streams per metric. Raise it knowingly — see §20.

18. Troubleshooting

Errors the package raises

Code Meaning Fix
ORA-20101 No namespace configured Run configure (§7)
ORA-20102/3/4 Invalid namespace: bad characters, reserved oci_/oracle_ prefix, or too long Choose another name
ORA-20105 Changing namespace after publishing Re-run with p_force => TRUE if intended
ORA-20106 start_push without a successful test publish Run §9 and fix what it reports
ORA-20107 Push interval outside the allowed range, or not a whole number Pick a value between min_schedule_secs and max_schedule_secs, or move those bounds deliberately — the message shows the exact UPDATE
ORA-20120 Metric not in the registry Check list_metrics for the exact name
ORA-20121 Cannot remove a built-in metric Disable it instead
ORA-20140 SQL has no VALUE column Alias your number: ... AS value
ORA-20141 SQL will not parse Read the original error appended to the message
ORA-20142 Object exists but METRIC_EXPORTER cannot read it Run the GRANT the message prints — §14
ORA-20143 Object does not exist, or a column collides with an injected dimension (resourceid, dbname) Fix the spelling, or alias the column
ORA-20144 More than 20 dimensions OCI's limit; return fewer columns
ORA-20145 Over the cardinality guardrail Reduce dimension cardinality, or raise max_streams_metric (§20)
ORA-20146 Invalid metric name Letters, digits, ., _, -, $; must start with a letter
ORA-20150 TS column is not a date/timestamp type CAST(your_column AS TIMESTAMP) AS ts
ORA-20401 OCI rejected the signature — an identity problem, not a policy one SELECT dbms_metric.check_credential() FROM dual; and follow its checklist
ORA-20404 OCI verified the signature and did not find the object Not an error for check_credential — it counts as success

Symptoms

Symptom Cause and fix
ORA-20004: Credential "METRIC_EXPORTER"."OCI_METRICS_CRED" does not exist The credential is missing or was created in the wrong schema. Redo §8 as METRIC_EXPORTER.
test_publish returns NOT AUTHORIZED Policy missing, wrong compartment, or still propagating. Compare against print_policy. Check you wrote in compartment id <ocid>, not the compartment name.
Push log shows http_status = -1 A non-2xx surfaces as an ORA-2040x exception rather than a status code. Read err_text — it names the real cause.
HTTP 200 but nothing in Metrics Explorer Check failed_metrics. Then check you set Resource group in the console and --resource-group on the CLI. Then click Update Chart.
A metric stopped, others fine metric_errors(60). Per-metric isolation means one problem query never blocks the batch — it is logged and skipped.
Everything stopped at once job_status (is the job there?), then recent_pushes (are we getting 401/404?). An expired API key is the usual answer.
rows skipped is non-zero Either a NULL value, or a TS outside the −2h/+10min window. Both are dropped deliberately.
PLS-00103: Encountered the symbol "end-of-file" on a pasted block A comment merged onto an EXEC line and swallowed the END;. Use BEGIN ... END; / — see §2.5
version() disagrees with the script you ran Somebody patched only the body, or only the spec. health_summary shows both compile times; re-run the whole script section
Package body is INVALID after install SELECT * FROM all_errors WHERE owner='METRIC_EXPORTER' AND name='DBMS_METRIC'; Usually a missing grant from PART A — re-run it.
Alarm saves but never fires Resource-group mismatch, or a dimension filter the metric does not carry. Confirm with summarize-metrics-data ... --query 'data[].dimensions'.

19. Complete API reference

All calls are metric_exporter.dbms_metric.<name>.

Setup

Call Purpose
configure(p_namespace, p_credential, p_resource_group) One-time: validate and store the namespace, then print the IAM policy with real OCIDs
print_policy Reprint that policy at any time
set_namespace(p_namespace, p_force) Change the namespace; refuses after publishing unless forced
set_credential(p_credential) Point at a different DBMS_CLOUD credential
check_credential (function) Does OCI accept this credential at all? Isolates a signing problem from a policy problem
test_publish (function) Publish the canary, return a plain-English verdict
start_push(p_seconds) Create the scheduler job; refuses without a successful test_publish
stop_push Drop the scheduler job

Registry

Call Purpose
check_sql(p_source_sql) (pipelined) Validate SQL in the exporter's own environment: parse, value column, timestamp column, dimensions, streams, skipped rows, grant diagnosis
metric_dryrun(p_metric_name, p_source_sql, p_unit, p_resource_group) (pipelined) Show the exact JSON that would be sent, without sending or registering
add_metric(p_metric_name, p_source_sql, p_unit, p_description, p_resource_group, p_enabled) Validate then register
enable_metric(p_metric_name) Re-validate, then enable
disable_metric(p_metric_name) Stop publishing it
remove_metric(p_metric_name) Delete it; built-ins refuse
list_metrics (pipelined) Everything registered, with state, description and the effective resource group (db (default) when inherited)
reset_to_shipped Back to as-installed: canary on, other built-ins off. Reports the custom metrics and added grants it deliberately left alone

Engine and diagnostics

Call Purpose
push_metrics One full publish cycle; what the scheduler calls
recent_pushes(p_minutes) (pipelined) Batch-level history with status and response
metric_errors(p_minutes) (pipelined) Per-metric SQL errors
failed_metrics(p_minutes) (pipelined) Pushes OCI accepted but partially rejected
health_summary(p_minutes) (pipelined) One-screen overall state
job_status (pipelined) Scheduler job state and next run
last_payload (function) The most recent request body
version (function) Package version
prune_log(p_keep_days) Trim the push log

Configuration table

metric_exporter.metric_config, one row, config_id = 1:

Column Default Meaning
namespace — Set by configure
resource_group db Default for new metrics
credential_name OCI_METRICS_CRED DBMS_CLOUD credential used to sign
compartment_ocid NULL Override; NULL means self-discovered
schedule_secs 60 Current push interval, set by start_push
min_schedule_secs 60 Cadence floor — below this OCI's one-minute aggregation makes the extra pushes pure cost
max_schedule_secs 3600 Cadence ceiling — beyond this, short-window alarms see gaps and report no data
batch_atomicity NON_ATOMIC So one bad stream does not sink the batch
max_streams_call 50 OCI's per-call cap; do not raise
max_streams_metric 200 Your cardinality guardrail
canary_ok_at NULL Set by a successful test_publish; gates start_push
enabled Y Master switch for push_metrics

20. Limits, cost and cardinality

OCI limits the package works within

Limit Value How it is handled
Streams per API call 50 Batched automatically; multiple POSTs per cycle if needed
Dimensions per metric 20 Two are injected, so 18 of your columns; ORA-20144 at registration
Dimension key / value length 256 / 512 chars Keys sanitised, values truncated
Empty dimension values Rejected by OCI The key is dropped for that row
Datapoint age < 2h past, < 10min future Out-of-window rows skipped and counted
Namespace prefix Not oci_ / oracle_ Rejected at configure
Minimum publish / aggregation 1 second / 1 minute 60s default cadence

Cost

Custom metric ingestion is billable, and the unit is the stream. A metric returning one row is one stream. A metric grouped by a column with 400 distinct values is 400 streams, every minute, forever.

Before adding a dimension, ask what its cardinality will be in production, not on your test database. check_sql tells you the stream count today; the guardrail (max_streams_metric, default 200) stops a single runaway metric from surprising you.

Rules of thumb:

  • Dimension by things with bounded cardinality: status, region, node, tier.
  • Never dimension by user ID, session ID, order ID, or anything unbounded.
  • If you need per-entity detail, that is a logging problem, not a metrics problem.

21. Uninstalling

-- as ADMIN
BEGIN metric_exporter.dbms_metric.stop_push; END;
/
DROP USER metric_exporter CASCADE;
DROP FUNCTION admin.metric_object_kind;

-- optional: revoke the grants you added for your metrics
-- REVOKE SELECT ON SYS.DBA_OBJECTS FROM metric_exporter;   (implied by DROP USER)

Also remove, on the OCI side: the policy, the dynamic group or the service user and its API key, and any alarms you built.

Published metric data is not deleted. It ages out under OCI Monitoring's retention.

22. Appendix — how it works inside

DBMS_SCHEDULER (every 60s)
      │
      ▼
for each enabled registry row:
   DBMS_SQL.PARSE  ──►  DESCRIBE_COLUMNS  ──►  find VALUE, find TS,
                                               everything else = dimension
   fetch rows  ──►  group by dimension set  ──►  one stream, N datapoints
      │
      ▼
batch at 50 streams  ──►  {"metricData":[...],"batchAtomicity":"NON_ATOMIC"}
      │
      ▼
DBMS_CLOUD.SEND_REQUEST   (signs the OCI request for you)
      │
      ▼
https://telemetry-ingestion.<region>.oraclecloud.com/20180401/metrics
      │
      ▼
parse failedMetricsCount  ──►  metric_push_log  ──►  diagnostics

Four design decisions worth knowing:

Identity is self-discovered. V$PDBS.CLOUD_IDENTITY gives the region, compartment OCID, database OCID and database name as JSON at runtime. Nothing is hardcoded, so the same script installs unchanged in any region or tenancy.

DBMS_CLOUD.SEND_REQUEST is the supported egress path on Autonomous AI Database, and it performs OCI request signing for you. No Signature v1 implementation, no date header, no body hashing — and no network ACL configuration needed to reach OCI service endpoints.

Issues are isolated per metric. A problem in one metric's SQL is logged and that metric is skipped; the rest of the batch still publishes.

Everything is logged. metric_push_log keeps the full request body and the full response for every push. When something looks wrong, the evidence is already there.

23. What changed in v0.3

Every item here came out of a first-user run by someone who had not written the code.

Change Why
c_version moved from the spec into the body It was in the spec, so a body-only patch left the package reporting a stale version — which is exactly what happened between v0.1 and v0.2
check_credential() added "Signature rejected" and "policy missing" were indistinguishable, so the wrong one got investigated first. 200 and 404 both pass; only 401 is an error, and it now prints a four-point checklist
test_publish() recognises ORA-20401 It used to fall through to "check the credential exists", pointing at the wrong thing entirely
list_metrics() resolves the resource group A bare NULL left you unable to tell which resource group a metric would publish under — the top cause of "my metric is missing"
health_summary() shows spec and body compile times So a partial patch is visible directly instead of being inferred
reset_to_shipped() added Testing leaves metrics enabled and grants behind; there was no way back to a clean state
Every EXEC replaced with BEGIN ... END; / EXEC swallows an adjacent comment line and produces a baffling PLS-00103
§9 split into credential check, then publish check The two issues are different and were being conflated
§11 gained "reading a metric that has dimensions" The console's dimension control filters rather than splits, and nothing says so
§15.3 gained alarm timing and state-checking "It didn't fire" usually means "I looked in my inbox two minutes early"
start_push() validates the interval The cadence was always configurable, but nothing stopped start_push(1) — which costs money and buys nothing, since OCI aggregates custom metrics at one minute. Bounds live in metric_config so you can move them knowingly
Identity-domain guidance throughout §5, §8, §9 A user in a custom domain is the common modern case, and the OCID/key mismatch it invites was the single biggest time sink in testing

DBMS_METRIC v0.3. Validated on Autonomous AI Database — Dedicated, Oracle AI Database 26ai (23.26.2.0.0), us-ashburn-1. This is a self-contained solution you deploy and own in your own tenancy, rather than a built-in capability; it is built to sit cleanly alongside whatever we ship natively in this space later.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment