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.
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.
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 …
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.
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.
Which, you have to admit, is a much shorter URL to type!
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
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
