Analyze All Tables

Description: This procedure will analyze and estimate statistics for all the tables in the user’s schema.

SET serveroutput ON size 1000000
DECLARE
 cursor_id INTEGER;
 v_tablenames VARCHAR2(30);
 v_counter NUMBER:=0;
CURSOR rec_finder IS
SELECT table_name
FROM user_tables;
 
BEGIN
OPEN rec_finder;
  LOOP
  FETCH rec_getter INTO v_tablenames;
  EXIT WHEN rec_finder%notfound;
   cursor_id:=DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE (cursor_id, 'ANALYZE TABLE '||v_tablenames||' estimate stats',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 ('Tables analyzed:'||v_counter.);
CLOSE rec_finder;
END;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.