Forum Stats

  • 3,759,951 Users
  • 2,251,621 Discussions
  • 7,870,879 Comments

Discussions

READ UNCOMMITTED or NOLOCK on Oracle

627145
627145 Member Posts: 3
edited Dec 15, 2009 5:06PM in General Database Discussions
Hi,

In SQL Server, I can do

1. select * from tb_name (nolock)
or
2. SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

To avoid blocking due to lengthy select operation.

Do we have something like this in Oracle?

Thanks
Claire
«1

Comments

  • ben23
    ben23 Member Posts: 484
    Nope, no dirty reads in Oracle.

    No blocking in Oracle either.

    But, ahem, your snapshot might get a bit too old.
  • sgalaxy
    sgalaxy Member Posts: 5,691 Bronze Trophy
    The Oracle Db allows :
    1) readers of the data not to be blocked by writers of the data
    2) writers of the data not to be blocked by readers of the data

    You can find info reading the following...
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#insertedID3

    Greetings...
    Sim
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    That's one of my favourite topics.
    In oracle select doesn't block anything except when we want it to. And Oracle the isolation level read committed is preserved by default that means as mentioned by Sim, in oracle, Readers don't wait for writes and writers don't wait for readers.
    Please read the link that is mentioned by Sim.And just a suggestion, if you are in Oracle world, think from oracle point of view. I meet lot of people every week who wants x feature implemented in database abc in Oracle.Well it may be there , may not be.
    Have fun with Oracle.
    Aman....
  • sgalaxy
    sgalaxy Member Posts: 5,691 Bronze Trophy
    Yes... exactly these characteristics permit Oracle DB to be the more scalable DB in the world....

    Greetings...
    Sim
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    100% and also no lock escalation as like other db's.
    Aman....
  • Basil
    Basil Member Posts: 4
    yes, we all know that Oracle has unlockable read from rollback segments using SCN, but as DBA I WANT to see uncommited data of some of my hanged applications to know what they are doing before they commit.

    And how can I do it?
  • 289595
    289595 Member Posts: 843
    You may want to read:

    http://laurentschneider.com/wordpress/2008/08/read-uncommitted.html
  • Basil
    Basil Member Posts: 4
    Not helpful...
    If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.
  • 289595
    289595 Member Posts: 843
    Actually, the link was to support that you won't be able to do so.....

    The last line in the link: 'But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

    '
  • Hans Forbrich
    Hans Forbrich Member Posts: 11,543
    Basil wrote:
    Not helpful...
    If I had a chance to interfere with first session and suspend transaction in it, I also could see it's data in it. But as I stated above, session is hanged in application server and I need to see its data. Looks like Oracle dont bother such administrative monitoring things.
    You are right. Oracle does not bother with such things, as they are rarely needed in Oracle in a well designed application.

    Unfortunately many developers are not aware of things like DBMS_APPLICATION_INFO which would help weird monitor situations.
This discussion has been closed.