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