I am trying to analyze the execution plan for a merge statement that I have written, but when I run it in the database it is throwing 01031. 00000 - "insufficient privileges" error. The same explain plan when used for select statement is working fine and the plan is getting generated successfully. can anyone help on why this is happing for the merge statement alone?
To perform the EXPLAIN PLAN statement you need the same privileges as for the execution of the statement.
So if you have only SELECT granted on the merged table you will not be able to excecute the MERGE and perform the EXPLAIN PLAN statement for it.
Here is the relevant citation form the Documentation
You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, then you must have privileges to access both the other view and its underlying table.