Script to analyze a particular schema in the DB

Script to analyze schema

begin
for x in (select table_name from dba_tables where owner='<SCHEMA_NAME>')
loop
dbms_output.put_line('TABLE : ' || x.table_name);
begin
dbms_stats.gather_table_stats(ownname => '<SCHEMA_NAME>',tabname =>
x.table_name, cascade => TRUE, estimate_percent => 35);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;
/

Script to generate Analyze table script

select 'Analyze table <SCHEMA_NAME>.'|| table_name || ' estimate statistics;' from
dba_tables where owner = 'OWNER_NAME'