LISTING 7: This script, orderAnalObjs.crt, sorts the objects in the sys_analyzed_objects table.
/*
   Filename : orderAnalObjs.crt
   Author   : Julius J.Y.  Lin 	11/22/96
   Purpose  : create sorted object in sys_analyzed_objects for easy look up
*/ 

set serveroutput on size 20000

DECLARE
   PROCEDURE insert_sorted_Objs( p_schema varchar2 ) AS
      v_cur_tbl	   varchar2(30);
      cur_ndx	   varchar2(30);
      v_upc_owner    	   varchar2(10) := upper( p_schema ) ;
 
      cursor tbl_cs is
           select  	table_name
           from    	dba_tables
           where   	owner = v_upc_owner
           order by 1
           ;
/*
      cursor ndx_cs is
           select  	index_name
           from    	dba_indexes
           where   	owner = v_upc_owner
           and     	table_name = v_cur_tbl            -- v_cur_tbl is a variable
           order by 1
           ;
*/
   BEGIN
      open  tbl_cs ;
      fetch tbl_cs into v_cur_tbl ;
      WHILE tbl_cs%found LOOP
          update_analyzed_obj( p_schema,v_cur_tbl,'TABLE', NULL, NULL, NULL, 'INIT' );
          update_analyzed_obj( p_schema,v_cur_tbl||'*NDX', 'index', NULL, NULL, NULL, 'INIT' );
/*   
          open ndx_cs ;
          fetch ndx_cs into cur_ndx ;
          WHILE ndx_cs%found LOOP
               update_analyzed_obj( p_schema, cur_ndx, 'INDEX', NULL,NULL,NULL);
               fetch ndx_cs into cur_ndx ;
          END LOOP ;
          close ndx_cs ;
*/   
          fetch tbl_cs into v_cur_tbl ;
      END LOOP ;   -- tbl_cs%found loop
   
      close tbl_cs ;
      commit ; 
   EXCEPTION
      when others then
         dbms_output.put_line( 'Init Anal Obj '||substr( SQLERRM, 1, 150)) ;
   
   END ;
-- ==================================================================
begin
   insert_sorted_Objs( '&majorUser') ; 
   UPDATE SYS_ANALYZED_OBJECTS SET analyzed_count = NULL ;
   commit ;
end ;
/