This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 42%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAZ%3C/text%3E%3C/svg%3E)
We are trying to copy data to an Oracle DWH, and we are facing issue when trying with different setups on the “Write Batch Size” parameter.
The copy activity works when we set the “Write Batch Size” to 1, but of course performances are bad, it writes about 10.000 rows in 5 minutes.
To speed up copy we are trying to set the parameter to the default value of 10.000
But in this case, copy data fails with the following error:
Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
Error in parameter 1.,Source=msora28.dll,'
So far we have INSERT privileges on Oracle Schema (in fact writing works with parameter = 1 and using direct SQL), but it looks like something different is used with the default value on Write Batch Size
We don’t want to focus on the error message, it is obvious that it has been raised on the Oracle Side.
But we need more information in order to understand what’s causing the issue. It looks like ADF is using two different ways to copy data depending on the value of the parameter.
Any help would be greatly appreciated.
Thanks in advance
Alessandro
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 48%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Hi @Alessandro Zenato
Welcome to Microsoft Q&A platform, thank you for reaching out to us with your query related to copying data to an Oracle DWH using Azure Data Factory.
The "Write Batch Size" parameter in ADF specifies the number of rows to be written in a single batch. When you set the parameter to 1, it writes only one row at a time, which is why the copy activity works but with poor performance. When you set the parameter to the default value of 10,000, it writes 10,000 rows in a single batch, which is faster but fails with an error message.
The error message you are seeing indicates that there is an issue with the Oracle side, specifically with insufficient privileges. It is possible that the default value of the "Write Batch Size" parameter is using a different method to copy data, which requires additional privileges.
To resolve this issue, you can try granting the necessary privileges to the Oracle user account that you are using to connect to the Oracle DWH.
Additionally, you can try increasing the "Connection Timeout" parameter in ADF to a higher value to see if it resolves the issue.
I hope this information helps. Let me know if you have any further questions or concerns.
Hi @Harishga
thank you for your feedback and explanation.
I asked to Oracle admins for further privileges, but their response is that I already have enough privileges for INSERT into the DWH (in fact writing one row a time works). So I need more information to share with them about what's happening when setting the "Write Batch Size" to 10,000.
When you talk about the "Connection Timeout" is the timeout related to the connection to Oracle or there is another setting for "Connection Timeout" in ADF?
Thank you very much!
Alessandro
When you set the “Write Batch Size” to 10,000, it means that you are trying to write 10,000 rows at a time to the DWH. This can cause performance issues if the DWH is not optimized for such large batch sizes. You can ask the Oracle admins to check the DWH configuration and see if it can handle such large batch sizes.
Regarding the “Connection Timeout”, it is the timeout related to the connection to Oracle. It specifies the maximum amount of time that a connection can remain idle before it is closed by the server. If the connection is closed due to a timeout, you will need to re-establish the connection to continue working with the database.
As for the “Connection Timeout” in ADF, it is the minimum amount of time that the Open method can take to obtain a pooled connection before the request is terminated.
I hope this information helps you. Let me know if you have any further questions or concerns.
Hi @Alessandro Zenato
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Hi @Harishga sorry for the delay. The error raises also if I try to set the parameter to 2. so it's not about the batch dimension DWH side. I've also tried to increment "Connection Timeout", but issue still remains. Thanks Alessandro
Hi @Alessandro Zenato
Thank you for the update.
If the error is occurring even when you set the "Write Batch Size" parameter to 2, it is possible that there is an issue with the Oracle driver or the connection string.
You can try updating the Oracle driver to the latest version and see if it resolves the issue. Additionally, you can check the connection string to ensure that it is correct and contains all the necessary parameters.
If the issue persists, you can try enabling diagnostic logging in ADF to get more information about the error. You can also check the Oracle logs to see if there are any errors or warnings related to the connection or the insert operation.
I hope this information helps. Let me know if you have any further questions or concerns.
Hi @Alessandro Zenato
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
We resolved the issue changing the connection to Oracle. Now we use an Oracle user with the same name of the Oracle schema. (I don't know much about Oracle configuration becuase it is managed by third-party).
Nothing else changed, using this different user the copy activity runs fast.
Alessandro