This content has been marked as final. Show 9 replies
Thanks for the clarification.
What about writing to the plan_table? Does it do a insert on a schema-less plan_table as well?
I can run select * from plan_table and get results, but when I try to run an explain plan on a query, I get a 'Insufficient Privileges' error. I assume this is because it is trying to write to a table outside of my allowed schema.
Did K's comments help and have you been able to resolve your privilege issue?
Explain plan has been around (with syntax stable) since Oracle V7, and the same for sys.dbms_transaction, while the use of a public synonym for plan_table and the dbms_xplan.display came in with Oracle 9, I think. SQL Developer assumes a plan_table exists (whether it's your local copy or the public synonym for sys.plan_table$) and a local copy will take precedence over the public synonym as expected.
Note that "select * from plan_table" after using Autotrace (F6) or Explain Plan (F10) from the worksheet toolbar should produce output when using SQL Dev 2.1. In 3.0, however, we do an immediate rollback after fetching the plan for display so you must rely on the Autotrace or Explain Plan result tabs.
I suppose allowing the user to specify INTO <plan_table> in Tools -> Preferences -> Database -> Autotrace/Explain Plan would have been nice in 2.1 or earlier, but given the rollback in 3.0 it would be pointless.
Gary Graham, SQL Developer Team
I can perform the
and I get 'null' for the result.
SELECT dbms_transaction.local_transaction_id FROM dual
When I run
explain plan SET STATEMENT_ID = '1305182280989' for /**/ SELECT * FROM dual
As additional information (not sure if it is useful or not):
Error starting at line 1 in command: explain plan SET STATEMENT_ID = '1305182280989' for /**/ SELECT * FROM dual Error report: SQL Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. *Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.
I my personal login for the database does not allow me to browse the tables directly, although I do have access to browse the synonms. In the synonyms, I have 'plan_table' with Owner: my username / Synonym_name: PLAN_TABLE / Table Owner: db owner username / Table Name: PLAN_TABLE
Edited by: ThatAirplaneGuy on May 16, 2011 8:49 AM
I imagine you should only get an ORA-01031 if one of the following is true:
1) No select privilege on dual.
2) No insert privilege on plan_table.
Since you are working in an environment with synonyms, the precise name and type of the underlying objects in use are obscured. That might translate into some confusion over which privileges you actually have, either directly or via a role, either on the base object or its synonym.
I would advise trying to find out for sure. Try querying the all_objects view to obtain the underlying name and type for dual and plan_table just to make sure both really are synonyms. Next query the all_synonyms view to obtain the underlying name and owner. Then verify that the relevant privileges have been granted.
The only other thing I can think of involves explain plan not correctly using invoker rights when SET CURRENT_SCHEMA is in effect. See the following link if that might be your situation: http://oraganism.wordpress.com/2010/01/08/explain-plan-for-anomaly/
So it appears that I do not have insert or update privileges on PLAN_TABLE. Our DBA's have created a TOAD_PLAN_TABLE which I have complete access to, since most of the people that touch our DB use TOAD. I was trying to use the FREE Oracle-developed tool to get to the database, but it looks like, by design, I won't be able to use it.
Is there any way I can tell SQLDeveloper to use TOAD_PLAN_TABLE instead of PLAN_TABLE? There is a option setting in TOAD that allows me to choose what table to use for explain plans - does SQLDeveloper have that option? Is it even possible to make it an option in future releases? If so, how do I go about making that request?
Thanks for all the help here so far!
Currently there is no feature to instruct SQL Developer to use an alternate PLAN_TABLE. If you wish to log an enhancement request, you can do that via the SQL Developer Exchange: http://sqldeveloper.oracle.com
Otherwise, you may have a couple of options available, depending on how tightly the DBAs have restricted your privileges.
First and simplest is to create a PLAN_TABLE in your own user's schema. That overrides any PLAN_TABLE public synonym pointing to something for which you have no insert/update privileges. You need to find the correct version of the utlxplan.sql (or something like that) for your release of Oracle.
Here is one example for Oracle 9: http://www.oracleutilities.com/Packages/dbms_xplan.html
The other alternative involves creating a private PLAN_TABLE synonym in your user's schema for TOAD_PLAN_TABLE. I think this is unlikely to succeed since a public synonym for PLAN_TABLE probably exists already. If so, you cannot override that. If not, then create the private synonym if you have the CREATE SYNONYM privilege.
Gary Graham, SQL Developer Team