# Troubleshooting Oracle permissions

Oracle privileges can be very complex.

When unexpected permissions issues arise, here are some troubleshooting actions that you can take.

## Symptoms that can occur <a href="#symptoms" id="symptoms"></a>

Permissions issues can have several different symptoms. Here are the most common types of errors:

* `ORA-01031: insufficient privileges`
* `ORA-01039: insufficient privileges on underlying objects of the view`
* `ORA-00942: table or view does not exist`
* `ORA-00904: string: invalid identifier`

## Gathering information to help resolve issues <a href="#gather-information" id="gather-information"></a>

### Getting the current privileges <a href="#current-privileges" id="current-privileges"></a>

As the Structural user, issue the following commands:

* `SELECT ROLE FROM SESSION_ROLES`
* `SELECT PRIVILEGE FROM SESSION_PRIVS`

These reveal the system privileges that are currently in effect.

### Getting the failing SQL statement <a href="#failing-sql-statement" id="failing-sql-statement"></a>

To capture the failing SQL statement, use the Structural [diagnostic logging](https://docs.tonic.ai/app/admin/tonic-monitoring-logging/logs-redacted-diagnostic) feature.

### Get Oracle audit trail logs and identify related events <a href="#audit-trail-logs" id="audit-trail-logs"></a>

Depending on where the statement failed, you get the audit trail from the source or the destination Oracle server.

To get the audit trail logs and identify events that are related to the job:

```sql
SELECT action_name, object_schema, object_name, sql_text, event_timestamp
FROM UNIFIED_AUDIT_TRAIL
WHERE return_code != 0
AND DBUSERNAME = 'TONIC'
ORDER BY event_timestamp DESC;
```

## Types of issues and their sources <a href="#issue-types-sources" id="issue-types-sources"></a>

### Problems during job startup and schema gathering <a href="#job-startup-schema-gathering" id="job-startup-schema-gathering"></a>

These most often occur when Structural queries system views to obtain information about the source and destination databases.

Ensure that the Structural user has the `SELECT_CATALOG_ROLE` privilege or the `SELECT ANY DICTIONARY` privilege.

If the user does not have, and cannot get, either of those, then the user might need explicit privileges on objects in the source or destination schema.

The Oracle Autonomous Database `SELECT ANY DICTIONARY` privilege does not give access to objects in the `SYS` schema, which is where Structural does most of its querying.

On views in `SYS` that Structural uses, use the `SELECT_CATALOG_ROLE` role or grant the `SELECT` object privilege.

### Insufficient privileges during BulkCopy <a href="#bulkcopy-insufficient-privileges" id="bulkcopy-insufficient-privileges"></a>

The `BulkCopy` tool requires the `INSERT ANY TABLE` privilege or `INSERT` privilege on the destination table.

### Insufficient privileges when creating a view or materialized view <a href="#view-creation-insufficient-privileges" id="view-creation-insufficient-privileges"></a>

The destination schema does not have privileges on objects that are referenced in the view declaration.<br>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tonic.ai/app/setting-up-your-database/oracle/oracle-permissions-troubleshoot.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.