Generic Oracle Setup Guide

This guide explains the required Oracle permissions to enable OLake to perform full refresh and incremental sync. It applies to Oracle databases running on any infrastructure.

Oracle Permissions Required

To allow OLake to perform operations, the following grants must be applied to the user used in your source configuration:

Privilege	Purpose	Grant Query	Error Without It
CREATE SESSION	Fundamental login ability	GRANT CREATE SESSION TO <username>;	ORA-01045: user <username> lacks CREATE SESSION privilege; logon denied
EXECUTE ON DBMS_FLASHBACK	Required to fetch SCN	GRANT EXECUTE ON DBMS_FLASHBACK TO <username>;	ORA-00904: "SYS"."DBMS_FLASHBACK": invalid identifier
SELECT ON <owner>.<table>	Read data from source table	GRANT SELECT ON <table_owner>.<table_name> TO <username>;	ORA-00942: table or view does not exist
FLASHBACK ON <owner>.<table>	Perform Flashback AS OF queries on the table	GRANT FLASHBACK ON <table_owner>.<table_name> TO <username>;	ORA-01031: insufficient privileges

---

Where and Why Each Privilege is Needed

1. CREATE SESSION

• Use: Allows the Oracle user to connect to the database
• Without this: The connection cannot be established.

2. EXECUTE ON DBMS_FLASHBACK

• Use: To run DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, which is used to fetch the current SCN during backfill.
• Without this: Current user will be unable to retrieve the SCN.

3. SELECT ON <table>

• Use: Enables read access to the specified table(s) for the Oracle user.
• Without this: User will be unable to discover or read from the table during backfill or incremental sync.

4. FLASHBACK ON <table>

• Use: Required for queries like SELECT * FROM table AS OF SCN <scn> during chunk iteration.
• Without this: Flashback queries will fail for that table.

---

How to Check Permissions

Use the following queries to verify granted privileges:

-- List system-level privileges granted to your session
SELECT * FROM SESSION_PRIVS;

-- List all the object-level privileges which the current user has been granted
SELECT * FROM USER_TAB_PRIVS;