Categories:
.NET (961)
C (387)
C++ (185)
CSS (84)
DBA (8)
General (31)
HTML (48)
Java (641)
JavaScript (220)
JSP (109)
JUnit (31)
MySQL (297)
Networking (10)
Oracle (562)
Perl (48)
Perl (9)
PHP (259)
PL/SQL (140)
RSS (51)
Software QA (28)
SQL Server (5)
Struts (20)
Unix (2)
Windows (3)
XHTML (199)
XML (59)
Other Resources:
How To View Existing Locks on the Database
How To View Existing Locks on the Database? - Oracle DBA FAQ - Understanding SQL Transaction Management
✍: FYIcenter.com
As can see from the pervious tutorial exercise, performance of the second session is greatly affected by the data lock created on the database. To maintain a good performance level for all sessions, you need to monitor the number of data locks on the database, and how long do they last.
Oracle maintains current existing data locks in a Dynamic Performance View called V$LOCK with columns like:
The following tutorial exercise shows you how to view existing locks on the database:
(session 1) SQL> connect HR/fyicenter SQL> UPDATE fyi_links SET url='centerfyi.com' WHERE id=110; 1 row updated. (session 2) SQL> connect HR/fyicenter SQL> INSERT INTO fyi_links (url, id) VALUES ('oracle.com', 112); 1 row created. SQL> UPDATE fyi_links SET notes='FYI Resource' WHERE id=110; (wait on lock at id=110)
Now keep those two sessions as is. You need to open a third window to connect to the database as SYSTEM to view all current locks:
(session 3) SQL> connect SYSTEM/password SQL> select sid, username from v$session 2 where username='HR'; SID USERNAME ---------- ------------------------------ 23 HR 39 HR SQL> SELECT sid, type, lmode, request, ctime, block FROM V$LOCK WHERE sid in (23, 39) ORDER BY ctime DESC; SID TY LMODE REQUEST CTIME BLOCK ---- -- ---------- ---------- ---------- ---------- 1 39 TX 6 0 84 1 2 39 TM 3 0 84 0 3 23 TM 3 0 27 0 4 23 TX 6 0 27 0 5 23 TX 0 6 18 0
You should read the output as:
2007-04-17, 5950👍, 0💬
Popular Posts:
Regarding C Coding Given: Line in file Contents 30 int * someIDs, theFirst, *r; 110 someIDs =GetSome...
.NET INTERVIEW QUESTIONS - What is COM ? Microsoft’s COM is a technology for component software deve...
Explain all parts of a deployment diagram? Package: It logically groups element of a UML model. Node...
How To Create an Array in PL/SQL? - Oracle DBA FAQ - Introduction to PL/SQL If you want create an ar...
Can you tell me how to check whether a linked list is circular? Create two pointers, and set both to...