Insufficient privileges to analyze an object

I wrote a stored procedure that is intended to be run as batch to collect statistics in the schemata. The procedure is similar to this (for simplicity, I removed lines dealing with logging and exceptions):

CREATE OR REPLACE PROCEDURE COLLECT_STATS AS BEGIN

	FOR rec_schema IN (
		SELECT distinct owner 
		FROM dba_segments
		WHERE (owner like 'CAL%'  
		  or owner like 'NTE%' )
		  and segment_type != 'TEMPORARY'
	) LOOP
		DBMS_STATS.gather_schema_stats( 
			rec_schema.owner , 
			NULL,FALSE,NULL,1,'DEFAULT',TRUE)
		;
		UTL_FILE.FFlush(v_id);
	END LOOP;
	UTL_FILE.FFlush(v_id);
	UTL_FILE.FCLOSE(v_id);

When created in schema SYS or SYSTEM, I received "ORA-20000 Insufficient privileges to analyze an object in Schema". It took me a while to figure out a workaround for the problem. The workaround is creating and running the procedure in another schema, after having granted "select on dbs_segments" and "analyze any" to it.

But it is very hard for me to explain why it is like this. I hope the gurus out there can provide some insight.

Cheers

Bonminh Lam

email: mailto at bmlam dot DE Received on Thu May 16 2002 - 04:54:33 CDT