With an Oracle DataBase is it necessary to rebuild  files index from time to
time and how ?

Thank you

This was covered in this NG recently. Please browse through the threads...

Also, it's good to rebuild if your index has a high percentage of
deletions..depending on what book you read the percentage varies but I
recall seeing 10% and 20%.

To rebuild (the index in this example is  s_dept_id_pk), use

SQL> alter index s_dept_id_pk rebuild;

Index altered.

Steve

You must be connected as the user who owns the indexes, obviously. It takes
into account index 'height' and deleted rows as per others recommendations.

set serveroutput on
set pages 0
set feedback off
set echo off
DECLARE
        height          index_stats.height%TYPE;
        del_lf_rows_len index_stats.del_lf_rows_len%TYPE;
        lf_rows_len     index_stats.lf_rows_len%TYPE;
        del_perc        number;
        table_name      user_indexes.index_name%TYPE;
        sql_stmt        varchar2(100);
        dbname          varchar2(20);
        wday            varchar2(11);
Begin
        dbms_output.enable(50000);

        select name into dbname from v$database;
        select to_char(sysdate,'dd-Mon-yyyy') into wday from dual;
        dbms_output.put_line('Index Analyze Report for '||dbname||' on
'||wday);
        dbms_output.put_line('*****');
        dbms_output.put_line('Analyzing indexes for '||user);
        dbms_output.put_line('*****');

        for t in (select index_name from user_indexes) loop

                sql_stmt := 'analyze index '||t.index_name||' validate
structure';
                execute immediate sql_stmt ;

                execute immediate 'select height, del_lf_rows_len,
lf_rows_len from index_stats'
                        into height, del_lf_rows_len, lf_rows_len;

                if (height > 3) then
                        dbms_output.put_line(t.index_name||' needs rebuilt
(height > 3)');
                end if;
                if (lf_rows_len != 0 and del_lf_rows_len != 0) then
                        del_perc := (del_lf_rows_len/lf_rows_len)*100;
                        if (del_perc > 20) then
                                dbms_output.put_line(t.index_name
                                        ||' needs rebuilt - high no. of
deleted entries ('
                                        ||round(del_perc,2)||'%)' );
                        end if;
                end if;
        end loop;
End;
/

CREATE OR REPLACE PROCEDURE BO.CREER_F0902
AS
   i number;
   BEGIN
      i:=i+1;
execute immediate 'Create table bo.f0902 as select * from bo.v$f0902';
execute immediate 'drop table bo.f0902';

 EXCEPTION
 When NO_DATA_FOUND THEN
  dbms_output.put_line('toto');
END;
/

Thank's

The reason it doesn't work is you don't have privileges (execute permission)
on dbms_output, so connect as SYS, and 'grant execute on dbms_output to
yourusername'. Then reconnect and try again.