Results 1 to 6 of 6

Thread: Process history

  1. #1
    Join Date
    Jan 2010
    Posts
    72

    Unanswered: Process history

    I have one job running against oracle database which queries some tables and do the opertaions like select,update,insert and delete. When ever job stucks, we used to kill the job and run the stats. When the job is running, we can monitor and findout which statement is currently running. If we kill the job without seeing the stmt details, is it possible in oracle to look at the history information (where the job was stuck actually).

    Thank you.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >is it possible in oracle to look at the history information (where the job was stuck actually).
    It might be possible depending upon actual Oracle version

    V11 does capture & record some SQL history
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could create a logging procedure which uses autonomous_transaction to log to a log table. Then call the logging procedure from your script/procedure after each statement. This will then log how far you got even if the job is killed or rolled back.

    Alan

  4. #4
    Join Date
    Jan 2010
    Posts
    72
    Hi Anacedent,

    Thanks for your reply.

    We are using version 10, is there anyway we can look here?

    You suggested it is possible in V11. can you send me the link or the process to more details on this.

    Thank you.

    Regards,
    Regtha

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    the following views exist in V11.2
    DBA_HIST_SQLBIND
    DBA_HIST_SQLCOMMAND_NAME
    DBA_HIST_SQLSTAT
    DBA_HIST_SQLTEXT
    DBA_HIST_SQL_BIND_METADATA
    DBA_HIST_SQL_PLAN
    DBA_HIST_SQL_SUMMARY
    DBA_HIST_SQL_WORKAREA_HSTGRM

    ALTER SESSION SET SQL_TRACE=TRUE
    above will capture all SQL for the current session
    if above were done at the start of the batch job, you could see how far it gets
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2010
    Posts
    72
    Thanks to all.

Posting Permissions

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