monitor your oracle db session

There many popular reports or select statements to rech and understand what happen in the db. I usually use the built in function of PL/SQL developer application.

I collected what kind of privileges you have to ask to use it. because very often you dont have DBA rights. Just ask SELECT for these:

Main window: v_$session
Cursors tab: v_$open_cursor
SQL Text tab: v_$sqltext_with_newlines
Statistics tab: v_$sesstat, 
                      v_$statname
Locks tab: v_$lock

good to have also:

v$sql

v$transaction

v$mystat

v$sgainfo

Deadlock during an update

You thought before if you insert or update a row in a table, you do it parallel on different records.

My scenario is now to run a data transfer in PL/SQL and  keep running the Java - hibernate application. It is a paralel running, the data transfer is running on old records, and the application users create new content in the database, and modify only them.

In many cases we receive an exception on both part:
"SQLException: ORA-00060: deadlock detected while waiting for resource".

In fact the Oracle RDBMS use a lock in table level also for update and insert statements. You can read about here.

You can find these table lock modes in the session also, there are some exclusive and sharing modes.

The table indexes are coming to this topic, maybe for first it is strange.
"Indexing the foreign keys in child tables provides the following benefits:

• Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.

"

If you have no index on foreign columns, the db will use full table lock instead of row lock.

"Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. In heap-organized tables, locking behavior depends on the indexing of foreign key columns. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason, Oracle recommends indexing foreign keys in most cases except when the matching unique or primary key is never updated or deleted."