Auto Optimize SQL throwing Missing Keyword error

[JohnDorlon]

Hi Peter.

I was able to reproduce the ORA-00905 missing keyword by following these steps:

1. Create this user on a DB with no publicly granted plan table

create user lowpriv identified by lowpriv;
grant create session to lowpriv;
grant create table to lowpriv;

2. Log in as that user
3. Go to Editor in Toad and enter "Select * from dual"
4. Right-click on the SQL and choose "Optimize" -> "Auto Optimize SQL" to send to SQL Optimizer.
5. A "select plan table" dialog appears. Just click OK to let Toad create one (Do NOT check "make it a global temporary table" just yet). In modern versions of Oracle, you will be able to create an empty table at this point even though you don't have tablespace quota because no storage is actually used until the first time you try to insert a row.
6. SQL Optimizer gives ORA-00905: missing keyword

I traced the session and found that it happens because SQL Optimizer tries to run this SQL, which is invalid: explain plan set STA .... That exact text was in the trace file, including the 3 dots. This is the SQL that causes the ORA-00905.

The solution, as @peter.innes_781 noted, is to grant some tablespace quota to your user. But SQL Optimizer should handle this more gracefully (ideally with ORA-01950 no privileges on tablespace BLAH. I'll let someone on the SQL Optimizer team know about it.

Another solution, if you can't get tablespace quota, is

1. Drop your plan table
2. Attempt the explain plan again from the editor.
3. When this dialog appears, check "make it a global temporary table".
   
   

Temporary tables use the TEMP tablespace, so you don't need a tablespace quota for them.

Thanks @peter.innes_781 for posting the cause of this 5 year old problem!

John