Printable View
Hi
I have a table name comments.
In this table there is a column of varchar2(1500).
When i try to delete the records in the table my system just gets hangs. Can anybody just tell what should i do to delete record from the table.
By the way it doesnot have lots of rows, only 10 rows.
Im using Oracle 9i version 9.1.
If u dont want data at all, then Truncate table...
it will be much much faster...i can tell few secs..
Abhay.
This may be a deadlock situation. When a DML transaction is not complete on the table by another session/user, it occurs.
Make sure that u are the only person working on the table.
Check this out...
http://technet.oracle.com/docs/produ...cnsis.htm#6037
HTH.
BTW its not called DEADLOCK situation...this sessions TRAN will jus be waiting on that TRAN to complete...can say this sessionis blocked by that....Quote:
Originally posted by ggnanaraj
This may be a deadlock situation. When a DML transaction is not complete on the table by another session/user, it occurs.
Make sure that u are the only person working on the table.
HTH.
Typical Deadlock situation goes something like this
TRAN1 needs to update / delete on 2 tables and does 1...say TableA..
TRAN2 also needs to update / delete on same 2 tables and does 1 say Table2....
Now Tran2 would like to lock tableA but is being locked by TRAN1 and inturn Tran1 wants to lock TableB but cannot do so as Tran2 has locked that table....
So both TRANs will be waiting for one another to complete which will never happen....and the situation is Deadlock..
Abhay.
The following simulates your environment:
SEssion 1:
insert into temp values('1');
commit;
Session 2:
insert into temp values('2');
SEssion 1:
delete from temp;
1 row deleted.
Session 2:
delete from temp;
(hangs)
SEssion 1:
commit;
Session 2:
(comes alive)
HTH.
10 rows is realy nothing at all -- a straight delete ought to perform for you. Maybe you have a deadl;ock, but probably not I would say as Oracle will kill off one of the sessions it a deadlock is detected.
How long does it hang for?
If I dont give a commit at the end of the above quote for session 1. It waits/hangs indefinitely in Session 2. So this is not a deadlock (which is detected by the database) but a lock.Quote:
Originally posted by ggnanaraj
The following simulates your environment:
SEssion 1:
insert into temp values('1');
commit;
Session 2:
insert into temp values('2');
SEssion 1:
delete from temp;
1 row deleted.
Session 2:
delete from temp;
(hangs)
SEssion 1:
commit;
Session 2:
(comes alive)
HTH.
HTH.
ya. I think the above example illustrates a LOCK and not a DEADLOCK.
I think a deadlock would be encountered when session A is waiting for session B to complete something while session B is waiting for session A to complete someting else.
Try killing all sessions except that of oracle
SELECT sid, serial# FROM v$session WHERE username IS NOT NULL
and ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
and then retry truncating the table.