expert one on one Oracle_note

1.
To get a repeatable read in Oracle, set the isolation level to serializable instead of using select for update
which you only do when you want  to physically serialize access to data.

2.
If all the statements execute very rapidly, then MTS works well. Or other user will hang
select * from v$session where  type = 'USER' and username='ACNDEV'

3.
Oracle locks data at the row level on modification only. There's is no lock escalation to a block or table level
Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.
A writer of data does not block a reader of data. (different from almost every other database, where reads are blocked by writes)
A writer of data is blocked only when another writer of data has already locked the row it was going after.

4.
In Oracle, Null is neither equal to nore not equal to Null. In Sybase and SQL Serverr, Null equals null.

5.
While a stored procedure is executing, the procedure itself is locked in a mode that allows others to execute it, but will not permit another user to alter it in any way.

6.
If the parent table row is deleted (before commit), then the entire table will be locked (in the absence of an index)

7.
Select ... for update places the row exclusive lock and ROW SHARE TABLE lock (to prevent table structure modification by other user)

8.
B*Tree index is used mostly (one key to one row/range) while Bitmap index is used for low cardinality data (Y,N and null).