CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Sarcastic Dev
Sarcastic Dev

Reputation: 55

This code is working in a DB, but while i am trying compile it in another DB. it is throwing error

create or replace procedure rebuild_indexes(
    p_table_name in varchar2 := 'SYSTEM'
) as
SQL_STR VARCHAR2(1000);
USERNAME VARCHAR2(50);
begin

    Select USER into USERNAME from dual;

    for indexes_to_rebuild in
    (
        select index_name
        from dba_indexes
        where owner =  USERNAME
           -- and table_name = p_table_name
    ) loop
        SQL_STR := 'alter index '||USERNAME||'.'||indexes_to_rebuild.index_Name|| ' rebuild';
        execute immediate SQL_STR;
    end loop;
end;

EDIT: The Error i get:

1- Error(16,51): PLS-00364: loop index variable 'INDEXES_TO_REBUILD' use is invalid 2- Error(11,9): PL/SQL: SQL Statement ignored 3- Error(12,14): PL/SQL: ORA-00942: table or view does not exist 4- Error(16,9): PL/SQL: Statement ignored

Upvotes: 0

Views: 672

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

There is no need for Select USER into USERNAME from dual; just use USER directly. Try this one:

for indexes_to_rebuild in
(
    SELECT INDEX_NAME
    FROM USER_INDEXES
    WHERE INDEX_TYPE IN ('NORMAL', 'BITMAP', 'FUNCTION-BASED BITMAP', 'FUNCTION-BASED NORMAL')
        AND PARTITIONED = 'NO'
        AND TEMPORARY = 'N'
        -- and table_name = p_table_name
) loop
    SQL_STR := 'alter index '||USER||'.'||indexes_to_rebuild.index_Name|| ' rebuild';
    execute immediate SQL_STR;
end loop;

For partitioned indexes you have to execute REBUILD separately for each (sub-)partition.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

There is nothing wrong with your code. The user which you have logged in does not have privileges to view DATA DICTIONARY objects.

So, Login first from a SYSDBA user and execute 

GRANT SELECT on DBA_INDEXES to youruser;

Or, If you need the user to have privileges to view all the data dictionary ( all_objects etc.. )

GRANT SELECT ANY DICTIONARY TO youruser;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

It seems the user lacks a select right on dba_indexes. Have a DBA grant the user the right:

grant select on dba_indexes to thatusername;

Or better, as your are only interested in the user's indexes anyway, don't read dba_indexes at all, but user_indexes instead. Change

select index_name
from dby_indexes
where owner =  USERNAME

to

select index_name
from user_indexes

Upvotes: 0

Related Questions