Connect SPSync to Oracle Database

SPSync writes to Oracle Database through the python-oracledb driver in thin mode. No Oracle Instant Client and no other library is needed on the machine that runs SPSync.

Before you start

An Oracle Database you can reach, and the name of the pluggable database service, for example FREEPDB1 on current releases. You need a DBA login to do the one-time setup.
A name for the schema SPSync loads into. On Oracle a schema is a user account, so that name must be a user that already exists.
Identifier names: Oracle 12.2 and later allow 128 bytes. A release before 12.2 allows 30, so SharePoint list and column names have to be short enough that the generated names fit.

Create the database and a least-privilege user

This is the part that differs from every other destination. Oracle cannot create a schema on the fly, and the merge SPSync uses needs a second schema for staging. A DBA creates two users once. SPSync’s own tests run the staging user, the quotas and the ANY TABLE grants below against a throwaway Oracle container, with the passwords replaced. The container image creates the app user itself, so the CREATE USER sync_app lines are from vendor docs, not run here.

CREATE USER sync_app IDENTIFIED BY "use-a-long-random-password";
GRANT CONNECT, RESOURCE TO sync_app;
ALTER USER sync_app QUOTA UNLIMITED ON USERS;

CREATE USER sync_app_staging IDENTIFIED BY "use-another-long-password";
GRANT CONNECT, RESOURCE TO sync_app_staging;
ALTER USER sync_app_staging QUOTA UNLIMITED ON USERS;

GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE,
      INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO sync_app;

sync_app is the schema SPSync loads into and the user it connects as, so dataset_name is sync_app. sync_app_staging must be called <dataset_name>_staging. The ANY TABLE privileges are database-wide. Oracle has no way to scope them to one schema, so think before granting them on a shared production instance.

If you switch on reporting views, sync_app also needs CREATE VIEW. This grant is from vendor docs, not run here:

GRANT CREATE VIEW TO sync_app;

Allow SPSync to reach it

The listener is on TCP 1521 by default. On the desktop app and a self-hosted server the connection starts from that machine, so allow that machine through the host firewall and any network access list. For the Azure managed application, allow the address it connects out from instead of the whole internet.

For Oracle on a cloud virtual machine, that means a security group or network security group rule for the listener port. For a managed Oracle service, use that service’s access control list for the same purpose.

Encrypt the connection

Plain Oracle Net traffic is not encrypted unless the server is set up for it. SPSync has not been run against a TLS listener, so what follows is from the python-oracledb documentation and is not verified here: a tcps listener (usually port 2484) and a connect descriptor that uses the tcps protocol. Whatever you set up, use Test connection and a first sync to confirm it before you rely on it.

Authentication SPSync supports today

A user name and password in the connection string. Kerberos, external or OS authentication, Oracle wallets and Microsoft Entra ID or other token sign-in are not supported.

Autonomous Database

Oracle Autonomous Database normally connects with a downloaded wallet (mutual TLS). SPSync has no field for a wallet, and it has not been run against Autonomous Database, so treat the following as documentation only and not as a tested path. If your database accepts TLS connections without a wallet, a plain oracle+oracledb:// string with the service name from the console is the only shape SPSync could use. Wallet-based mutual TLS is not available.

Connect from SPSync

The string names the pluggable database with service_name, not in the path. HOST is your server’s name:

oracle+oracledb://sync_app:PASSWORD@HOST:1521/?service_name=FREEPDB1

Putting the name in the path (.../FREEPDB1) makes Oracle treat it as an old-style SID, which does not find a pluggable database.

Desktop app: open the Destination screen, choose Oracle Database, paste the string into Connection string and choose Test connection.

CLI:

export SPSYNC_ORACLE_DSN='oracle+oracledb://sync_app:PASSWORD@HOST:1521/?service_name=FREEPDB1'
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind oracle --connection-string-env SPSYNC_ORACLE_DSN --dataset-name sync_app \
  --client-id <entra-app-client-id>

AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination. You enter the string on the secure entry page.

What Test connection and doctor check

Both open a connection and run SELECT 1 FROM dual. A pass means the listener is reachable, the service name resolves and the password is accepted. It does not check that sync_app exists as a schema, that sync_app_staging exists, or that the privileges are granted. A missing user or privilege appears on the first sync.

Common errors

What you see	What it means and the fix
`ORA-12541: TNS:no listener` or `DPY-6005`	The host or port is wrong, or a firewall blocks 1521.
`ORA-12514` or `DPY-6001`	The service name is not registered. Check `service_name` with your DBA.
`ORA-01017: invalid username/password`	Wrong password, or the user is locked.
`ORA-01918: user 'SYNC_APP_STAGING' does not exist`	The staging user is missing or has the wrong name. It must be `<dataset_name>_staging`.
`ORA-01031: insufficient privileges`	The `ANY TABLE` grants or `CREATE VIEW` are missing.
`ORA-01950: no privileges on tablespace`	The user needs `QUOTA UNLIMITED ON USERS` (or its tablespace).