one table two users showing different records

[lalit khera]

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 Vashko]

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]

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 Vashko]

You should be able to issue the following command as a SYS user:
alter user system identified by whatever_password_you_like;
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.