dbms_stats.gather_schema_stats(user,cascade=>true,stattab=>'STATS',options=>'gather stale');

     -- -------------------------------------------------------------
 
     --
 
     -- ForEachObj.sql
 
     --
 
     -- Description:
 
     -- Performs any PL/SQL command with desired objects (from all_objects)
 
     --
 
     -- Modification:
 
     -- 11-APR-2001
 
     --
 
     -- Notes:
 
     -- Must have "direct" privelege: select on "all_objects".
 
     -- -------------------------------------------------------------
 
    PROCEDURE ForEachObj (
    	 cmd1		in	varchar2
    	,whereand	in	varchar2	default ''
        ,owner		in	varchar2	default ''
    	,repl_chr	in	varchar2	default '?'
    	,cmd2		in	varchar2	default ''
    	,cmd3		in	varchar2	default ''
    	,precmd		in	varchar2	default ''
    	,postcmd	in	varchar2	default ''
    	,debug_flg	in	integer		default  0 
    )
    AS
    	TYPE	cur_typ IS REF CURSOR;
    	cv	cur_typ;
    	ownr	varchar2( 50 );
    	cv_sql	varchar2( 4000 );
    	obj_ow	varchar2( 50 );
    	obj_nm	varchar2( 50 );
    	obj_tp	varchar2( 50 );
    	obj_id	varchar2( 50 );
    	sql_cmd	varchar2( 4000 );
    	cnt	integer		:=	 0 ;
    	repl_o	varchar2( 50 )	:=	repl_chr || 'o';	 -- Owner
 
    	repl_n	varchar2( 50 )	:=	repl_chr || 'n';	 -- Name
 
    	repl_t	varchar2( 50 )	:=	repl_chr || 't';	 -- Type
 
    	repl_i	varchar2( 50 )	:=	repl_chr || 'i';	 -- Object ID
 
	err_cnt	number		default  0 ;

    BEGIN

       IF rtrim(owner) IS NOT NULL THEN
          ownr := owner;
       ELSE
          ownr := USER;
       END IF;

       cv_sql := 'select owner, object_name, object_type, object_id from all_objects where owner like upper(''' || ownr || ''') ';

       IF trim(whereand) IS NOT NULL THEN
          cv_sql := cv_sql || trim(whereand);
       END IF;

       DBMS_OUTPUT.DISABLE;
       DBMS_OUTPUT.ENABLE( 1000000 );

       IF (debug_flg <>  0 ) THEN
          p(cv_sql);
       END IF;

       IF rtrim(precmd) IS NOT NULL THEN
          IF (debug_flg <>  0 ) THEN
             p(precmd);
          END IF;
          EXECUTE IMMEDIATE (trim(precmd));
       END IF;

       OPEN cv FOR cv_sql;  -- USING owner;
 
       LOOP
          FETCH cv INTO obj_ow, obj_nm, obj_tp, obj_id;
          EXIT WHEN cv%NOTFOUND;

          sql_cmd := cmd1 || cmd2 || cmd3;
          sql_cmd := REPLACE(sql_cmd,repl_o,obj_ow);
          sql_cmd := REPLACE(sql_cmd,repl_n,obj_nm);
          sql_cmd := REPLACE(sql_cmd,repl_t,obj_tp);
          sql_cmd := REPLACE(sql_cmd,repl_i,obj_id);
       
          IF (debug_flg <>  0 ) THEN
              --p(obj_ow||%af_src_str_15||obj_nm||%af_src_str_16||obj_tp||%af_src_str_17||obj_id);
 
             p(sql_cmd);
          END IF;
          BEGIN
             EXECUTE IMMEDIATE (sql_cmd);
          EXCEPTION
             WHEN OTHERS THEN
             LogErr(SQLCODE, SQLERRM, 
                    'NH_ADM.ForEachObj (sql_cmd=>''' || sql_cmd || ''', whereand=>'''
                    || whereand || ''', owner=>''' || ownr || ''')');
             err_cnt := err_cnt +  1 ;
          END;
          cnt := cnt +  1 ;

       END LOOP;
       CLOSE cv;

       IF rtrim(postcmd) IS NOT NULL THEN
          IF (debug_flg <>  0 ) THEN
             p(postcmd);
          END IF;
          EXECUTE IMMEDIATE (trim(postcmd));
       END IF;

       p('---');
       p(cnt || ' row(s) affected...');

       IF ( err_cnt <>  0  ) THEN
          p(' ');
          p('*********');
          p('ATTENTION: ' || to_char(err_cnt) || ' error(s) were found !!!' || CHR( 10 ) || 'Check "NH_ADM_ERROR" table for details...');
       END IF;

    EXCEPTION
       WHEN OTHERS THEN
       LogErr(SQLCODE, SQLERRM, 
              'NH_ADM.ForEachObj (''' || sql_cmd || ''','''
              || whereand || ''',''' || ownr || ''')');
       err_cnt := err_cnt +  1 ;
       IF ( err_cnt <>  0  ) THEN
          p(to_char(err_cnt) || ' error(s) were found !!!' || CHR( 10 ) || 'Check "NH_ADM_ERROR" table for details...');
       END IF;
    END ForEachObj;

declare jobno NUMBER;
begin
  DBMS_JOB.SUBMIT ( :jobno
			,'nh_adm.ForEachObj(''alter index ?o.?n rebuild'',''and object_type=''''INDEX'''' and not exists(select null from user_indexes where index_name=object_name and index_type like ''''IOT%'''')'');'
			,TRUNC (SYSDATE) +  25 / 24 , 'TRUNC (SYSDATE) + 25/24'
  );
  p(jobno);
  commit;
end;
/