-
Problems deleting the table
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Last edited by ggnanaraj; 03-18-2003 at 07:48 AM.
-
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.
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....
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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?
-
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.
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.
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|