"1. How do you know What are the objects have locks??
Solution:
select * from v$lock where block<>0;
if block=0 then no locks if block=1 then there is locks"
Unfortunately the V$LOCK view contains no object information whatsoever. Also, simply because BLOCK is 0 does not mean there are no locks present, simply that none of the existing locks are blocking anyone. Executing the query supplied against a 10.2.0.3 database provides the following output:
SQL> select * From v$lock where block <> 0;
no rows selected
SQL>
which, according to the 'information' supplied says no locks exist in the database. Nothing could be further from the truth; querying V$LOCK again, absent the supplied WHERE clause, reveals:
SQL> select * From v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
070000003B8AF020 070000003B8AF040 165 XR 4 0 1 0 130060 0
070000003B8AF0B8 070000003B8AF0D8 165 CF 0 0 2 0 130060 0
070000003B8AF1E8 070000003B8AF208 165 RS 25 1 2 0 130055 0
070000003B8AF318 070000003B8AF338 166 RT 1 0 6 0 130055 0
070000003B8AF4E0 070000003B8AF500 164 TS 3 1 3 0 130049 0
070000003B8AF6A8 070000003B8AF6C8 167 MR 20 0 4 0 130052 0
070000003B8AF740 070000003B8AF760 167 MR 1 0 4 0 130052 0
070000003B8AF7D8 070000003B8AF7F8 167 MR 2 0 4 0 130052 0
070000003B8AF870 070000003B8AF890 167 MR 3 0 4 0 130052 0
070000003B8AF908 070000003B8AF928 167 MR 4 0 4 0 130052 0
070000003B8AF9B8 070000003B8AF9D8 167 MR 5 0 4 0 130052 0
070000003B8AFA50 070000003B8AFA70 167 MR 6 0 4 0 130052 0
070000003B8AFAE8 070000003B8AFB08 167 MR 7 0 4 0 130052 0
070000003B8AFB80 070000003B8AFBA0 167 MR 8 0 4 0 130052 0
070000003B8AFC18 070000003B8AFC38 167 MR 9 0 4 0 130052 0
070000003B8AFCB0 070000003B8AFCD0 167 MR 10 0 4 0 130052 0
070000003B8AFD48 070000003B8AFD68 167 MR 11 0 4 0 130052 0
070000003B8AFDE0 070000003B8AFE00 167 MR 12 0 4 0 130052 0
070000003B8AFE78 070000003B8AFE98 167 MR 13 0 4 0 130052 0
070000003B8AFF10 070000003B8AFF30 167 MR 14 0 4 0 130052 0
070000003B8AFFA8 070000003B8AFFC8 167 MR 15 0 4 0 130052 0
070000003B8B0040 070000003B8B0060 167 MR 16 0 4 0 130052 0
070000003B8B00D8 070000003B8B00F8 167 MR 17 0 4 0 130052 0
070000003B8B0170 070000003B8B0190 167 MR 18 0 4 0 130052 0
070000003B8B0208 070000003B8B0228 167 MR 19 0 4 0 130052 0
070000003B8B02A0 070000003B8B02C0 167 MR 21 0 4 0 130052 0
070000003B8B0350 070000003B8B0370 167 MR 22 0 4 0 130052 0
070000003B8B03E8 070000003B8B0408 167 MR 23 0 4 0 130052 0
070000003B8B0480 070000003B8B04A0 167 MR 24 0 4 0 130052 0
070000003B8B0518 070000003B8B0538 167 MR 25 0 4 0 130052 0
070000003B8B05B0 070000003B8B05D0 167 MR 26 0 4 0 130052 0
070000003B8B0648 070000003B8B0668 167 MR 27 0 4 0 130052 0
070000003B8B06E0 070000003B8B0700 167 MR 28 0 4 0 130052 0
070000003B8B0778 070000003B8B0798 167 MR 29 0 4 0 130052 0
070000003B8B0810 070000003B8B0830 167 MR 201 0 4 0 130052 0
35 rows selected.
SQL>
Gee, there are 35 locks existing in the database; of course none of which are blocking anyone (30 of the locks are media recovery locks with the remaining 5 locks identified as follows: CF -- Control file enqueue lock, RS -- row shared lock, RT -- Redo thread enqueue, TS -- Temporary segment enqueue lock, XR -- Forced logging enqueue due to checkpoints, I suspect). Which doesn't mean they don't exist, just that no one is waiting on one or more of them to be released so that the transaction in waiting can complete. [The MR locks are associated with the data files and temp file associated with the database in question, one per file.]Of course this was not enough misinformation to provide the user community so this poor soul offers this nugget of wisdom:
"2. How do you know locked tables? and how do you remove locks?
Solution:
1. select a.object_id, a.session_id, substr(b.object_name, 1, 40)from v$locked_object a,dba_objects bwhere a.object_id = b.object_idorder by b.object_name ;
2. select sid, serial#, command, taddr from v$session where sid=
3. alter system kill session '';"
My, what wonderful and considerate advice: just kill whatever session is blocking someone else, whether or not that session has completed its task and without any notification to the soon-to-be affected user. To act in such an irrational and arbitrary manner is wrong, to say the least. Yes, there MAY be situations where such action is warranted but, invariably, such action follows user complaints and/or physical problems not resolvable in any other way. A power outage (no matter how small) may render users unable to re-connect as the prior sessions still exist in the database. [This would usually be an application issue, although if resource profiles are in use this could generate an Oracle error.] Such is one valid case for simply killing sessions to free resources; remember, though, that a complaint was lodged regarding the inability to perform necessary work and that notice was given regarding said act. Another might be that an application, due to network or client resource problems, appeared to 'hang' while processing a transaction and the end-user simply clicked on the red 'X' in the upper right hand corner of the window, killing the local process but leaving the database session still in existence. Killing the offending session in such a scenario would be a valid course of action, again preceded by notification from the user community. But, to kill sessions simply because they lock an object or objects (for even the smallest length of time) that YOU want to access is irresponsible.
The Internet is an interesting place, full of wit, wisdom, humour and, unfortunately, plenty of mis-information. Myths abound, and half-truths become gospel truths simply because they are found there. Such could be the fate of these examples of 'a little knowledge can be a dangerous thing.' And, truly, the latter of the two examples is a dangerous piece of 'advice', based solely upon the author's ill-conceived notion of locks and locking in an Oracle database. Of course, if your goal is to madden the user community at large and make it virtually impossible to perform any meaningful work then, by all means, follow his recommendation. Of course if you do then don't be surprised when a 'pink slip' from HR lands upon your desk, as those who blindly follow such instructions invariably find themselves standing in the unemployment line.
0 comments:
Post a Comment