problem with cursor and analyze index

From: Marc Eggenberger <marc.eggenberger_at_power.alstom.com>
Date: Fri, 28 Feb 2003 15:28:15 +0100
Message-ID: <MPG.18c99068fdee054498969e@localhost>

hi there.

i found some articles on oracle that said I can determ when an index is fragmented by doing

analyze index &index_name validate structure;

and then I have to check the index_stats view ... I wanted to do this analyze on all indexes of a database with the follwoing code:

set serveroutput on size 1000000

declare

    CURSOR cur_name is select INDEX_NAME from DBA_INDEXES where owner = 'AMS';
    indexname dba_indexes.INDEX_NAME%TYPE; begin

    open cur_name;
    loop

        fetch cur_name into indexname;
        exit when cur_name%NOTFOUND;
        analyze index 'ams.'||indexname validate structure;

    end loop;
    close cur_name;
end;

but I get an error "Found Symbol INDEX where of these has been expected: := . ( @ % ;
(error was in German so I translated it to English)

The system is Oracle 8.1.7.

Why does this not work?
Any hints?

-- 
mfg
Marc Eggenberger

Received on Fri Feb 28 2003 - 08:28:15 CST