Analyze

DECLARE
lcount NUMBER;
BEGIN
DBMS_OUTPUT.put_line
('CREATE OR REPLACE PROCEDURE SIEBEL_GATHER_STATS IS BEGIN ');
FOR i IN (SELECT * FROM user_tables ORDER BY 1)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' i.table_name INTO lcount;
IF lcount > 0 THEN
DBMS_OUTPUT.put_line
( ' BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME=>''SIEBEL'' ,
TABNAME=>''' i.table_name ''' ,
METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'' ,
GRANULARITY=>''ALL'' ,
CASCADE=>TRUE ,
DEGREE=>DBMS_STATS.DEFAULT_DEGREE);
END;' );
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('END;');
END;