Toad Explain Plan : Statement ID Value too large

Toad for Oracle
1

Hi All,

I’ having a problem when i tried to explain plan and hit the error “value too large for column plan_table.statement_id”.

I have been altering the column from 30 byte to 50 byte, only if i have the privilege to create or alter the table to solve the issue.

But now i have only read access to the DB, i cant do any of the solution above.

Understand from some other thread , that the statement_ID was generated with OS user + some DATE timestamp .

Is there any way i can change that config locally to trim some of the characters.

I’m using Toad version 10.1.1.8

Thanks!

2

There’s no way to configure it. You’d have to change your OS User Name to something shorter, or have the plan table changed.

3

hmm… thanks John…

Previously i saw an article that there was another way to change the configuration but editing the Toad.ini file but that was for Toad version 9. So i thought it would have the same resolution as well.

Not to sure how to change the OS User Name since this is an office laptop and the Toad license was register under this OS User Name(technically belong to my company).

Is there a way to change the OS User Name for a shorter one?

4

You really ought to pursue the "getting the plan table changed’ route before you mess with your OS Username. Changing the table is by far the easiest solution to this problem. I tried to change my OS Username once and I was left with remnants of the old name - it wasn’t a complete change - it was messy. I ended up changing it back.

The next easiest thing to do would be to upgrade your Toad. Starting in Toad version 10.6, we only take the first 17 characters of OS Username (we add 13 characters to it to form the Statement ID) so you won’t get a “value too large” error if you upgrade your Toad to at least 10.6.

But, if you want to try changing your OS Username, you can do that in Control Panel under “User Accounts”. Change it to something 17 characters or less. I don’t think it will affect your Toad registration, but you might want to check with your IT department first to make sure it won’t cause any problems with their infrastructure.

5

thanks John… Changing Username is not really option for me as this is link to an enterprise account… it will be lengthy process to do this.

is there update/patch available for toad? i mean as in without reinstalling the toad?

6

Well, there have been a lot of updates since 10.1, but there is no 10.1 patch which fixes this bug. You’d have to install 10.6 or newer. You could keep version 10.1 installed if you wanted.