Forum Discussion
Advocate IIIOracle Upsert Least Privileges Needed for Copy Activity - Interim Table Creation
Hey All, I'm attempting to do an Upsert with Data Factory in an Oracle DB and im looking to find out what is the minimum Permissions needed for Upsert. I'm currently getting an Oracle Error Code=OracleCreateInterimTableError
Failed to create interim table in Oracle.,Message=ORA-01031: insufficient privileges
It looks like its failing to create the Interim Table. So trying to find out what privilege is needed to resolve this.
Hi kritchensBMcD,
For Upsert in Fabric / Azure Data Factory Copy Activity, the interim (staging) table is created and used as part of the runtime execution. The behavior is:
- The service creates the interim table at the start of each run
- It uses this table to stage data and apply the insert/update (merge) logic
- Once the activity completes, the table is cleaned up (dropped) automatically
So effectively, the interim table is ephemeral and managed by the service, and it is recreated for each pipeline execution, rather than being reused across runs.
One thing to be aware of: in rare cases where the activity fails mid-execution, the interim table may remain temporarily, but under normal successful runs, it is removed automatically.
Thanks,
Prashanth
7 Replies
v-prasareCommunity Support
Hi kritchensBMcD,Upsert in Fabric / Azure Data Factory Copy Activity requires creating an internal interim (staging) table to apply insert/update logic; this is part of the service’s implementation (for example, creating a table via SELECT INTO). Because of this, the Oracle user must have CREATE TABLE privileges, otherwise the operation fails with ORA-01031: insufficient privileges, which explicitly indicates missing required permissions.thanks,PrashanthMS Fabric community support- kritchensBMcD
Hello, does the Table owner need to be the user as well? We are using a secondary account vs the schema account.
so trying to understand all needs for the account we added create table but still ran into issues where it couldn't find the table after creation due to the account needing explicit share of the table. So would it also need full rights to see objects in the schema?
- v-prasare
Hi kritchensBMcD.,
You don’t need the table owner to be the same user, but when you’re using a secondary account instead of the schema owner, Oracle requires explicit permissions for both object access and visibility. In your case, even though adding CREATE TABLE allows the interim table to be created, the follow-up error where it couldn’t find or reuse the table typically points to missing privileges on the target schema objects.
The secondary account will need explicit grants on the target tables (at least SELECT, INSERT, and UPDATE) and the ability to reference those objects within the schema, since Oracle does not automatically expose them across users. So no, it doesn’t need full rights on the schema, but it does need sufficient object-level permissions and visibility (either via direct grants or synonyms). This is a common behavior in cross-schema setups, and ensuring proper access to both the interim table and target objects should resolve the issue.
Thanks,
Prashanth
- v-prasare
Hi kritchensBMcD,
We would like to confirm if our answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support - v-prasare
Hi @kritchensBMcD,
We would like to confirm if our answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
