RE: Rebuild Indexes

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Fri, 13 Feb 2004 10:14:14 +0100
Message-Id: <20040213091415.A0BB810E2B3@smtp-01.servidoresdns.net>

Try this.
This do not use a cursor so you can stop it when you what. Take care in production because VALIDATE STRUCTURE do some tipe of lock.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
+++


-- Juan Miranda    Serm=E1tica	06/AGO/2002

Utiliza VALIDATE STRUCTURE -> OJO en producci=F3n. Puede generar = errores ORA-00054.
OJO nolog -> NO usar con stand by

set serveroutput on size 1000000
set pagesize 0
set feedback off
set echo off
set trimspool on

spool c:\reb_index1.sql

DECLARE

        dbname          varchar2(20);
        wday            varchar2(11);

BEGIN

   dbms_output.put_line('set echo off');
   dbms_output.put_line('set feedback off');
   dbms_output.put_line('set head off');

   dbms_output.put_line('spool c:\reb_index2.sql');
   dbms_output.put_line('prompt set feedback on');

dbms_output.put_line('prompt set echo on'); dbms_output.put_line('prompt spool c:\reb_index2.log');

FOR t IN (select owner, index_name from dba_indexes where owner not = in
('SYS','SYSTEM') order by owner,index_name) LOOP

dbms_output.put_line('prompt --Analizando '||t.owner||'.'||t.index_name);

dbms_output.put_line('Analyze index '||t.owner||'.'||t.index_name||' validate structure;');

dbms_output.put_line('select ' || '''' || 'Alter index ' || t.owner = ||
'.' || t.index_name || ' rebuild online;' || ''''|| ' from index_stats = where
(height > 2) or
(10<=3Ddecode(lf_rows_len,0,NULL,((del_lf_rows_len/lf_rows_len)*100)));')= ;

END LOOP;

  dbms_output.put_line('prompt spool off');
  dbms_output.put_line('spool off');
  dbms_output.put_line('@c:\reb_index2.sql');

END;
/
spool off

@c:\reb_index1.sql

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

+++

-----Mensaje original-----
De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] = En
nombre de vidya kalyanaraman
Enviado el: viernes, 13 de febrero de 2004 9:46 Para: oracle-l_at_freelists.org
Asunto: Rebuild Indexes=20

Hi
It may be a silly thing to ask, but I am stuck right now.=20 I have been given a task to find out the indexes which need to be=20 rebuilt. There are around 3000 Indexes. I know I can run the following =

command
"analyze index <Index> VALIDATE STRUCTURE " =20

for a single index and then find the rows from index_state based on=20 del_lf_rows_len/lf_rows_len > 20%. =20
Does anyone have a script for dynamically finding out the indexes that=20 are the candidates for rebuilding? How do you normally handle=20 situations like this?

TIA
Vidya

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
-----------------------------------------------------------------

----------------------------------------------------------------
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 Fri Feb 13 2004 - 03:14:14 CST