problem w/ explain plan ? ? ?

[hylton]

I'm trying to get explain plan to work w/ the latest version of PL/SQL developer (5.1.0.657), it worked before the upgrade to this version.

I get an error 'plan table does not exist, create it?', when I click YES, I get another error 'ORA-00955: name is already used by an existing object'.

Anybody know where to set the explain plan table name, in Toad you can set it in the options menu, can't seem to find that here in PL/SQL developer.

Thanks,
Chris

 

[Marco Kalter]

This indicates that the current user already has an object named "PLAN_TABLE", which is not a table or view. When the Explain Plan function tries to query the plan_table, it fails, and when it tries to create one, this fails too.

Find the plan_table object and rename or drop it.

------------------
Marco Kalter
Allround Automations

 

[aviator]

I am a DBA. I am also having issues with the plan table designation. I do not want everone to have their own plan table , I want them to use the sys plan_table. Where do I tell the developers to go in your product to designate to use the sys plan table. I have a public synonym on the sys.plan_table.
Thanks.
.

 

[Marco Kalter]

If you have a public synonym for the global sys.plan_table, and if the name of the synonym is plan_table, and if the developers have select, insert, update, and delete privileges on the sys.plan_table, then this should work okay.

Note however that the developers should not have their own plan_table object (table, view, or private synonym).

If this info doesn't help, just let one of the developers execute a "select * from plan_table" statement and see what happens. Maybe that will provide a clue.

------------------
Marco Kalter
Allround Automations

 

[CTucker]

Enhancement request...

It would be nice to be able to specify the SCHEMA owner to use for the PLAN table.

Thanks,

 

[rbrooker]

Hi, I use the single sys.plan_table scenario, where there is a public synonym etc. I would also like to use it for the profiler, the three tables that are required for that, but it seems not to work. any idea why?

cheers.

------------------
richard.brooker@vuw.ac.nz

 

[Marco Kalter]

Originally posted by CTucker:
Enhancement request...

It would be nice to be able to specify the SCHEMA owner to use for the PLAN table.

Thanks,

I have added this to the list, but note that you can already create a (private or public) synonym for this.

------------------
Marco Kalter
Allround Automations

 

[Marco Kalter]

Originally posted by rbrooker:
Hi, I use the single sys.plan_table scenario, where there is a public synonym etc. I would also like to use it for the profiler, the three tables that are required for that, but it seems not to work. any idea why?

According to the Oracle Documentation it should work:

"Alternately, the tables can be created in one centrally administered schema, and
profiler users can get access to the tables by creating public synonyms and granting
INSERT/SELECT privileges on the tables and sequence."

What exactly goes wrong?

------------------
Marco Kalter
Allround Automations

 

[rbrooker]

I get an error message :
Error starting profiler

result = 2

cheers.

 

[Marco Kalter]

I found a reference to this on Oracle MetaLink:

"The tables need to be set up in the schema that the profiling needs to be done."

This contradicts my quote from the Oracle Supplied Packages manual, but it seems this may indeed not work.

------------------
Marco Kalter
Allround Automations