wood burning stoves 2.0*
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 3606
    
  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: 3606
    
  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.
 
Consider Paul's rocket mass heater.
 
subject: one table two users showing different records
 
Similar Threads
Loading same table row in two different sessions
Problem in Multiple Select of drop down
Instant Messaging using JMS with the help of JSP and Servlets
Deleting duplicate rows using sql query
Test duplication on db with exception...am I on the right track?