Locking on bitmap index

[pranavgovind]

All, I have a question in bitmap index. It would be great if any one could clarify this... I have two scenario's. In first scenario, it is locking. But the second scenario, it is not locking. I am not sure why it is...

Scenario 1
==========

Session 1
=========

SQL> create table t_bitmap(no number,name varchar2(10),status char(1));

Table created.

SQL> ed
Wrote file afiedt.buf

1* create bitmap index biidx on t_bitmap(status)
SQL> /

Index created.

SQL>

SQL> insert into t_bitmap values(1,'Scott','M');

1 row created.

SQL>


Session 2
=========
SQL> insert into t_bitmap values(3,'John','F');


This session is hanging.....



================================================================================


Scenario 2
==========

Session 1
=========
SQL> create table t_bitmap(no number,name varchar2(10),status char(1));

Table created.

SQL> declare
2 begin
3 for i in 1.. 100 loop
4 insert into t_bitmap values(1,'Scott','F');
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> declare
2 begin
3 for i in 1.. 100 loop
4 insert into t_bitmap values(1,'Scott','M');
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create bitmap index biidx on t_bitmap(status);

Index created.

SQL> insert into t_bitmap values(9999,'Scott','M');

1 row created.

SQL>

Session 2
==========
SQL> insert into t_bitmap values(9999,'Scott','F');

1 row created.

SQL>

Here the session is not hanging...

[malay_biswal]

May be the session is hanging due to some other reason. check alert log for any error..

I tried in my D/B. Did not get any hanging session..

[pranavgovind]

Malay, I checked the alert log. I don't see any information. Are you sure that, it is working in your DB? I checked the same in different database. It is hanging...

Here is my db version.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

May be the session is hanging due to some other reason. check alert log for any error..

I tried in my D/B. Did not get any hanging session..

[davey23uk]

whats the sessions waiting on?

[pranavgovind]

Dav, I believe, you are asking which session is waiting.. The session 2 is waiting in Scenario I. I am sorry if i did not understand your question.

[davey23uk]

and what is it waiting on?

[abhaysk]

check v$session_wait..

[pranavgovind]

Here is the entry in v$session_wait. But i am not able to understand the reason for waiting...

SID    SEQ#    EVENT    P1TEXT    P1    P1RAW    P2TEXT    P2    P2RAW    P3TEXT    P3    P3RAW    WAIT_TIME    SECONDS_IN_WAIT    STATE
19    80    enqueue    name|mode    1415053316    54580004    id1    655392    000A0020    id2    4,522    000011AA    0    0    WAITING 

[davey23uk]

is your dba not around to help you know what an enqueue is?

[pranavgovind]

Thanks Dav. I will take care of it.

is your dba not around to help you know what an enqueue is?