Why does index creation waiting for 'library cache lock'?

From: Jay <jaysingh1_at_optonline.net>
Date: Sun, 29 Feb 2004 15:57:02 -0500
Message-id: <ECEFIFEIGAOACAHHGDPDAEHACNAA.jaysingh1@optonline.net>

Dear All,

We were trying to create an index on one of the table in our Production system.

Background:

Version: 8.1.7.3,OPS
OS: Sun Solaris
Table Size : 300,000 rows,200MB size
Index Type: Normal,Single Column, Nullable,only 12,000 rows not null value

The table is not locked by anybody( Checked gv$locked_object). This index creation process should not take more than 5 min. But we waited for one hour, it just hangs. Also tried with NOLOGGING,PARALLEL DEGREE 5 but nothing clould help us.

Contacted oracle by providing ("alter session set events 'immediate trace name SYSTEMSTATE level 10';") trace dump and they said..

PROCESS 123 which is ABC application is holding ''library cache lock'. which is causing the
create index to hang in this situation.

1)Why does index creation waiting for 'library cache lock'?

But even after killing Process 123 , we could not succeed. What could be the problem?

We check alert log,gv$locked_object,etc...Also checked for block corruption by exporting the object,analyze validate structure,etc..

Any help would be really appreciated.

Thanks
Jay

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Sun Feb 29 2004 - 13:56:24 CST