Why aren't my trace files being created?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Why aren't my trace files being created?

  1. #1
    Join Date
    Feb 2001
    Posts
    34

    Question

    Hello all.

    I login to my 8.1.7 instance using SQL*PLUS with a user with DBA-role authority. I run the following:

    alter system set user_dump_dest='/tmp';
    alter session set timed_statistics=true;
    alter session set sql_trace=true;
    select count(*) from some_big_table;
    exit;

    When I check /tmp, there is no trace file! What am i missing, please?

    Thanks in advance! Joe B.

  2. #2
    Join Date
    Nov 2000
    Posts
    224
    You can't change your USER_DUMP_DEST dynamically. Check for the location of this parameter in init.ora and you will find trace generated in that location.

  3. #3
    Join Date
    Feb 2001
    Posts
    34

    Smile That was my first guess (and it may still be correct)!

    Thanks for responding Sharma!

    That was my first guess, but I looked up 'alter system' on technet and it had 'user_dump_dest' listed as a dynamic parameter. I'll have to admit that it is behaving as though it is not.

    I am a contractor with DBA access to the instance, but not SYS and certainly not 'root' on the UNIX box. My problem is that the administrator is off today and I don't have access to the current file listed in user_dump_dest.

    Any ideas how I can get a trace file without calling someone on their day off?

    Joe B.

  4. #4
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    what information are you looking to gain from the trace file?
    there may be other ways to get it other than through the trace file.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Technet (and the documentation) talks about the scope of how USER_DUMP_DEST can be modified: its scope is SYSTEM, not SESSION! You must use

    ALTER SYSTEM SET USER_DUMP_DEST='/tmp';

    and not

    ALTER SESSION SET USER_DUMP_DEST='/tmp';

    With ALTER SESSON you must have gotten the following error:
    ORA-02096: specified initialization parameter is not modifiable with this option
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by joeyb

    alter system set user_dump_dest='/tmp';
    Bit sleepy jmodic?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89

    Bit sleepy jmodic?
    Indeed!

    Even after your message I had read original post couple of times before I realised Joe did used ALTER SYSTEM, not ALTER SESSION as I've consystently seen in his message before. Sorry about that.

    To my excuse - it is almost midnight over here, so I realy am a bit sleepy...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2001
    Posts
    34

    Cool Thanks for looking at it, folks!

    Thanks for trying to help me out!

    maachan - I'm trying to create a trace file that I can run TKPROF against. I'm having problems with a query that is running poorly.

    jmodic - go to sleep!

    Joe B.

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    So whats the final result? Success/Failure?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Feb 2001
    Posts
    34

    Unhappy No success!

    No success yet sambavan! I'm basically still where I started. If someone could give me a second opinion on whether:

    alter system set user_dump_dest='/tmp'

    is dynamic or not, that would be helpful.

    If it is dynamic, then I am still at a loss as to where my trace files are going and would like some suggestions that I could try.

    Thanks everyone! Joe B.

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