Toad World® Forums

Plan table


#1

All

When I get TOAD to create a plan table for me, it creates one where the first
column is STATEMENT_ID of type VARCHAR2(30). The first time I try and do an
explain plan I get the following error:-

ORA-00604: error occurred at recursive SQL level 1

ORA-12899: value to large for column
.

.”STATEMENT_ID” (actual: 31, maximum:
30)

What I usually do is modify the table myself so the column is VARCHAR2(50). But
really, we shouldn’t be getting this error. Is this an issue with the
database, or with TOAD?

FYI, we’re on Oracle 10.2.0.4, and I’m using TOAD 10.5, although
this has been happening since TOAD 9.7 at the very least.

Regards,

Mike McAllister

Principal Systems Engineer

TKC Global, LLC


#2

I think it’s Toad. I’ll check it out and let you know later today.


#3

It was a Toad bug. For statement ID, we use OS User Name + ‘:’ +
date in the form of mmddyyhhnnss. So if your OS Username is more than 17
characters, it won’t fit.

I made a change to just take the first 17 characters of OS User name + the other
stuff. It’ll be in the next beta.


#4

Hi Team,
Is this fix availalbe. what is the version in which this is available.


#5

ALTER TABLE ECECUSER.TOAD_PLAN_TABLE MODIFY ( STATEMENT_ID VARCHAR2(80 BYTE));

Hi,

I did the following and to get the explain plan in TOAD:

This worked for me as my os user id is 20characters.


#6

That change should be in Toad 10.6.

What version are you running?


#7

You may need to let toad recreate the plan table if you created it a while back
say using an old version that did not do this. Toad does not verify the
plan_table columns versus the database version. So it’s a good idea to
recreate the plan table with each new toad and/or oracle release – to be
safe …


#8

Bert,

You may want to explain how to recreate the Toad plan_table. The help file
included in version 10.6 refers you to the Server Side Object wizard to create
or recreate

that table. I have gone through all of the options in the wizard and I can find
nothing to upgrade the plan table (or that even references it).

I even had Toad generate all of the scripts without running them and those
scripts did not have anything about the plan table.

I have also checked the Wiki and cannot find anything about it there either.

Lynn


#9

Open sql editor, run following command:

@?/rdbms/admin/utlxplan.sql


#10

Easier than that - just drop the table.

Next time you do an explain plan, toad will popup a dialog to help you select an
existing plan table. If there are none to be found, you can click a button to
create a new one, which will be correct for whatever version of Oracle you are
connected to.

-John


#11

Thanks for the info.

Could you have the help file updated with this information for the 11.0 release?
The current instructions are very misleading.

Lynn


#12

Yup, that’s already in the works. :slight_smile:


#13

And if you want your plan table to be “self cleaning” see the AskToad
howto here:
http://asktoad.com/DWiki/doku.php/howto_configure_an_oracle_database_to_
have_a_self-cleaning_plan_table

Which is soon to be available on the new Communities web site at:
http://communities.quest.com/docs/DOC-7994

Which, you have to admit, is a much shorter URL to type! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#14

There’s a checkbox for that in Toad, too. :slight_smile:
image001.png


#15

I’ve often felt that this checkbox should be the default for two reasons:
stays current with the Oracle release and avoids saving tons of old plans that
user may not even realize are piling up until the evil DBA J stops by to educate
someone J
image001.png