insert hangs

[Naeem]

Hello All,

I do an insert in a table tab1 which has 2 unique keys
unique key 1 : col1, col2, col3
unique key 2 : col1, col2, col3, col4

Insert into tab1 (col1, col2, col3, col4) values (1,2,3,4);

first time it gave me an error ORA-02049

The application rolled back

Now if I re execute the query it hangs! there is no error message!

I thought there is a lock in the table row but there is no lock.


select a.sid, a.serial#,sql_id ,(case LOCKED_MODE when 0 then 'none'
when 1 then 'null'
when 2 then 'row-S'
when 3 then 'row-x'
when 4 then 'share'
when 5 then 'S/Row-X'
when 6 then 'exclusive'
end ) LOCKED_MODE
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id;

Any body has an idea what this is waiting for. Where can I find this?

Please help me....

[Naeem]

Hello All,

I do an insert in a table tab1 which has 2 unique keys
unique key 1 : col1, col2, col3
unique key 2 : col1, col2, col3, col4

Insert into tab1 (col1, col2, col3, col4) values (1,2,3,4);

first time it gave me an error ORA-02049

The application rolled back

Now if I re execute the query it hangs! there is no error message!

I thought there is a lock in the table row but there is no lock.


select a.sid, a.serial#,sql_id ,(case LOCKED_MODE when 0 then 'none'
when 1 then 'null'
when 2 then 'row-S'
when 3 then 'row-x'
when 4 then 'share'
when 5 then 'S/Row-X'
when 6 then 'exclusive'
end ) LOCKED_MODE
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id;

Any body has an idea what this is waiting for. Where can I find this?

Please help me....

I have posted this in development forum also cas I don't know in which categorie to add.

Sorry for my english...

[gandolf989]

From looking at the error message you are trying to lock something.

http://docs.oracle.com/cd/B28359_01/...htm#ADMIN12284

You should not want to lock anything. You can do an insert with an
append hint, but you should not need to lock a table to do an insert.
You might have an issue getting a lock if there are uncommitted rows
in that table. You should start by closing Toad if you use Toad. Toad
might have an uncommitted transaction that is preventing the insert.

[Naeem]

From looking at the error message you are trying to lock something.

http://docs.oracle.com/cd/B28359_01/...htm#ADMIN12284

You should not want to lock anything. You can do an insert with an
append hint, but you should not need to lock a table to do an insert.
You might have an issue getting a lock if there are uncommitted rows
in that table. You should start by closing Toad if you use Toad. Toad
might have an uncommitted transaction that is preventing the insert.

I am able to insert other records. It is only this record I am not able to insert!
I have a tranaction with status "Prepared" in the v$transcation table. Does this means anythig?

[gandolf989]

I am able to insert other records. It is only this record I am not able to insert!
I have a tranaction with status "Prepared" in the v$transcation table. Does this means anythig?

I'm not sure off hand what that means, but it seems like you have more than one session,
and you are stopping your self from inserting the records. Try committing or rolling back
all of your sessions.

[ora_venlo]

Did you check the alertlog file or trace files to see if there is any information?

[Naeem]

I have checked the alert log there is no ORA error, there is no rows in the v$lock, there are 2 rows in the v$transaction table, and there are 2 rows in the v$locked_objects! With these information I am not able to go further. If i try to drop the index in this table i am getting ora-00054 resource busy error!

[gandolf989]

I have checked the alert log there is no ORA error, there is no rows in the v$lock, there are 2 rows in the v$transaction table, and there are 2 rows in the v$locked_objects! With these information I am not able to go further. If i try to drop the index in this table i am getting ora-00054 resource busy error!

It still looks like you have an uncommitted transaction. Try this query to see who is locking the table.
You will need to format the output.

select s.sid,        s.serial#,   s.username,    s.machine, s.program,
       s.status,     s.lockwait,  t.used_ublk,   t.used_urec,
       t.start_time, o.owner,     o.object_name, o.object_type
  from v$transaction        t
 inner join v$session       s 
    on t.addr = s.taddr
 inner join V$LOCKED_OBJECT l
    on s.process = l.process
 inner join dba_objects     o
    on l.object_id = o.object_id
 order by 1,2,3;