4 Replies Latest reply: Dec 15, 2010 7:41 AM by P.Forstmann RSS

    Isolation levels


      I have 2 short questions:

      1. I know Oracle has these 3 transaction isolation levels (a) Read commited, (b) Serializable (c) Read Only. But I have heard about a 4th one, called something like "Optimistic isolation level", I heard about it, and thought I would search it in oracle documentation on internet. But now I have searched a lot, almost a whole day, but I couldn't find any such thing. All I found was optimistic locking. Can anybody tell me what is meant by "Optimistic transaction isolation level" ??? and what it does?

      2. Can anybody tell me what is "Virtual Private Database" in Oracle, I searched on net and found some documentation, but they are very lengthy and are not very clear. So, Can anybody tell me in short words, what is this?

      Thanks in Advance.
        • 1. Re: Isolation levels
          read this...

          nice article from tom kyte http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:51192606320458


          • 2. Re: Isolation levels

            The SQL standard defines four levels of isolation in terms of the phenomena that a transaction running at a particular isolation level is permitted to experience

            -Read uncommitted
            -Read committed
            -Repeatable read

            see this link :)
            • 3. Re: Isolation levels
              Pavan Kumar

              1. - refer to doc http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm -very much clear
              2 - vpd demo - http://www.oracle.com/technetwork/database/security/index-088277.html - hope you understand

              - Pavan Kumar N
              • 4. Re: Isolation levels
                1. Neither SQL standard nor Oracle has an "optimistic transaction isolation level". There are only optimistic locking and pessimistic locking strategies.
                2. From 11.2 Concepts Guide:
                Virtual Private Database (VPD) enables you to enforce security at the row and column level. A security policy establishes methods for protecting a database from accidental or malicious destruction of data or damage to the database infrastructure.

                VPD is useful when security protections such as privileges and roles are not sufficiently fine-grained. For example, you can allow all users to access the employees table, but create security policies to restrict access to employees in the same department as the user.

                Essentially, the database adds a dynamic WHERE clause to a SQL statement issued against the table, view, or synonym to which an Oracle VPD security policy was applied. The WHERE clause allows only users whose credentials pass the security policy to access the protected data.