Re: PL/SQL problem.... (Rebuild Indexes)

Check if you have the select previlages on sys.dba_segements table (If not grant the previlage on that table)

That could be one problem

Madhu Cherukuri

ashesh48_at_my-dejanews.com wrote in message <6rcntj$1as$1_at_nnrp1.dejanews.com>...
>Hi All, I am trying to write a query for rebuilding the Indexes but it
gives
>me the following errors..The code and error is as below. I don't get where
i
>am making mistake...? this would be a grate help.
>
>SQL> create or replace Procedure CID.REBUILD_INDEXES 2 IS 3 3 --select
>'Fragmented Indexes rebuilt on '|| 4 --to_char(sysdate,'mm/dd/yyyy')||' at
>'||to_char(sysdate,'hh:mi:ss AM') Comments 5 --from dual ; 6 6
>v_cursorid integer; 7 v_dummy integer; 8 v_index_name varchar2(50);
9
> v_drop_stmt varchar2(100); 10 10 cursor c1 is select segment_name
from
>sys.dba_segments 11 where owner = 'CID' 12 and extents > 1 13 and
>segment_type = 'INDEX' ; 14 14 BEGIN 15 15 -- Open Cursor 16
>v_cursorid := dbms_sql.open_cursor ; 17 open c1 ; 18 18 loop 19
fetch
>c1 into v_index_name ; 20 exit when c1%notfound ; 21 21 insert into
>cid.index_history(index_seq_num, process_name,action,process_date) 22
>values (rebuild_index_seq.nextval,v_index_name,'Index Rebuilt',sysdate) ;
23
> 23 -- Statement for the rebuilding the indexes. 24 v_drop_stmt :=
'alter
>index '||v_index_name||' rebuild tablespace cid_index' ; 25 25 -- Parse
>the Statement 26 dbms_sql.parse(v_cursorid,v_drop_stmt,dbms_sql.v7); 27
>27 -- Execute the statement. 28 v_dummy := dbms_sql.execute(v_cursorid);
>29 29 end loop ; 30 30 -- Close cursor. 31
>dbms_sql.close_cursor(v_cursorid); 32 commit ; 33 33 END; -- Procedure
>34 /
>
>****************
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>10/15 PL/SQL: SQL Statement ignored
>10/22 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>
>10/41 PLS-00201: identifier 'SYS.DBA_SEGMENTS' must be declared 19/5
>PL/SQL: SQL Statement ignored SQL> ************* here Index_history is the
>local table which will keep info about the rebuilt indexes...
>
>Thanks you for your help in advance.
>
>Ashesh
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Tue Aug 18 1998 - 20:19:22 CDT