Hello @Mahendra Pawar
Welcome to Microsoft Q&A and thank you for posting your questions here.
You encountering and error: 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-26086: direct path does not support triggers
I'm sorry to hear that you're having this error. The error message is indicating that when you are trying to perform a data operation, such as an insert or update, on a table that has a trigger defined, it's not compatible with the direct path loading method. Direct path loading is a high-performance mechanism for quickly loading large amounts of data into an Oracle database, and it has some limitations, including not supporting certain triggers.
Here are some steps to help you resolve this issue: Do these independently.
- Re-run the change when the database is idle.
- Do all DDL during a maintenance window with all end-users locked-out.
- Kill the sessions that are preventing the exclusive lock.
- Examine the triggers that are defined on the table where you are encountering the error.
- If the triggers are not essential for the data load operation, you can consider disabling them temporarily.
- Instead of using direct path loading, you can use conventional path loading. Conventional path loading is slower but more flexible and compatible with triggers.
- If disabling triggers or changing the loading method is not feasible, consider loading the data in smaller batches, loading data in smaller portions might allow you to bypass the issue without compromising the database's integrity.
- If the triggers are necessary, you may need to review and optimize them to minimize their impact on the data loading process.
NOTE: These are personal experience and little research about your error code: Remember that making changes to triggers and data loading methods can have a significant impact on your database's behavior and performance, so it's very important to thoroughly test any changes in a non-production environment before applying them to your production database.
I hope this is helpful! PS: Do not hesitate to let me know if you have any other questions.
Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.