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

# Oracle

> Connect Validio to Oracle Database to monitor and validate your operational data.

Oracle Database is a multi-model database management system commonly used for running online transaction processing, data warehousing, and mixed database workloads. Oracle uses SQL for database updating and retrieval. Integrate Validio with your Oracle database to monitor, analyze, and validate your data assets.

In addition to monitoring and validation, Validio can build data [Lineage](https://docs.validio.io/v10.1/docs/lineage.md) for Oracle by reading query history from the database's audit trail. This requires Unified Auditing to be enabled on the database. See [Lineage requirements](#lineage-requirements).

To integrate with Oracle,

1. Create a service account in Oracle with the appropriate permissions for Validio. See [Prerequisites](#prerequisites).
2. Create a Validio credential to connect to the service account. See [Add an Oracle Credential](#add-an-oracle-credential).
3. Create a Validio source to monitor the tables in the Oracle database. See [Add an Oracle Source](#add-an-oracle-source).

## Prerequisites

Create a service account in Oracle with the appropriate permissions for Validio to read and validate your data. For more information about credentials in Validio, see [Credentials](https://docs.validio.io/v10.1/docs/credentials.md).

> [!WARNING]
>
> **Credential Permission Requirements**
>
> Validio Credentials **require** `VIEWER` access rights when connecting to sources to read and access data. Admins must ensure that they do not provide `EDITOR` access rights to their credentials.

The following SQL script provides steps for setting up a user with the necessary permissions for the source in Validio. Copy the script into a SQL worksheet and follow the steps:

```sql title="SQL" theme={null}
-- Create a user.
CREATE USER VALIDIO_USER IDENTIFIED BY <password>;
GRANT CREATE SESSION TO VALIDIO_USER;

-- Grant read access to the following system tables
-- These permissions are required for Validio catalog functionality.
GRANT SELECT ON "SYS"."V_$SERVICES" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."V_$SQL" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."DBA_USERS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_USERS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_OBJECTS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_TAB_COLUMNS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_TAB_COMMENTS" TO VALIDIO_USER;
GRANT SELECT ON "SYS"."ALL_COL_COMMENTS" TO VALIDIO_USER;

-- For data monitoring and profiling, grant read access to all relevant tables and views.
-- The following grants read access to all existing and future tables and views.
GRANT SELECT ANY TABLE TO VALIDIO_USER;
/*
The following variants may be used to grant read access to specific tables and views.

-- Grant read access to an individual table/view.
GRANT SELECT ON <myschema>.<mytable> TO VALIDIO_USER;

-- Grant read access to all existing and future tables/views within a schema.
-- Oracle 23c and above.
GRANT SELECT ANY TABLE ON SCHEMA <myschema> TO VALIDIO_USER;
*/
```

### Lineage requirements

Validio builds data [Lineage](https://docs.validio.io/v10.1/docs/lineage.md) for Oracle by reading the statements recorded in the database's unified audit trail. This setup is optional: without it, Validio still discovers your assets, infers schemas, and validates your data, but it does not derive query log–based lineage.

To capture lineage, [Unified Auditing](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/UNIFIED_AUDIT_TRAIL.html) must be enabled on the database. Once it is enabled, create an audit policy that records the statements Validio uses to derive lineage, and grant the Validio user read access to the audit trail:

```sql title="SQL" theme={null}
-- Create an audit policy to capture lineage-carrying statements.
CREATE AUDIT POLICY validio_audit_pol ACTIONS
    ALTER TABLE,
    ALTER VIEW,
    ALTER MATERIALIZED VIEW,
    CREATE TABLE,
    CREATE VIEW,
    CREATE MATERIALIZED VIEW,
    RENAME,
    INSERT,
    UPDATE,
    DELETE,
    -- NOTE: MERGE is not a supported action in Oracle 12 and should be removed from this list on 12.x versions.
    MERGE,
    TRUNCATE TABLE,
    CREATE PROCEDURE,
    CREATE FUNCTION,
    CREATE PACKAGE BODY,
    CREATE TRIGGER;

-- Enable the policy.
AUDIT POLICY validio_audit_pol;

-- Grant read access to the audit trail view.
GRANT AUDIT_VIEWER TO VALIDIO_USER;
```

> [!NOTE]
>
> **Oracle 12.x**
>
> `MERGE` is not a supported audit action in Oracle 12. Remove it from the action list before running the script on 12.x versions, otherwise the `CREATE AUDIT POLICY` statement fails.

Once the policy is enabled and the grant is in place, Validio begins building lineage. You can view Oracle assets and their relationships on the [Catalog](https://docs.validio.io/v10.1/docs/catalog.md) and [Lineage](https://docs.validio.io/v10.1/docs/lineage.md) pages.

## Add an Oracle credential

![The Oracle credential configuration form, showing host, port, user, password, and service fields](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/820-oracle-credential.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=6c5c5b88992d062e894ebab048a8244f)

Configuration for an Oracle credential

To add a credential for Oracle,

1. Navigate to **Credentials** and click **+ New credential**.
2. Under **Namespace**, select a namespace where the resources will be created.
3. For **Credential Type**, select **Oracle Credential**.
4. Fill in the credential parameter fields. Refer to the [Oracle Credential Parameters](#oracle-credential-parameters) table.
5. Check **Use for catalog and schema checks** to automatically discover credentials and add them to the catalog page.
6. Click **Create**.

Validio will validate the connection to the Oracle account. If validation passes, Validio will automatically start fetching data. If validation fails, check that you provided the correct parameter values and try again.

Once the credential is created, you can add a [source](https://docs.validio.io/v10.1/docs/sources.md) to monitor Oracle data.

### Oracle credential parameters

| Parameter | Description |
| :- | :- |
| Name | Identifier for the credential, referenced when configuring a source. |
| Host | DNS hostname or IP address to the Oracle database server. |
| Port | Port number of the Oracle database server. |
| User | Username of the Oracle account with read access to the desired table. |
| Password | Password of the specified Oracle account. |
| Service | Name of the Oracle service to connect to. |
| Enable TLS | (Optional) Check to enable encryption between Validio and the Oracle server. |
| PEM certificate bundle | When TLS is enabled, paste or upload a PEM certificate file. |

## Add an Oracle source

![The Oracle source configuration form, showing credential, database, schema, and table selection](https://mintcdn.com/validio/h4NQPs1kJwlpB1Yj/images/docs/integrations/810-oracle-source-config.png?fit=max\&auto=format\&n=h4NQPs1kJwlpB1Yj\&q=85\&s=0d83a4cf853434f503cf29da165d6b30)

Configuration for an Oracle source

To add a source for Oracle,

1. Navigate to **Sources** and click **+ New source**.
2. Under Source type, select **Oracle**.
3. Under **Config**,
   1. Select the valid **Credential** or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. **Select an existing table** -- Enter the **Database**, **Schema**, and **Table** to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the [Source Configuration Parameters](#oracle-source-configuration-parameters) table.
      2. **Use Custom SQL** -- Write a valid SQL query to specify the tables to monitor.
   3. Set the **Polling schedule**, which is how frequently the validators on the source will check for changes.
4. Under **Schema**, click **Continue** to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under **Source details**,
   1. Add **Tags** to help group related sources or to use for routing notifications.
   2. Add an **Owner** who will be the contact for incident notifications.
   3. Assign a **Priority**, which indicates the importance of incidents detected on this source.
6. Click **Continue** to create the source.
   Source names are generated automatically and  will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

### Oracle source configuration parameters

| Parameter | Description |
| :- | :- |
| Credential | Oracle credential. |
| Database | Name of the Oracle database. |
| Schema | Name of the schema that contains the table. |
| Table | Name of the table to read data from. |