Generate an analyze script for all tables in schema

-- Replace USERNAME with the schema owner who's tables are to be analyzed.

set head off pagesize 0 echo off verify off feedback off linesize 200

spool analyze.sql

select 'set head on echo on verify on feedback on'
from dual
/

select 'spool analyze.log'
from dual
/

select	'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>' || '''' || owner || 
	'''' || ', ' || 'tabname=>' || '''' || table_name || '''' || ', ' || 
	'cascade=>true, degree=>4, estimate_percent=>99);'
from dba_tables
where owner = 'USERNAME'
/

select 'spool off'
from dual
/

select 'exit;'
from dual
/

spool off

exit;