This discussion is archived
3 Replies Latest reply: Mar 6, 2013 4:36 AM by Christine Schnittker RSS

Rebuild index

user9077483 Newbie
Currently Being Moderated
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
IS
l_sql VARCHAR2(4000);
CURSOR cur_tab_schema 
IS
SELECT tablename,schema_name
FROM control_table3;

CURSOR schema_names
 IS
SELECT owner_name FROM global_bu_mapping;
            
CURSOR indx_names(p_schema_name VARCHAR2,p_tablename VARCHAR2)
 IS
 select index_name
        from all_indexes
        where owner = p_schema_name
            and table_name = p_tablename;
begin
FOR c1 IN cur_tab_schema 
LOOP
for c2 IN schema_names
LOOP
FOR c3 IN indx_names (c2.owner_name,c1.tablename)
 loop
      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;
end;
/
This contains the 30 schema names.
SELECT owner_name FROM global_bu_mapping;
Please help me.
  • 1. Re: Rebuild index
    BEDE Explorer
    Currently Being Moderated
    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
    user9077483 Newbie
    Currently Being Moderated
    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.

    Thanks.
  • 3. Re: Rebuild index
    Christine Schnittker Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points