9 Replies Latest reply on May 20, 2011 1:38 PM by cjones

    Choose specific plan_table in SQL Developer 2+?

      My company is running on an older of Oracle, so we don't have the much 'friendlier' plan_table system that is currently being used.

      I have a plan_table created for my specific connection/login, but it appears that SQL Developer is looking for the SYS based plan_table found in later versions of Oracle.

      Is there any way, in SQL Developer, that I can make it use my own plan_table for Explain Plans? I have looked through preferences, but I can't find anything that would let me do it. My colleagues that are using TOAD said that they were prompted to choose a plan_table. I prefer SQL Developer, but will be forced to go to TOAD if I can get explain plans working.

      Thanks ahead of time...

        • 1. Re: Choose specific plan_table in SQL Developer 2+?
          Vadim Tropashko-Oracle
          Your statement is incorrect: SQL Dev is using "select * from plan_table" without explicit schema referring to whatever object plan_table may be.
          1 person found this helpful
          • 2. Re: Choose specific plan_table in SQL Developer 2+?
            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.
            • 3. Re: Choose specific plan_table in SQL Developer 2+?
              Sqldev does issue
              SELECT dbms_transaction.local_transaction_id FROM dual
              before e.g.
              explain plan SET STATEMENT_ID = '1305182280989' for /**/ SELECT * FROM dual
              Which of those fails?

              1 person found this helpful
              • 4. Re: Choose specific plan_table in SQL Developer 2+?
                Gary Graham-Oracle
                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
                • 5. Re: Choose specific plan_table in SQL Developer 2+?
                  I can perform the
                  SELECT dbms_transaction.local_transaction_id FROM dual
                  and I get 'null' for the result.

                  When I run
                  explain plan SET STATEMENT_ID = '1305182280989' for /**/ SELECT * FROM dual
                  I get:
                  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.
                  As additional information (not sure if it is useful or not):
                  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
                  • 6. Re: Choose specific plan_table in SQL Developer 2+?
                    Gary Graham-Oracle
                    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/
                    • 7. Re: Choose specific plan_table in SQL Developer 2+?
                      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!

                      • 8. Re: Choose specific plan_table in SQL Developer 2+?
                        Gary Graham-Oracle
                        Hi CJ,

                        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.

                        Good luck,

                        Gary Graham, SQL Developer Team
                        • 9. Re: Choose specific plan_table in SQL Developer 2+?
                          Thanks for the information.

                          I'll submit a request for the functionality in SQL Developer.

                          Unfortunately, the option of me creating any tables or synonyms is very unlikely to happen - I had to beg to even have read permission directly from the database. ha!

                          Thanks again!