Printable View
Hey,
This is urgent. One of my table is locked by something and I can't update records in it, it just hangs up on me but I can insert a new record though. Can you tell me if there is a way to find out if my table is locked? I have used V$locked_object command. I don't know much more. A reply would be appriciated. Thanks.
Also, how to find what is locking my table?
1. start your update
2. in another window, select * from dba_waiters. This will give you the sid of the blocker.
dba_waiter does not exist in sql plus.
I am using 8i by the way. I restarted the server but can you please explain why dba_waiters object does not exist? I'll appreciate it. Thanks.
as sys, run ?/rdbms/admin/catblock.sql to create
You have some concepts problem, what do you mean a view doesnt exist in sqlplus? sqlplus is not the database, it's a tool
Quote:
Originally posted by see_one
Hey,
This is urgent. One of my table is locked by something and I can't update records in it, it just hangs up on me but I can insert a new record though. Can you tell me if there is a way to find out if my table is locked? I have used V$locked_object command. I don't know much more. A reply would be appriciated. Thanks.
Also, how to find what is locking my table?
try use the v$LOCKED_OBJECT, it will also show who the blocker is:
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",
o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc
/
start RTM - Concepts
you can use following query
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id
Don't forget the utllockt script in $ORACLE_HOME/rdbms/admin. Running this gives you a nice tree-like report of who's waiting and who's blocking.
Actually, I was in a hurry. I meant the object does not exist in the database when I desc it in SQL Plus. Thanks a lot for all of your help and responses I am greatful for such a great repsonse. I am very new to Oracle DBA stuff. I would like to ask you guys for another favor. I have searched books on the same matter, such as oracle tuning and dynamic tables and views in oracle. Would you guys recommend a good book which has good reviews for someone like me who wants to delve into V$ views? Thank again.
V$ views mean nothing until you understand what is going on. Reading most books out there will lead you down the path of measuring by ratios and counts and when you think you know something we'll blow you out of the water. I suggest reading the Concepts manual twice before even thinking about the v$ views.
Thanks for your advice. I am actually reading a lot and trying to get the concepts straight on Oracle and the nuts and bolts of oracle. Before I go into the details of that I have another problem that I am facing.
Yesterday, my table got locked up. And I was trying to find:
1. what table was locked?
2. what locked it up?
when I restarted my server and rollback the database before the lock up actaully happened. When it restarted, it showed me the BEFORE Trigger that caused the lockup on my SHIPMENT TABLE.
This BEFORE trigger which updates another table in our database. Btw, we are using Oracle forms6i for the front-end.
---------------------------------------------------------------------
declare
USER1 VARCHAR2(30);
TQAP_CNT NUMBER := 0;
HOLD_CHANGE VARCHAR2(1000) := null;
begin
SELECT USER INTO USER1 FROM DUAL;
SELECT COUNT(*) INTO TQAP_CNT FROM TQAP_SCORES WHERE REG_NUM = :NEW.REG_NUM;
IF :NEW.SHIPMENT_CODE IN ('MM','FM','RN') AND
:OLD.ACT_DELIVERY_DATE IS NULL AND :NEW.ACT_DELIVERY_DATE IS NOT NULL AND
TQAP_CNT = 0
THEN
INSERT INTO TQAP_SCORES
(REG_NUM,
GBLOC_SCORED,
PRIORITY,
RECEIPT,
SHIPMENT_STATUS,
ORIGINAL_1840)
VALUES
(:NEW.REG_NUM,
:NEW.ORIGIN_GBLOC,
'N', 'N', 'A', 'X');
END IF;
end;
---------------------------------------------------------------------
The thing, that I want to know now is what application that the user was using that locked my table up. I know the sid and session but is it possible to find out what application that is doing this lock up on our table. In here, users have different application screens that they are using but we don't know what screen or the application that is causing the lockup when we are trying to UPDATE the SHIPMENT Table but no problem on INSERT though. I am not sure of you would be able to come up with the solution or if there is even a way to find what I am wanting. Anyways, I just thought there might be somoene out there who can shed some light. Thanks again to all of you.
It's like deja vu all over again. When you are experiencing the locking, look at dba_waiters and dba_blockers.
It's okay now.
What's the error in your alert.log? Did everything create successfully? Did you run them as sys?
I checked the script and I realized that it was creating the synonyms based on tables that does not exist in our database. I think if i need to create the views I need to make sure that all the tables that the views require exists on the database. I think to do something like this, is a little bit risky esp. messing around with databases. I simply want to learn more and test those things on my own machine.
But, I still want to find out the solution for the problem that our aplication my be causing to the table's trigger. Thanks.