Oracle Database Integration

Prerequisites

  • Infrastructure Agent v1.6.1+ installed on the host that will collect metrics. See the Installation Guide.
  • Oracle Database v10.2+.
  • A database user with read permissions on the views listed in Step 1 below (no superuser required).

Tip (connectivity): Oracle’s listener typically runs on TCP 1521; many connections use EZCONNECT syntax //host[:port]/service_name.

Setup

1 Grant Permissions

Grant the minimum read access needed for this integration:

1GRANT SELECT ON V_$SESSION        TO <username>;
2GRANT SELECT ON V_$SYSSTAT        TO <username>;
3GRANT SELECT ON V_$RESOURCE_LIMIT TO <username>;
4GRANT SELECT ON DBA_TABLESPACES   TO <username>;
5GRANT SELECT ON DBA_DATA_FILES    TO <username>;

Here,

  • V$SESSION shows current sessions; V$RESOURCE_LIMIT exposes resource usage vs limits.

Quick verify (from the Agent host):

1# Using SQL*Plus or SQLcl with EZCONNECT
2sqlplus <username>/<password>@//<db-host>:1521/<service_name> @- <<'SQL'
3SELECT 1 FROM dual;
4SELECT COUNT(*) FROM v$session;
5SELECT resource_name, current_utilization, limit_value FROM v$resource_limit FETCH FIRST 5 ROWS ONLY;
6SQL

Here,

  • Expect 1 from the health check, session count > 0, and a small sample from v$resource_limit.

2 Access Integrations

In Middleware, go to Installations → All Integrations → Oracle and start the connection flow.

Oracle Overview

3 Enable Oracle Integration

Click Add Host and fill out your Oracle configuration details.

Oracle Host

Here,

  • endpoint: host:port used to connect (e.g., db01.acme.local:1521).
  • service: Oracle service name the agent connects to (e.g., orclpdb1).
  • username: Oracle user granted in Step 1.
  • password: Password for that user (special characters supported).

Connection strings typically resolve to //host:port/service_name under the hood (EZCONNECT). Default listener port is often 1521.

Visualize Your Data

Default Oracle Dashboard

Open Dashboard Builder → Oracle (Default) to explore prebuilt charts (sessions, processes, transactions, tablespace usage, CPU time, parses).

Create Oracle Widget

In any dashboard, choose Add New Widget → Oracle and select metrics (e.g., oracledb.sessions.usage, oracledb.tablespace_size.usage, oracledb.hard_parses).

Metrics Collected

MetricDescription
oracledb.cpu_timeCumulative CPU time (in seconds)
oracledb.enqueue_deadlocksTotal number of deadlocks between table or row locks in different sessions
oracledb.exchange_deadlocksNumber of times that a process detected a potential deadlock when exchanging two buffers
oracledb.executionsTotal number of calls (user and recursive) that executed SQL statements
oracledb.logical_readsNumber of logical reads
oracledb.hard_parsesNumber of hard parses
oracledb.parse_callsTotal number of parse calls
oracledb.pga_memorySession PGA (Program Global Area) memory
oracledb.physical_readsNumber of physical reads
oracledb.user_commitsNumber of user commits
oracledb.user_rollbacksNumber of times users manually issue a ROLLBACK statement or an error occurs during a user's transaction
oracledb.sessions.usageCount of active sessions
oracledb.processes.usageCurrent count of active processes
oracledb.processes.limitMaximum limit of active processes, -1 if unlimited
oracledb.sessions.limitMaximum limit of active sessions, -1 if unlimited
oracledb.enqueue_locks.usageCurrent count of active enqueue locks
oracledb.enqueue_locks.limitMaximum limit of active enqueue locks, -1 if unlimited
oracledb.dml_locks.usageCurrent count of active DML (Data Manipulation Language) locks
oracledb.dml_locks.limitMaximum limit of active DML (Data Manipulation Language) locks, -1 if unlimited
oracledb.enqueue_resources.usageCurrent count of active enqueue resources
oracledb.enqueue_resources.limitMaximum limit of active enqueue resources, -1 if unlimited
oracledb.transactions.usageCurrent count of active transactions
oracledb.transactions.limitMaximum limit of active transactions, -1 if unlimited
oracledb.tablespace_size.limitMaximum size of tablespace in bytes, -1 if unlimited
oracledb.tablespace_size.usageUsed tablespace in bytes
oracledb.db_block_getsNumber of times a current block was requested from the buffer cache (Disabled)
oracledb.consistent_getsNumber of times a consistent read was requested for a block from the buffer cache (Disabled)

Troubleshooting

Integration menu not visible:

  • Your account likely lacks Installation permissions. Ask an admin to enable it for your role.

Can’t connect from the Agent host:

  • Test the listener and service with SQL*Plus/SQLcl:
    1sqlplus <user>/<pass>@//<db-host>:1521/<service_name> @- <<'SQL'
    2SELECT 1 FROM dual;
    3SQL
  • If this fails, check DNS/firewall/security groups and confirm the correct port and service name. Listener port is commonly 1521 unless your DBA configured a different port.

ORA-01017 / authentication errors:

  • Re-check the username/password and that the user exists in the target DB/service. Try connecting locally on the DB host to rule out network/NAT issues.

Missing rights when reading views: If queries against v$session, v$sysstat, v$resource_limit, dba_tablespaces, or dba_data_files fail with permission errors, re-apply the GRANT SELECT statements from Step 1 and test again:

1SELECT COUNT(*) FROM v$session;
2SELECT resource_name, current_utilization, limit_value FROM v$resource_limit;

(These views are the data sources for sessions/limits.)

Only some instances show data (RAC / multiple databases):

  • Ensure each target (host:port/service) you want monitored is configured. Metrics are collected per instance/service, not cluster-wide by default.

Port or service changed by DBA:

  • If the listener was moved from 1521, update endpoint and re-test. With EZCONNECT, the format is //host:port/service_name.

Need assistance or want to learn more about Middleware? Contact our support team at [email protected] or join our Slack channel.