---
title: "Oracle"
description: "Configure Middleware integration with OracleDB for comprehensive database monitoring, performance analysis, and error detection."
section: "Database"
breadcrumb: "Database > Oracle"
canonical: "https://docs.middleware.io/integrations/oracledb-integration"
---
# Oracle

The Middleware Oracle Database integration collects performance metrics, such as sessions, CPU time, waits, tablespace usage and query execution, through the Middleware Infrastructure Agent.

## 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).

> **Note:** 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:

```bash
    GRANT SELECT ON V_$SESSION        TO <username>;
GRANT SELECT ON V_$SYSSTAT        TO <username>;
GRANT SELECT ON V_$RESOURCE_LIMIT TO <username>;
GRANT SELECT ON DBA_TABLESPACES   TO <username>;
GRANT 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):

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

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](/images/v2/integration/oracle-overview.png)

#### 3 Enable Oracle Integration

Click Add Host and fill out your Oracle configuration details.

![Oracle Host](/images/v2/integration/oracle-host.png)

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

| **Metric** | **Description** |
| --- | --- |
| `oracledb.cpu_time` | Cumulative CPU time (in seconds) |
| `oracledb.enqueue_deadlocks` | Total number of deadlocks between table or row locks in different sessions |
| `oracledb.exchange_deadlocks` | Number of times that a process detected a potential deadlock when exchanging two buffers |
| `oracledb.executions` | Total number of calls (user and recursive) that executed SQL statements |
| `oracledb.logical_reads` | Number of logical reads |
| `oracledb.hard_parses` | Number of hard parses |
| `oracledb.parse_calls` | Total number of parse calls |
| `oracledb.pga_memory` | Session PGA (Program Global Area) memory |
| `oracledb.physical_reads` | Number of physical reads |
| `oracledb.user_commits` | Number of user commits |
| `oracledb.user_rollbacks` | Number of times users manually issue a ROLLBACK statement or an error occurs during a user's transaction |
| `oracledb.sessions.usage` | Count of active sessions |
| `oracledb.processes.usage` | Current count of active processes |
| `oracledb.processes.limit` | Maximum limit of active processes, `-1` if unlimited |
| `oracledb.sessions.limit` | Maximum limit of active sessions, `-1` if unlimited |
| `oracledb.enqueue_locks.usage` | Current count of active enqueue locks |
| `oracledb.enqueue_locks.limit` | Maximum limit of active enqueue locks, `-1` if unlimited |
| `oracledb.dml_locks.usage` | Current count of active DML (Data Manipulation Language) locks |
| `oracledb.dml_locks.limit` | Maximum limit of active DML (Data Manipulation Language) locks, `-1` if unlimited |
| `oracledb.enqueue_resources.usage` | Current count of active enqueue resources |
| `oracledb.enqueue_resources.limit` | Maximum limit of active enqueue resources, `-1` if unlimited |
| `oracledb.transactions.usage` | Current count of active transactions |
| `oracledb.transactions.limit` | Maximum limit of active transactions, `-1` if unlimited |
| `oracledb.tablespace_size.limit` | Maximum size of tablespace in bytes, `-1` if unlimited |
| `oracledb.tablespace_size.usage` | Used tablespace in bytes |
| `oracledb.db_block_gets` | Number of times a current block was requested from the buffer cache (Disabled) |
| `oracledb.consistent_gets` | Number 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:

  ```bash
      sqlplus <user>/<pass>@//<db-host>:1521/<service_name> @- <<'SQL'
  SELECT 1 FROM dual;
  SQL
  ```
- 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:

```bash
    SELECT COUNT(*) FROM v$session;
SELECT 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? Get in touch with us via our Contact Us page or join our Slack channel.