Fix for TOAD error ORA-02404: specified plan table not found

TOAD can report error “ORA-02404: specified plan table not found” when trying to display execution plans:

TOAD Error

Two options to fix:

  1. Run script toadprep.sql. This creates the table toad_plan_table, among other things, and is meant to be run by a user with DBA privileges.
    or
  2. Change TOAD to use the same plan table (= plan_table) that other applications use. Do this in TOAD through the View -> Options -> Oracle -> General window, then change the Explain Plan Table name to “plan_table”, removing the “TOAD_” from the beginning:

TOAD fix

Plan_table itself ought to be in every database, although in versions 9i and earlier that required DBA intervention, was not created by default. See next post for details on that.

TOAD error fix from:

Posted in Performance tuning
52 comments on “Fix for TOAD error ORA-02404: specified plan table not found
  1. Raj says:

    Its absolutely good!!!!!!!
    Thanks alot for giving me a described solution for this error …

  2. Vivek Singh says:

    Abolutely true good work!!!!!! keep posting

  3. David Landgren says:

    I can confirm that the option 2 fix works for Toad 7.5 against a 10g database.

    I’ve been wondering about this for some time. I’ve been using Toad for ages on a 8.1.7 Oracle database, and since the beginning of this year I’ve been using it on a 10g database as well.

    The “Explain Plan” didn’t work in 10g and while I knew I should fix it I hadn’t had the need. This afternoon I finally needed to evaluate which of two selects was more efficient. Hence a web search that brought me to this page.

    In the process, the Explain Plan view helped me realise that I had forgotten a join between two tables which caused one of the statements to do a full table scan instead of an index scan. Yay!

  4. Nisha says:

    Thanks , It worked

  5. Mario says:

    Excelente!!! funcion贸 a la primera, muchas gracias.

    Saludos.
    Santiago, Chile.

  6. hans says:

    it was required n i got it here, tks a lot

  7. Pb says:

    thanks, it works 馃榾

  8. Carla says:

    This was just what I was looking for — thanks!

  9. Matt says:

    Thanks for the info.

  10. Thomas says:

    thank you for the working tip!

  11. Sunil says:

    This one really works man. thanks a lot.

  12. aviana says:

    Hi
    I tried the second option and it worked..
    now i can see explain plans.
    I didnt have a clue where to look for toadprep.sql…does anyone has a clue?

  13. instructions i got from help menu in Toad says:

    To Load and Execute TOADPREP.SQL

    1. Connect as System or a DBA.

    2. Go to File > Open File.

    3. Open your TOAD > TEMPS folder.

    4. Double-click TOADPREP.SQL.

    5. The TOADPREP.SQL loads into your SQL Editor.

    6. Execute each statement in the script by clicking on the statement and pressing .

    If this is your first time running the TOADPREP.SQL script and you execute a Drop statement, you receive an error message. This is because there is nothing to drop. You can click OK and continue. However, you can just skip all the drop statements (don’t execute the drop statements), since TOAD has nothing to drop.

    7. For the Connect statement, you need to modify the statement for your connection.

    8. When you exit, TOAD will ask you if you want to save changes. Click Yes.

    Quest Software

  14. John says:

    Thanks a lot for the solution, i really had good time.

  15. Mohan says:

    I tried to fix my problem with the second option but it thrown me the following error message.

    The following files specified on the Executables tab could not be found:

    SQL*Loader

    Ping

    I appreciate your help. Thanks in advance.

  16. someshrocking says:

    It Works !!! Thanks i appreciate your help

  17. Kanags says:

    Great! Thanks a lot for sharing the tip.

  18. leo says:

    thanks!!!

    i stumbled upon this link when googling. tried your suggestion, and it works great!
    thank you. this really helps.

  19. Anil says:

    Great!! It’s working thanks for the help

  20. desmus says:

    Bulls eye

  21. Onlyice says:

    Thanks… helped in the right time…

  22. hari says:

    thanks alottt…

  23. Ramesh Garikamokkala says:

    Thanks a lot!!!!….Option 2 has worked out with out any errors….

  24. Thies says:

    Thanks ! Why does Toad use its own plan table anyway..

  25. jose luis says:

    Gracias, la segunda opci贸n me funcion贸 a la perfecci贸n.

    Saludos.

  26. Itu says:

    Thanks I am a new at using TOAD but I only spent a couple of second to apply the solution without a hitch. A very straight for ward answer and good explanation to the problem.

  27. Ajith KR says:

    I tried the second option and it worked.
    Thanks guys.

  28. Raghavendra says:

    It really worked. solution is stright to the point.
    thanks a lot for sharing the tips.

  29. Mahesh says:

    This really worked for me .Thanks alot for providing this solution.Also please guide me to analyse this explain plan so as to optimise performance of the query.

  30. arun says:

    I tried with the second option and it worked.
    Thank you

  31. Ayaz says:

    Thanks a ton for valuable information. Really I appreciate your help

  32. KRRAJU says:

    Thanks a lot it’s worked 馃檪

    -Raju

  33. bhasker says:

    Thanks .. The second one really worked for me

  34. Violet says:

    thanks.. worked like a charm 馃檪

  35. Ruchi Vipasa says:

    Hey thanks a ton!! it worked.

  36. Monika says:

    Thanks a lot it works

  37. NTHIN says:

    XLENT MAN!!!
    ITS WORKING…
    THX VERY MUCH…..

  38. Ganesh says:

    Thnx a lot everyone !!!

  39. YEJAZ says:

    thanks,it is working fine now.

  40. Vikram says:

    Thanks man It works!!

  41. Moiz Tankiwala says:

    We have Toad Version 7.6.0.11, but cannot locate the TOADPREP.SQL file in the TOAD installed location.
    If we remove the TOAD_ prefix to the Explain Plan Table, we got the following error message:
    “The following files specified on the Executables tab could not be found:
    Ping”.
    SOLUTION
    =======
    Go to the Executables node on the same dialog box and in click the search button next to the Ping text box. My previous value of “C:\WINNT\system32\PING.EXE” got updated to “C:\WINDOWS\system32\PING.EXE” and this fixed the problem.

  42. Sri says:

    Awesome man thanks a ton for the info 馃榾

    Cheers,
    Sri.

  43. Yogesh Jadhav says:

    Thanks u sir

  44. Ramgokul says:

    Thanks a lot ..2nd one worked for me 馃檪

  45. Viju says:

    Thanks alot

  46. D says:

    it was useful. Thanks

  47. luigi says:

    wonderful

  48. Tom says:

    Your quick Solution still working 5 years later.. Thanks!

  49. Lalit says:

    It works!! Thanks for the help Andrew.
    馃檪

  50. om says:

    Thanks for info.. keep it up

  51. sandrazka says:

    Me funcion贸 de maravillassss. Muchas gracias.

  52. sam says:

    thanks

1 Pings/Trackbacks for "Fix for TOAD error ORA-02404: specified plan table not found"
  1. […] Explain Plan Table name to 鈥淧LAN_TABLE鈥, removing the 鈥淭OAD_鈥 from the beginning.Reference: http://www.andrewfraserdba.com Thanks to Andrew Fraser #dd_ajax_float{ background:none repeat scroll 0 0 #FFFFFF; border:1px solid […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*