Oracle-L: RE: How do we know that an index need to be rebuilt.

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Mon, 22 Sep 2003 00:29:50 -0800
Message-ID: <F001.005D0A24.20030922002950@fatcity.com>

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<=decode(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

--_at_c:\reb_index1.sql

-----Mensaje original-----
De: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]En nombre de Veeraraju_Mareddi
Enviado el: lunes, 22 de septiembre de 2003 8:00 Para: Multiple recipients of list ORACLE-L Asunto: How do we know that an index need to be rebuilt.

Dear Friends,

Can somebody tell me how do we that an index needs to be rebuilt.. Different scenarios / any documents will be helpful.

Thanks in advance.
Rajuvera

This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Veeraraju_Mareddi
  INET: rajuveera_at_satyam.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Juan Miranda
  INET: j.miranda_at_sermatica.es

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Sep 22 2003 - 03:29:50 CDT