Skip to main content
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:
→ Access to modify your Oracle configuration (DBA or equivalent)⏱️ Estimated time to finish: 10 minutes.

​
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:
ViewDescription
ALL_TAB_COLUMNSColumn metadata and types
ALL_OBJECTSTables, views, and materialized views
ALL_TAB_COMMENTSTable-level comments
ALL_COL_COMMENTSColumn-level comments
ALL_TABLESTable metadata, row counts, and block counts
ALL_VIEWS / ALL_MVIEWSView and materialized view definitions
ALL_CONSTRAINTSPrimary key and unique constraints
ALL_CONS_COLUMNSConstraint column details
ALL_USERSSchema/user listing
V$PARAMETERDatabase block size (for size calculation)
For Custom SQL monitors, we need SELECT access to your actual data tables.
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.

​
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.
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.

​
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)

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

​
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.
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.
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).
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.
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: 
-- 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;
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.

​
Network Configuration

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

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