3 Replies Latest reply: Mar 6, 2013 6:36 AM by Christine Schnittker RSS

    Rebuild index

      Hi Experts,
      I have one control table as below.
      I want to rebuild all indexes for the tables in control table.

      The control table is having the following data.
      SEQ_ID     TABLENAME                SCHEMA_NAME     
      1     GEDIS_ORDER_FORM_STATES           ALL
      2     GEDIS_NOTES                ALL     
      3     GEDIS_CARD_TYPE_AUDIT         APRT     
      4     FAX_HEADER                   OMS
           In the control_table schema_name "ALL" means this is for 30 schemas(The table is existed in 30 schemas).
      Except for schema_name "ALL" ,the table is existed in the particular schema(The table is existed in the only one schema).

      I tried the following code it is executing for all 30 schemas(ALL).
      But it is not executing for specific schemas.
      CREATE OR REPLACE PROCEDURE Rebuilding_index
      l_sql VARCHAR2(4000);
      CURSOR cur_tab_schema 
      SELECT tablename,schema_name
      FROM control_table3;
      CURSOR schema_names
      SELECT owner_name FROM global_bu_mapping;
      CURSOR indx_names(p_schema_name VARCHAR2,p_tablename VARCHAR2)
       select index_name
              from all_indexes
              where owner = p_schema_name
                  and table_name = p_tablename;
      FOR c1 IN cur_tab_schema 
      for c2 IN schema_names
      FOR c3 IN indx_names (c2.owner_name,c1.tablename)
            l_sql:= 'alter index '||c2.owner_name||'.'
                  ||c3.index_name||' rebuild';
       DBMS_OUTPUT.put_line (l_sql);
      EXECUTE immediate l_sql;            
          end loop;
          end loop;
          end loop;
      This contains the 30 schema names.
      SELECT owner_name FROM global_bu_mapping;
      Please help me.
        • 1. Re: Rebuild index
          Does the oracle user that owns the schema the given procedure is have the grants needed on all the objects in the other schemas? I think that's why it does not work for some schemas.
          I did something like that only for objects in one schema and not in different schemas.

          Edited by: BEDE on Mar 6, 2013 11:12 AM
          • 2. Re: Rebuild index
            Procedure is having access on all schemas.

            ALL means 30 schemas we can get it from

            SELECT owner_name FROM global_bu_mapping;

            Apart from 30 we have some other schemas. The schema name should be taken from control table.

            Please help me how to implement this logic.

            • 3. Re: Rebuild index
              Christine Schnittker
              You shouldn't go around rebuilding indexes like that..

              Apart from that, get rid of at least 2 loops. You can get all statements with something like (not tested)
              SELECT 'alter index '||ai.owner_name||'.'||ai.index_name||' rebuild' AS alter_stmt
              FROM all_indexes ai,
                       control_table3 ct,
                       global_bu_mapping g
              WHERE  (g.owner_name = ct.schema_name OR ct.schema_name = 'ALL')
                  AND ai.table_owner = g.owner_name
                  AND ai.table_name = g.schema_name;
              Please note that you probably also confused table owner and index owner.

              // Tine