aspose file tools*
The moose likes Oracle/OAS and the fly likes one table two users showing different records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "one table two users showing different records" Watch "one table two users showing different records" New topic
Author

one table two users showing different records

lalit khera
Ranch Hand

Joined: Jun 06, 2012
Posts: 36
hi friends, i came to about an ambiguity in displaying the datase from a single table accessed by two different oracle users.
let me clear the things. i am using sql developer with 3 users at this moment.
user1-user1
user2-user2
user3-SYS

user1 creates one table and inserts 3 rows successfully. now when i write "select * from tablename". it shows me 3 rows.
now i have given privelege to user2 to insert the data into same tablename that is being created and maintained by user1.
user2 insert one more row in the same table. now when i write "select * from tablename". it shows me 4 rows.

Again when i move back to user1 and i write "select * from tablename". it shows me 3 rows.
Again when i move back to user2 and i write "select * from tablename". it shows me 4 rows.

IS there something that i am not aware of or it is a concept in oracle or i need to check for object privelegis.
is it user-specific??


waiting for the reply.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I suppose you haven't committed the last inserted row. Issue a commit there (either write and execute the command, or just press the button in the toolbar) and retry.

Some databases provide isolation levels that allow you to see uncommited changes. Oracle doesn't; uncommited changes are never visible across session there.

And, by the way, stop using the SYS account for anything. We already talked about that, I think. You can only hurt yourself by doing so.
lalit khera
Ranch Hand

Joined: Jun 06, 2012
Posts: 36
Martin Vajsar wrote:I suppose you haven't committed the last inserted row. Issue a commit there (either write and execute the command, or just press the button in the toolbar) and retry.

Some databases provide isolation levels that allow you to see uncommited changes. Oracle doesn't; uncommited changes are never visible across session there.

And, by the way, stop using the SYS account for anything. We already talked about that, I think. You can only hurt yourself by doing so.


thanks martin.
i am using SYS account only as sysdba.
Yes , i remember you told me to use SYSTEM for all the admin privileges, but may i know please what is the password for the SYSTEM account?
Regarding my query, is there any sort of object privileges that i can user so that changes made by one user, is going to reflect in the user account who is the owner of the object.
thanks
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

You should be able to issue the following command as a SYS user:

and then log in as the SYSTEM user using the password you assigned him.

And no, there is no way to view uncommitted changes made by another session in Oracle. This is not even a user-related thing. Log in twice as the same user, insert a new record in one session and query the table in the other. Until you commit the first session, the other session won't see the inserted row, or any other modifications made by the first session.

This is the correct, expected and desired behavior. Some databases allow you to read uncommited changes, but that is only a kludge which allows you to skip locks these databases use to ensure consistency. Oracle provides consistency by multiversioning. This let's you see the data in the database that existed (ie. were committed) when your query (or, sometimes, transaction), and this concept neither needs nor allows you to see uncommited changes. This is actually the great thing about multiversioning databases.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: one table two users showing different records