how to fix this ? -- rebuild and analyze indexes ( dbms_index_utl package )

CREATE OR REPLACE PROCEDURE index_maintainance (
   table_name_h    VARCHAR2,
   schema_name_h   VARCHAR2 default 'SYS'
)
IS
   table_count_p          NUMBER          := 0;
 
   TYPE ind_tab IS TABLE OF all_indexes.index_name%TYPE;
 
   index_table            ind_tab;
   vjob_num1              job_num_tab     := job_num_tab ();
   vjob_num2              job_num_tab     := job_num_tab ();
   job_command            VARCHAR2 (200);
   n_jobs_running_count   NUMBER;
   job_list               VARCHAR2 (2000);
   break_signal           NUMBER          := 0;
BEGIN
-- CHECK IF THE TABLE EXISTS IN THE GIVEN SCHEMA
   SELECT COUNT (table_name)
     INTO table_count_p
     FROM all_tables
    WHERE table_name = UPPER (TRIM (table_name_h))
      AND owner = UPPER (TRIM (schema_name_h));
 
-- IF TABLE EXISTS THEN DO THE INDEX MAINTAINANCE JOB ON NOT VALID INDEXES ON THE SUPPLIED TABLE
   IF table_count_p = 1
   THEN
-- COLLECT THE INDEX NAMES ON THE SUPPLIED TABLES WHOSE STATUS IS NOT VALID
      SELECT index_name
      BULK COLLECT INTO index_table
        FROM all_indexes
       WHERE table_name = UPPER (TRIM (table_name_h))
         AND owner = UPPER (TRIM (schema_name_h));
 
    
 
      -- BUILD THE BACK GROUND INDEX REBUILD JOBS
      FOR i IN index_table.FIRST .. index_table.COUNT
      LOOP
         vjob_num1.EXTEND;
         job_command := 'ALTER INDEX ' || index_table (i) || ' REBUILD';
         DBMS_OUTPUT.put_line (job_command);
         DBMS_JOB.submit (job       => vjob_num1 (i),
                          what      =>    'EXECUTE IMMEDIATE '''
                                       || job_command
                                       || ''';'
                         );
      END LOOP;
 
      -- SUBMIT THE BUILT BACK GROUND JOBS -- THEY KEEP RUNNING IN THE BACKGROUND IN PARALLEL
      COMMIT;
 
      -- KEEP WAITING TILL ALL THE SUBMITTED BACKGROUND JOBS ARE DONE.
      LOOP
         dbms_output.put_line('wait loop');
         
         SELECT COUNT (job)
           INTO n_jobs_running_count
           FROM user_jobs
          WHERE job IN (SELECT column_value
                          FROM TABLE (CAST (vjob_num1 AS job_num_tab)));
 
         IF n_jobs_running_count = 0
         THEN
            -- EXIT THE LOOP ONCE ALL THE BACK GROUND JOBS ARE DONE
            break_signal := 1;
         ELSE
            DBMS_OUTPUT.put_line ('KEEP WAITING');
            -- CHECK AFTER EVERY ONE MINUTE FOR THE BACKGROUND JOB STATUS.
            DBMS_LOCK.sleep (60);
         END IF;
 
         EXIT WHEN break_signal = 1;
      END LOOP;
 
      break_signal := 0;
 
      -- BUILD THE ANALYZE INDEXES JOBS TO BE SUBMITTED IN THE BACKGROUND
      FOR i IN index_table.FIRST .. index_table.COUNT
      LOOP
         vjob_num2.EXTEND;
         -- ESTIMATE THE STATISTICS WITH 30 PERCENT OF IT'S DATA
         job_command :=
               'ANALYZE INDEX '
            || index_table (i)
            || ' ESTIMATE STATISTICS SAMPLE 30 PERCENT';
         DBMS_OUTPUT.put_line (job_command);
         DBMS_JOB.submit (job       => vjob_num2 (i),
                          what      =>    'EXECUTE IMMEDIATE '''
                                       || job_command
                                       || ''';'
                         );
      END LOOP;
 
      -- SUBMIT THE BUILT BACKGROUND JOBS TO RUN IN PRARALLEL .
      COMMIT;
      -- ANALYZE THE TABLE IN THE MEAN WHILE. THIS IS NOT DEPENDENT ON THE ABOVE BACKGROUND JOBS.
      DBMS_STATS.gather_table_stats (ownname               => UPPER
                                                                 (TRIM
                                                                     (schema_name_h
                                                                     )
                                                                 ),
                                     tabname               => UPPER
                                                                 (TRIM
                                                                     (table_name_h
                                                                     )
                                                                 ),
                                     estimate_percent      => 30
                                    );
 
      -- KEEP WAITING TILL ALLL THE BACKGROUND JOBS ARE DONE ( ANALYZE INDEXES )
      LOOP
         dbms_output.put_line('wait loop');
         SELECT COUNT (job)
           INTO n_jobs_running_count
           FROM user_jobs
          WHERE job IN (SELECT *
                          FROM TABLE (CAST (vjob_num2 AS job_num_tab)));
 
         IF n_jobs_running_count = 0
         THEN
            break_signal := 1;
         ELSE
            DBMS_OUTPUT.put_line ('KEEP WAITING');
            -- CHECK EVERY 1 MINUTE FOR THE STATUS OF THE BACKGROUND JOBS.
            DBMS_LOCK.sleep (60);
         END IF;
 
         EXIT WHEN break_signal = 1;
      END LOOP;
   END IF;
END;
/

Select allOpen in new window