inserts cause oracle to hang?

[mbames]

I am new to Oracle, so maybe I am just done something silly, but here goes:
Installed 9i,
created a user, schema, quota and then a basic table -
ID (number)(unique), Name (vchar2).

I then inserted 2 records when logged into sqlplus as myself. Then I created an OBDC link to the database, and opened a link to the table in Access. Access did not show me any records, but I added 2 more.

Doing a select * from <myschema>.<mytable> showed all four records.

I then tried adding a new record with a conflicting ID and got an error as expected. I then opened up a new sqlplus (as sys) window and added 2 more records. This sys window only seemed to see the records I added when logged in as sys, although my initial sqlplus window could see all 6 records. I thrn tried addeding a conflicting ID in the sys window and that caused the window to lock up. I then repeated the process in the other windows and they all locked up. Even opening a brand new window resulted in the window locking up.

Any ideas / suggestions as to what I am doing? I have previously used Access and SQL server in the past, but it seems that Oracle is much more of a beast.... btw: if I can get this to work I shal be back asking about storing XML and why it appears to be slow!

Cheers,
Matt

 

[Scunningham99]

on an insert statement this creates an explicit DML lock on the table.

to see if you open a new session and select * from v$blockers and v$waiters to see the blocking session.

You have choice of either killing off the session with alter system kill session 'sid,serial#'
or issue a commit in the sessions window that is hung which in turn will release the lock on the row.

HTH

Sy UK

 

[nbateman]

Matt,

Probably not the case, but some of what you describe sounds like the inserts through SQLPlus were simply not committed. Access will usually commit automatically of course, but not SQL*Plus - you have to &quot;commit;&quot; explicitly.

Nick.

 

[sem]

Did you COMMIT your changes? Are you sure you're selecting from the same table you're inserting into?

Regards, Dima

 

[mbames]

Thanks.. I killed off the sessions and then started using the commit; command and all seems to be ok.

Cheers,
Matt