Read consistency? - errr.........
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Read consistency? - errr.........

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Read consistency? - errr.........

    You start a session as schema_owner_A and start a long-running query against Table A.

    In the meantime, you start a second session as schema_owner_A and truncate Table A.

    What happens to the first session??

    I'm having interesting results with this, so am open to discussion.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    It will read all the records if the extents are not overwritten by oracle..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You are pretty likely to get an error raised by the selecting query, I'd say. Something funky like ORA-08103: object no longer exists
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Something funky like ORA-08103: object no longer exists
    wouldnt this be candidate if object was dropped?..

    you may get errors like "invalid rowid"
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    wouldnt this be candidate if object was dropped?..

    you may get errors like "invalid rowid"
    Not everything in life is logical ... http://metalink.oracle.com/metalink/...&p_id=170185.1
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Not everything in life is logical ... http://metalink.oracle.com/metalink/...&p_id=170185.1
    oh ya i have seen that.. but its history ( 8.x ).. i assumed 9.x..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's in 10g also .. I don't think this is a bug, it's a "feature". There's certainly no guarantee that Oracle can get a consistent view of a table when it is deleted part way through the scan. It's logical that there would be an error of some type. As the note says, if you want the select to work, then use delete .
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Its also logical that there can be no error also.. providied all the extents which are marked free after truncate are not over written.. IMHO even after truncate, data is returned with no errors.. and if lets assume is a table scan operation u can see some thing like "Stale Object" getting scanned after truncate (in place of operation table scan)..


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Not only do I think you will get a "ORA-08103: object no longer exists" (because slimdave is a pretty smart guy), I know you will (because I tested it).
    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
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Since a truncate doesn't create redo, how is Oracle going to maintain a read consistant view?

    I agree with Jeff who has already found the answer.

    And of course SlimDave

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