Toad for ORACLE 13.3.0.181auto optimize SQL ORA-01031

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

I tried to use the tool auto optimize SQL in Toad for ORACLE 13.3.0.181 and in a former version 13.2. When it starts it causes the error : ORA-01031: insufficient privileges category:10.
The SQL runs fine when the same user directly runs it. It seems like some base settings for tool are not right, can you please advise?

Regards Jacques

2

In SQL Optimizer, we need to access some views for parsing, which is included the views from SYS.

We can rewrite the SQL because we know every words of SQL. Even a simple SQL ‘SELECT X from Y’. X could be a column, a function, or even a bind variable. To figure out this, we have to access the views such as ALL_TAB_COLUMNS or ALL_OBJECTS. The problem may come with accessing these views.

It could be a good try for a simple SQL such as "SELECT 1 FROM DUAL". It is really helpful for me to define the problem as my speculation.

Thanks,

Tony Ng

3

Thank you very much Tony Ng, I had a look into this and could execute select sysdate from user.
I also could execute a performance tuning on a simple custom table. The user also could select on the objects ALL_TAB_COLUMNS or ALL_OBJECTS. Also the SQL itself that was handed over could get executed. I am not sure how to find the object that limits the execution.

4

What if you optimize a simple SQL such as "select 1 from dual"? If the same problem happened, we can focus at the SYS objects from the auto optimizing tool. Otherwise, it could the problem from the source SQL.

Tony Ng

5

Hi Tony, Thank you. It seems like my answer was not 100% understood. I will try to reword:

  • a simple SQL like "select sysdate from user" could be executed and some analysis did start in auto optimize.
  • a simple SQL based on one single custom table like select * from table were name = 'aby' could be executed and some analysis did start in auto optimize.
  • the user that executes the auto optimize SQL tool has access to ALL_TAB_COLUMNS and ALL_OBJECTS
  • The user can run the SQL to be optimized. It contains several joins
  • the user runs into the error, if the more complex SQL is run.

I hope this helps?

Regards Jacques

6

Sorry, I have overlooked.

So, I suppose you have no problem to access the objects which Auto-Optimization needed. The error could be come from SQL’s objects. However, I found the error very commonly happened for the case of user doesn’t have read and execute permission at target OS executables. Or you may try to sign in as “sysdba“ for testing.

Tony Ng

7

Hi Tony,

thank you for your answer. It seems like a much more empowered user (select on all tables allowed) can execute the SQL in the optimize tool. From a data privacy/security perspective this
Do you have any hints/check lists which other sys views would need to be allowed to be accessed for this?

regards Jacques

8

There is a better ways for you find out where is the problem exactly from. Toad have a function call "SQL Tracker".

image
image835×780 161 KB

And you may capture all the SQL run through the particular process. The error will tell you what's the exact problem is.

image
image2527×1017 277 KB

Thanks,

Tony Ng