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

# Oracle

> Connect Datafold to Oracle Database for data diffing, reconciliation, and migration validation. Includes setup instructions and required permissions.

<Info>Oracle 19c and later are fully supported. If you use an Oracle version older than 19c, please contact [support@datafold.com](mailto:support@datafold.com) before proceeding.</Info>

<Info>Column-level Lineage is not currently supported for Oracle.</Info>

**Steps to complete:**

1. [Create a Datafold user in Oracle](#create-a-datafold-user)
2. [Grant read access to your data](#grant-read-access-to-your-data)
3. [Grant required system privileges](#grant-required-system-privileges)
4. [Configure the connection in Datafold](#configure-in-datafold)

> A [full script](#full-script) is available at the bottom of this page.

## Create a Datafold user

Datafold connects to Oracle using a dedicated database user. This user needs its own schema where Datafold can create temporary working tables during data diffs.

If your Oracle instance uses a multitenant architecture (CDB/PDB), first switch to the pluggable database where your data lives:

```sql theme={null}
-- Only needed for multitenant (CDB/PDB) setups. Skip if you use a single-tenant database.
-- Replace YOURPDB with the name of your pluggable database (e.g., XEPDB1).
ALTER SESSION SET CONTAINER = YOURPDB;
```

Then create the Datafold user:

```sql theme={null}
CREATE USER DATAFOLD IDENTIFIED BY <your_secure_password>;

-- Allow Datafold to connect to the database
GRANT CREATE SESSION TO DATAFOLD;

-- Allow Datafold to create temporary working tables in its own schema
GRANT CREATE TABLE TO DATAFOLD;
```

## Grant read access to your data

Datafold needs `SELECT` access on every table you want to diff. There are two approaches depending on how broad your access requirements are.

### Option A: Grant access to all tables (recommended for most migrations)

If Datafold should be able to diff any table in the database, grant the `SELECT ANY TABLE` system privilege:

```sql theme={null}
GRANT SELECT ANY TABLE TO DATAFOLD;
```

This is the simplest approach and avoids the need to update grants each time new tables are added.

### Option B: Grant access per schema

If you need to restrict Datafold to specific schemas, run the following block for each schema. Replace `YOURSCHEMA` with the schema name:

```sql theme={null}
BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'YOURSCHEMA') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON "YOURSCHEMA"."' || t.table_name || '" TO DATAFOLD';
  END LOOP;
END;
/
```

Repeat for each schema that contains tables you want to diff.

<Warning>Option B only grants access to tables that exist at the time you run the script. If new tables are added later, you will need to re-run the grant or add individual `GRANT SELECT` statements for those tables.</Warning>

## Grant required system privileges

Datafold requires two additional privileges to function correctly.

### Tablespace quota (required)

During a diff, Datafold creates temporary tables in the `DATAFOLD` schema to store intermediate results. The user needs permission to consume disk space for these tables.

You can grant unlimited tablespace:

```sql theme={null}
GRANT UNLIMITED TABLESPACE TO DATAFOLD;
```

Or, if your DBA prefers to cap disk usage, assign a specific quota instead:

```sql theme={null}
-- Example: allow up to 1 GB on a specific tablespace
ALTER USER DATAFOLD QUOTA 1G ON <tablespace_name>;
```

## Full script

Copy and customize this script for your environment. See the comments for what to change.

```sql theme={null}
----------------------------------------------------------------------
-- Datafold Oracle setup script
-- Run as a DBA or user with GRANT privileges
----------------------------------------------------------------------

-- Step 1: Switch to your pluggable database (multitenant only — skip if single-tenant)
-- ALTER SESSION SET CONTAINER = YOURPDB;

-- Step 2: Create the Datafold user
CREATE USER DATAFOLD IDENTIFIED BY <your_secure_password>;
GRANT CREATE SESSION TO DATAFOLD;
GRANT CREATE TABLE TO DATAFOLD;

-- Step 3: Grant read access (choose Option A or Option B)

-- Option A: Access to all tables (simplest)
GRANT SELECT ANY TABLE TO DATAFOLD;

-- Option B: Access to specific schemas only (repeat per schema)
-- BEGIN
--   FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'YOURSCHEMA') LOOP
--     EXECUTE IMMEDIATE 'GRANT SELECT ON "YOURSCHEMA"."' || t.table_name || '" TO DATAFOLD';
--   END LOOP;
-- END;
-- /

-- Step 4: Required system privileges
GRANT UNLIMITED TABLESPACE TO DATAFOLD;

-- Or, to cap disk usage:
-- ALTER USER DATAFOLD QUOTA 1G ON <tablespace_name>;
```

## Configure in Datafold

Once the Oracle user is created and grants are in place, add the connection in Datafold.

| Field Name                      | Description                                                                                                                                        |
| ------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Name**                        | A display name for this connection within Datafold (e.g., "Oracle Production")                                                                     |
| **Host**                        | The hostname or IP address of your Oracle database server                                                                                          |
| **Port**                        | The Oracle listener port (default: `1521`)                                                                                                         |
| **User**                        | `DATAFOLD` (the user created above)                                                                                                                |
| **Password**                    | The password you set when creating the `DATAFOLD` user                                                                                             |
| **Connection type**             | Choose **Service** or **SID** depending on your Oracle setup. Use **Service** if unsure — it is the default for most modern Oracle configurations. |
| **Service (or SID)**            | The Oracle service name or SID to connect to (e.g., `XEPDB1` or your database name)                                                                |
| **Schema for temporary tables** | `DATAFOLD` — this is the schema created automatically when you created the `DATAFOLD` user                                                         |

Click **Create**. Your data connection is ready!

## Troubleshooting

### `ORA-00942: table or view does not exist`

The Datafold user does not have `SELECT` access on the table being diffed. Verify that you ran the grants from [Step 3](#grant-read-access-to-your-data) for the correct schema.

### `ORA-28000: the account is locked`

The `DATAFOLD` user account has been locked, typically due to failed login attempts. Unlock it with:

```sql theme={null}
ALTER USER DATAFOLD ACCOUNT UNLOCK;
```

### `ORA-01950: no privileges on tablespace`

The `DATAFOLD` user does not have a tablespace quota. Run the tablespace grant from [Step 4](#grant-required-system-privileges).