Trace Oracle Errors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Trace Oracle Errors

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367

    Trace Oracle Errors

    Dear All

    We have a third party application where the debug output provides Oracle error codes and messages, but not the offending SQL. I need to find out what the offending SQL that is causing an ORA-01401, "inserted value too large for column" is, so I can determine the table it is occurring on. Unfortunately it would appear that the trace file output does not log that an error occurred. Does anybody know a way of getting ORA- error codes and their associated SQL statements to appear in the trace file?

    Any help would be much appreciated.

    Thanks

    Austin

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    there's probably a better way but you could set the following in your init file event="1401 trace name error stacck forever" I think this will spit out a trace file in the udump each time the ora-01401 is encountered

    steve
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Steve,

    I've just run into the same kind of problem - so thanks from me as well.

    Where is this documented?

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by DaPi
    Hi Steve,

    I've just run into the same kind of problem - so thanks from me as well.

    Where is this documented?
    i don't believe it is documented, I've gathered a few such events, i'll post em tomrrow when i get back to work, they are taped to my cube in true hi-tech fashion ;-)
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I've found something in Metalink which should do:
    Note:77343.1 SQL*Trace - Notes for Applications Support Analysts
    http://metalink.oracle.com/metalink/...T&p_id=77343.1

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Thanks for you help Dapi and stmongo, that MetaLink article was just the ticket. I did quite a bit of searching on MetaLink prior to this post, but it didn't show up. Oh well..

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I rarely ever find things in Meatlink - I only use it for posting TAR's. In this case I found the article only because Steve had already told us what the answer is!

  8. #8
    Join Date
    Jul 2001
    Posts
    3
    hi,

    use the following event to get trace
    alter system set events='10046 trace name context forever, level 8'

    this will generate raw trace file.
    In the raw trace file just search for err keyword.
    the sql statement just before the err 1401 will be sql that is causing 1401 error.

    thanks

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you look in the Metalink link you will see they suggest putting
    EVENT="942 TRACE NAME ERRORSTACK LEVEL 3"
    in the init.ora for a specific error (ORA-00942 in this example). (Steve was trying hard to remember this).
    Can this be implemented by an ALTER SYSTEM? I haven't had a chance to test.

    If I did the alter system that you, vipanshrm, suggest: I'd have 100's of Mb of trace to wade through - I only see the error about once per week. And because I don't know where it is coming from, I can't provoke it! . . . Steve's suggestion only generates trace output for the error and so can be left in place for a long period - spot on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width