Re: INDEX CREATE

Hi,

  Oracle locks the table for the index build. The 'ORA-00054: resource busy and acquire with NOWAIT specified' is because another session is holding a lock on that table.

  Now, are you trying to create a second index with the same column list as the first index ? If yes, then this is not possible. Either drop the first index or change the column order for the second index.

  What is your PL/SQL block trying to accomplish ? Are you doing some testing ?

Regards,
Denny

In article <96936058_at_NEWS.SAIC.COM>,
  "Tay, Virginia" <htay_at_geocities.com> wrote:
> Hi...
>
> I have a question...and need you all help promptly....
>
> I have one PL/SQL script to run for a table. The table has an UNIQUE
INDEX
> (col1 + col2 + col3). Now I would like create an NON-UNIQUE INDEX
(col1 +
> col2 + col3) and I got the following error...
>
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> I need the NON-UNIQUE INDEX to run the PL/SQL more sufficient and
> faster.....But without the UNIQUE INDEX it is tremendously slow....Any
> thoughts to solve the problem??? The following is my PL/SQL script
>
> DECLARE
> CURSOR memory is
> select distinct
> col1,
> col2
> col3
> from
> my_table;
>
> --EXECUTABLE SECTION
> BEGIN
> FOR i in memory LOOP
> delete from my_table
> where col1 = i.col1
> and col2 = i.col2
> and col2 = i.col3;
>
> insert into my_table (col1,col2,col3)
> values (i.col1,i.col2,i.col3);
>
> END LOOP;
>
> COMMIT;
> END;
>
> /

--
Denny Koovakattu
denny_at_vitalsol.com
http://vitalsol.com/

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 15 1999 - 12:46:24 CDT