Home » SQL & PL/SQL » SQL & PL/SQL » Monitoring all indexes through execute immediate
Monitoring all indexes through execute immediate [message #185383] Tue, 01 August 2006 08:32 Go to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
Hi everybody,
I created a stored procedure which modifies all defined indexes in a schema to the monitoring usage state.

CREATE OR REPLACE PROCEDURE PRC_MONITOR_INDEX(P_ARRAYSIZE IN NUMBER DEFAULT 100)
AS
TYPE IND_ARRAY_TYPE IS TABLE OF USER_INDEXES.INDEX_NAME%TYPE
INDEX BY BINARY_INTEGER;
IND_ARRAY_VAR IND_ARRAY_TYPE;
CURSOR C1 IS
SELECT DISTINCT INDEX_NAME
FROM USER_INDEXES
WHERE INDEX_TYPE NOT IN ('LOB');

BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO IND_ARRAY_VAR LIMIT P_ARRAYSIZE;
FORALL I IN 1..IND_ARRAY_VAR.COUNT
EXECUTE IMMEDIATE 'ALTER INDEX '||IND_ARRAY_VAR(I)||' MONITORING USAGE ';
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;


However , the error PLS-00801: Internal Error[*** ASSERT at file pdw4.c, line 586; Unknown expression Expr = 283.; PRC_MONITOR_INDEX__HIS__P__94646[16, 11]] appears!!!!
What's going wrong with this routine?????

Thanks a lot,
Simon
Re: Monitoring all indexes through execute immediate [message #185394 is a reply to message #185383] Tue, 01 August 2006 09:14 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi Simon,
Im sure that this is not the solution you are expecting...
But this can be used as an work around for your problem.

Try this
CREATE OR REPLACE PROCEDURE PRC_MONITOR_INDEX 
AS
  TYPE IND_ARRAY_TYPE IS TABLE OF USER_INDEXES.INDEX_NAME%TYPE
  INDEX BY BINARY_INTEGER;
  IND_ARRAY_VAR IND_ARRAY_TYPE; 
BEGIN
SELECT DISTINCT INDEX_NAME bulk collect into IND_ARRAY_VAR 
  FROM USER_INDEXES 
 WHERE INDEX_TYPE NOT IN ('LOB');

FOR  I IN 1..IND_ARRAY_VAR.COUNT  
LOOP
   EXECUTE IMMEDIATE 'ALTER INDEX '||IND_ARRAY_VAR(I)||' MONITORING USAGE ';
END LOOP;
END;


Naveen
Re: Monitoring all indexes through execute immediate [message #185398 is a reply to message #185394] Tue, 01 August 2006 09:29 Go to previous messageGo to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
>>Im sure that this is not the solution you are expecting

Why??? this is exactly i was expecting to......

Many thanks , have a good day
Simon
Re: Monitoring all indexes through execute immediate [message #185399 is a reply to message #185383] Tue, 01 August 2006 09:32 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Simon...
Error PLS-00801: internal error [74301] was beacuse
FORALL only works with DML statements and so fails with EXECUTE IMMEDIATE.
I got this information from the below link
http://forums.oracle.com/forums/message.jspa?messageID=1013599

Naveen
Previous Topic: update one table with the values from second table
Next Topic: Changing two clauses into one
Goto Forum:
  


Current Time: Fri Dec 09 12:00:34 CST 2016

Total time taken to generate the page: 0.11266 seconds