rollback issues. Any ideas
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: rollback issues. Any ideas

  1. #1
    Join Date
    Jan 2001
    Posts
    157

    Angry

    My partner and I are having a debate on whether a select statement uses rollback segments or not?
    Does selects use rollback segments ? If so How?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You may have a locking issue. Run a query against dba_ddl_locks such as:
    Code:
    col username for a12
    col object_locked for a40
    col MACHINE/PID for a30
    col program for a30
    set linesize 132
    set verify off
    accept objowner prompt 'Object Owner: '
    accept objname prompt 'Object Name : '
    
    select s.username, s.sid, s.program, d.owner || '.' || d.name object_locked,
    decode(s.process, NULL, s.machine, s.machine || ' PID: ' ||  to_char(s.process)) "MACHINE/PID"
    from v$session s, dba_ddl_locks d
    where s.sid = d.session_id
    and d.type like '%/Procedure/%'
    and d.owner = upper('&objowner')
    and d.name = upper('&objname')
    /
    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."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sorry about that, another poster deleted the question I was answering at the same time you added yours.
    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."

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    And Yes, select queries do use rollback segments in order to get a consistent view of the data.
    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."

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    A select does not use RBS's, but it does use cursors.
    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Marist89,

    Do you mean that a SELECT selects from a RBS, but it does not place data into a RBS?

    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    A SELECT statement will use a rollback segment to get data that may have changed after the start of a transaction. Nothing gets WRITTEN to an RBS, but they are USED during a SELECT.
    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."

  8. #8
    Join Date
    Jan 2001
    Posts
    157
    I mean selecting from a table e.g the EMP table. Does that use rollback segments?

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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."

  10. #10
    Join Date
    Jul 2001
    Location
    Minneapolis
    Posts
    15
    Yes. For example.....

    If you issue the statement

    insert into EMP values...
    commit;

    Your friend issues

    update EMP set .....

    And then you do a select on EMP before he issues the commit, you will see your original values, not your friends. That's how rollback segments are used in a select statement.

    Hope that helps.

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