> ## Documentation Index
> Fetch the complete documentation index at: https://docs.synq.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Oracle

> Integrating Oracle Database with Coalesce Quality

<Note>
  This guide explains how to connect Coalesce Quality to Oracle Database securely.

  We need this information so we can collect metadata about your tables.

  To be able to finish this guide, you'll need the following:
  <br /> → Access to modify your Oracle configuration (DBA or equivalent)

  ⏱️ Estimated time to finish: 10 minutes.
</Note>

## Data we collect

For the automated data anomaly testing we collect the following:

* Number of rows in every table (from optimizer statistics)
* Timestamp of the last statistics gathering (LAST\_ANALYZED)
* Size of tables in bytes (from data block counts)

**To provide out-of-the-box monitors for data volume and freshness, we don't require access to your actual data.**

We access metadata from the following dictionary views:

| View                     | Description                                  |
| ------------------------ | -------------------------------------------- |
| ALL\_TAB\_COLUMNS        | Column metadata and types                    |
| ALL\_OBJECTS             | Tables, views, and materialized views        |
| ALL\_TAB\_COMMENTS       | Table-level comments                         |
| ALL\_COL\_COMMENTS       | Column-level comments                        |
| ALL\_TABLES              | Table metadata, row counts, and block counts |
| ALL\_VIEWS / ALL\_MVIEWS | View and materialized view definitions       |
| ALL\_CONSTRAINTS         | Primary key and unique constraints           |
| ALL\_CONS\_COLUMNS       | Constraint column details                    |
| ALL\_USERS               | Schema/user listing                          |
| V\$PARAMETER             | Database block size (for size calculation)   |

For Custom SQL monitors, we need `SELECT` access to your actual data tables.

<Note>
  Oracle connections are scoped to a Pluggable Database (PDB) via the service name. A single connection can see all schemas the user has access to, so you only need one integration per PDB.
</Note>

## Authentication

Coalesce Quality supports multiple authentication methods for Oracle:

### Option 1: Username/Password

Standard Oracle database authentication. Works with all Oracle deployments.

### Option 2: Oracle Wallet (mTLS)

For Oracle Cloud Infrastructure (OCI) Autonomous Database, which requires mTLS by default. Download the wallet from the OCI console and provide the wallet path.

<Note>
  Oracle Wallet authentication is the recommended method for OCI Autonomous Database. The wallet contains the TLS certificates and connection details needed for secure mTLS connections.
</Note>

## Create a dedicated monitoring user

Connect to your Oracle PDB as a DBA user (e.g., `ADMIN` on OCI or `SYSTEM` on-premises) and execute the following:

### Step 1: Create the user and grant metadata access (required)

```sql theme={null}
-- Create the monitoring user
CREATE USER synq IDENTIFIED BY '<password>'
DEFAULT TABLESPACE USERS;

-- Allow the user to connect
GRANT CREATE SESSION TO synq;

-- Read-only access to all dictionary views (ALL_*, DBA_*, V$*)
GRANT SELECT ANY DICTIONARY TO synq;
```

With just these grants, Coalesce Quality can:

* Discover all tables, views, materialized views, and their columns
* Collect row counts, table sizes (via block counts), and last-analyzed timestamps
* Read view and materialized view definitions
* Collect query logs from `V$SQL` (in-memory query cache)

<Note>
  `SELECT ANY DICTIONARY` is the recommended grant for monitoring tools. It provides read-only access to all data dictionary views without granting access to actual table data.

  If your security policy prohibits `SELECT ANY DICTIONARY`, you can alternatively grant `SELECT_CATALOG_ROLE` which provides similar access to catalog views.
</Note>

### Step 2: Enable Query Logs (optional)

Coalesce Quality can collect query logs from Oracle to provide SQL lineage analysis. No additional grants are needed — `SELECT ANY DICTIONARY` (already granted above) provides access to the query log views.

There are two modes depending on your Oracle licensing:

**V\$SQL (default, no additional license)**

By default, query logs are collected from the `V$SQL` dynamic performance view, which contains SQL statements currently cached in the shared pool (SGA). This provides recent query history without any additional setup.

<Note>
  `V$SQL` is an in-memory cache — entries age out under memory pressure. The amount of history available depends on your SGA size and workload. For persistent query history, use the Diagnostics Pack option below.
</Note>

**AWR / Diagnostics Pack (persistent history)**

For persistent query history, Coalesce Quality can use AWR (Automatic Workload Repository) views (`DBA_HIST_SQLSTAT` and `DBA_HIST_SQLTEXT`). AWR captures hourly snapshots and retains them for a configurable period (default: 8 days).

<Warning>
  AWR is part of the **Oracle Diagnostics Pack**, which requires a separate license for Oracle Database Enterprise Edition. Ensure your organization has the appropriate license before enabling this option. Oracle Cloud (OCI) Autonomous Database includes the Diagnostics Pack at no extra cost.
</Warning>

Enable **Use Diagnostics Pack (AWR)** in the Coalesce Quality integration settings to use this mode.

### Step 3: Grant access for Custom SQL monitors (optional)

If you want Coalesce Quality to run Custom SQL monitors that query your actual data, grant `SELECT` on the target schemas:

```sql theme={null}
-- Option A: Grant access to all tables (simplest)
GRANT SELECT ANY TABLE TO synq;

-- Option B: Grant access to specific schemas only
GRANT SELECT ON <schema_name>.<table_name> TO synq;
```

<Warning>
  `SELECT ANY TABLE` grants read access to all tables in all schemas. If your security policy requires more granular control, use per-schema or per-table grants instead.
</Warning>

## Network Configuration

<Note>
  If your Oracle instance is behind a firewall or has network access controls, you may need to whitelist Coalesce Quality's IP addresses. See [IP Whitelist](/security/ip) for the complete list of IP addresses by region.

  For OCI Autonomous Database, configure the Access Control List (ACL) to allow connections from our IP addresses.
</Note>

## Input data in the UI

### Integration name

E.g., `oracle-production`

### Service Name

The Oracle service name (PDB name), e.g., `mydb_high` for OCI Autonomous Database or `ORCL` for standard installations

### Host

The hostname of your Oracle instance, e.g., `adb.eu-frankfurt-1.oraclecloud.com` for OCI

### Port

The default is `1521`. For OCI Autonomous Database with mTLS, use `1522`

### Authentication method

Choose one of:

* **Username/Password**: provide the `synq` user credentials
* **Oracle Wallet (mTLS)**: provide the wallet directory path (download from OCI console → Database connection → Download wallet)

### SSL

Enable for encrypted connections (TCPS protocol). Required for OCI Autonomous Database.

### Verify server certificate

Enable to verify the server's TLS certificate. Disable for self-signed certificates or development environments.

### Use Diagnostics Pack (AWR)

Enable to use AWR-based persistent query history instead of V\$SQL. Requires the Oracle Diagnostics Pack license (included with OCI Autonomous Database at no extra cost).