Analyzer

				
--**************************************
-- Name: Analyzer
-- Description:This procedure will Analyze & Estimate Statistics for all tables in the users schema. Only takes 3-10 minutes to run. It can easily be edited to Compute Statistics or specific number of rows in each table.
-- By: Ron Santillano
--
-- Assumes:This has been tested on Oracle 7.3.4 thru 8.1.7
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=456&lngWId=5--for details.--**************************************

---------------------------------------------------------------------
--This script will analyze 1024 records in each table the user owns--
--By Ron Santillano 3/13/2002--
---------------------------------------------------------------------
set serveroutput on size 1000000
DECLARE
 cursor_id Integer;
 v_tablenames varchar2(30);
 v_counter number:=0;
CURSOR rec_getter IS
select table_name
from user_tables;
BEGIN
 Open rec_getter;
 LOOP
fetch rec_getter INTO v_tablenames;
exit when rec_getter%notfound;
 cursor_id:=DBMS_SQL.OPEN_CURSOR;
 --estimate statistics can be changed to compute statistics but will take much longer
 --to run since compute statistics will go through every record in each table 
 DBMS_SQL.PARSE (cursor_id, 'ANALYZE TABLE '||v_tablenames||' estimate statistics',DBMS_SQL.NATIVE);
 DBMS_SQL.CLOSE_CURSOR (cursor_id);
v_counter:=v_counter + 1;
DBMS_OUTPUT.PUT_LINE (' Processing Table: '||v_tablenames);
 End loop;
 DBMS_OUTPUT.PUT_LINE ('The procedure has processed '||v_counter||' tables'); 
 Close rec_getter;
end;
/