Oracle - Coalesce Quality Documentation

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:

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
DBMS_METADATA, SESSION_ROLES	Table DDL (`DBMS_METADATA.GET_DDL`), and whether `SELECT_CATALOG_ROLE` is enabled for it
ALL_CONSTRAINTS	Primary, unique and foreign keys, and check constraints
ALL_CONS_COLUMNS	Constraint column details
ALL_INDEXES / ALL_IND_COLUMNS	Indexes that back no key, with their columns
DBA_OBJECTS / DBA_SOURCE	Procedures and functions with their source (`ALL_OBJECTS` / `ALL_SOURCE` when refused)
DBA_USERS / DBA_ROLE_PRIVS	Users and their roles (opt-in) (`ALL_USERS` when refused)
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.

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

An integration created in the Coalesce Quality UI signs in with a username and password. For OCI Autonomous Database, turn on SSL (TCPS) and allow our IP addresses in the database’s access control list. See Network Configuration. The DWH Agent can also connect with an Oracle Wallet (mTLS). Download the wallet from the OCI console and set wallet_path to its directory on the agent connection. SSL is turned on automatically when wallet_path is set, and the username and password can come from the wallet.

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;

-- Table DDL of other schemas through DBMS_METADATA.GET_DDL
GRANT SELECT_CATALOG_ROLE 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, and the DDL of every table
Read procedures and functions with their source
Collect query logs from V$SQL (in-memory query cache)

Both grants are needed, and neither gives access to table data. SELECT ANY DICTIONARY reads the dictionary views. DBMS_METADATA.GET_DDL shows the DDL of another schema’s tables only with SELECT_CATALOG_ROLE enabled in the session, and SELECT ANY DICTIONARY does not count for it: without the role, Coalesce Quality reads the DDL of the user’s own tables only and leaves the other schemas’ table DDL out.The role is enabled in the session only as a default role of the user. A role you grant is a default role unless the user’s default roles were changed; if they were, add it with ALTER USER synq DEFAULT ROLE ALL;.

Step 2: Enable Query Logs (optional)

Coalesce Quality can collect query logs from Oracle for SQL lineage analysis. SELECT ANY DICTIONARY from Step 1 gives access to the query log views. Turn on Fetch query logs in the integration settings. 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, and 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.

Step 4: Grant `DBMS_CRYPTO` for reconciliation of long rows (optional)

A reconciliation in row_checksum mode hashes each row on Oracle. To compare rows whose joined column text is longer than 4000 bytes, give the user execute rights on DBMS_CRYPTO:

GRANT EXECUTE ON SYS.DBMS_CRYPTO TO synq;

Oracle gives this right to no user by default. A database administrator must run the grant. Without it, rows of up to 4000 bytes compare as usual. A longer row makes the reconciliation fail with ORA-01489 and a message that names this grant. With or without the grant, a row gets the same hash as on every other supported database.

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

Username and password

The synq user you created above.

Enable SSL/TLS (TCPS)

Encrypts the connection with the TCPS protocol. Required for OCI Autonomous Database.

Verify server certificate

Verifies the server’s TLS certificate. Turn it off for self-signed certificates.

Fetch query logs

Collects query history from V$SQL, or from AWR with Use Diagnostics Pack (AWR). See Step 2 above.

Fetch users and service accounts (optional)

Shows once query logs are on. Lists the database users and their roles once a day, from DBA_USERS and DBA_ROLE_PRIVS. The grants from Step 1 are enough.

Use Diagnostics Pack (AWR)

Uses AWR for persistent query history instead of V$SQL. Requires the Oracle Diagnostics Pack license (included with OCI Autonomous Database at no extra cost).

Fetch table metrics

On by default. Reads the row count, size and last statistics time of each table every 30 minutes. The automatic volume and freshness monitors need these metrics.

Audit table FQN (optional)

Fully qualified name of the table for SQL test audit logs. Coalesce Quality creates the table if it does not exist. Leave empty if you don’t run SQL tests.

Catalog scope and Metrics scope (optional)

Restrict which schemas and tables are ingested into the catalog, and which of those are scanned for metrics. Leave both empty to ingest everything the user can see.

​Data we collect

​Authentication

​Create a dedicated monitoring user

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

​Step 2: Enable Query Logs (optional)

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

​Step 4: Grant DBMS_CRYPTO for reconciliation of long rows (optional)

​Network Configuration

​Input data in the UI

​Integration name

​Service name

​Host

​Port

​Username and password

​Enable SSL/TLS (TCPS)

​Verify server certificate

​Fetch query logs

​Fetch users and service accounts (optional)

​Use Diagnostics Pack (AWR)

​Fetch table metrics

​Audit table FQN (optional)

​Catalog scope and Metrics scope (optional)

Data we collect

Authentication

Create a dedicated monitoring user

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

Step 2: Enable Query Logs (optional)

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

Step 4: Grant `DBMS_CRYPTO` for reconciliation of long rows (optional)

Network Configuration

Input data in the UI

Integration name

Service name

Host

Port

Username and password

Enable SSL/TLS (TCPS)

Verify server certificate

Fetch query logs

Fetch users and service accounts (optional)

Use Diagnostics Pack (AWR)

Fetch table metrics

Audit table FQN (optional)

Catalog scope and Metrics scope (optional)