CoderZone.org

Category: >> Oracle PL/SQL >> Analyze All Tables Bookmark and Share

<< lastnext >>

Snippet Name: Analyze All Tables

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

Comment: (none)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009

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;
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 5