Generate a script to run complete refresh for all tables in schema

-- Replace USERNAME with schema owner who's snapshots are to be refreshed

set echo off verify off feedback off head off pagesize 0

spool comp_refresh.sql

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

select 'spool comp_refresh.log'
from dual
/

select 'exec DBMS_SNAPSHOT.REFRESH (''' || 'USERNAME.' || name || '''' || ',' || '''' || 'C'
|| '''' || ');' 
from dba_snapshots
where owner = 'USERNAME'
/

select 'spool off'
from dual
/

spool off

exit;