Results 1 to 9 of 9

Thread: table lock

  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Unanswered: table lock

    hi,
    plz find the following query

    select * from v$sqlarea where address in (select sql_address from v$session where username is not null);

    if i execute it i will get sql statements which is lockd.
    i wanted to know in which machine tat particular query is running.

    can u tell me how can i join with v$session.
    i need sqlquery, and machine which has locked in my output

    thanx

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a script I use to find db locks ...

    HTH
    Gregg
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    See if this works...

    Code:
    SELECT sql.*, sess.machine 
    FROM v$sqlarea sql, v$session sess 
    WHERE sql.address = sess.sql_address
    AND sess.username IS NOT NULL;
    JoeB
    save disk space, use smaller fonts

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    gbrabham,

    Why do you have a rollback statement in your script, even though it is only SELECTing from those system views?

    Thanks,
    Chuck

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    HABIT .... As a habit, I try to always rollback (or commit) in scripts ... that
    way I'm safe ... I will usually reuse scripts many times over at different client
    locations and may not remember exactly what all the script contains ...

    Habit ... or stupidity ...

    Gregg

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    > I try to always rollback (or commit) in scripts ...
    > that way I'm safe

    Are you? What if you did a load of updates, then ran the "find_db_locks" script just to check something, and suddenly it's undone all your work? That seems like a fairly dangerous side effect of running a report.

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Personally, I always run things together that need to be run together ...
    If I were doing any dml and wanted to check on other things I always open a separate SQL session. Being a consultant, I always take extra safeguards ... People just don't seem to want to pay for "screwing up" their data ... go figure ...

  8. #8
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Thanks for the script, gbrabham!
    I'll try it after I delete that rollback sentence... :P

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad to be of assistance ...

    Gregg

Posting Permissions

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