Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to reindex a index in a PL/SQL if the index is busy during the reindex process?

User_R5AHCSep 1 2015 — edited Sep 1 2015

Hi, Gentlemen,

I have created an script to rebuild the indexes for some specific tables:

DECLARE

c_schema dba_users.username%TYPE;

BEGIN

    dbms_output.put_line ('Sistema DYAD - Re-geração Preventiva de índices - Principais Tabelas');

    FOR c_schema IN (SELECT username FROM dba_users WHERE username IN ('DYAD_ADM'))

    LOOP

         dbms_output.put_line('------------------ Schema = ' || c_schema.username || ' ------------------');

         FOR dyad_tab IN (SELECT table_name FROM dba_tables WHERE owner=c_schema.username AND table_name IN ('ILOG', 'VINCULA', 'TABELA', 'SGUIAS', 'FATOR', 'PEDIDO', 'EVENTO'))

             LOOP

             dbms_output.put_line ('Tabela = ' || dyad_tab.table_name);

             FOR c_indx IN (SELECT owner, index_name, tablespace_name FROM dba_indexes

                            WHERE  owner=c_schema.username AND table_name=dyad_tab.table_name AND index_type != 'LOB' ORDER BY 1)

             LOOP

             EXECUTE IMMEDIATE 'ALTER INDEX ' || c_indx.owner || '.' || c_indx.index_name || ' REBUILD TABLESPACE ' || c_indx.tablespace_name;

             END LOOP;

             dbms_output.put_line ('-----------------------------------------------------------------');

         END LOOP;

    END LOOP;

END;

If the index at the moment is not busy, its 'EXECUTE' line runs fine. Therefore, if I get an error, like the showed below:

Relatório de erro:

ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou

ORA-06512: em line 16

00054. 00000 -  "resource busy and acquire with NOWAIT specified"

*Cause:    Resource interested is busy.

*Action:   Retry if necessary.

The index rebuild fails and I need to re-execute the whole script again for all the related indexes.

Is there any - in the PL/SQL script - to check this error moment and run again its 'EXECUTE ALTER INDEX' line command?
May be it is a rookie question, and I know I don´t have too much knowledge of PL/SQL programming. But I appreciate any suggestions you can give.

Thank you, in advance, for any comments/suggestions.

Luiz Cassio

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2015
Added on Sep 1 2015
5 comments
1,084 views