rem -----------------------------------------------------------------------
rem Filename:   mksnplgs.sql
rem Purpose:    Create a script to rebuild all snapshot logs
rem Date:       12-Feb-1999
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

conn /@&1

set feed off echo off pagesize 50000 trimspool on head off line 500

col global_name new_value global_name
select global_name from global_name
/

spool snap_&global_name..sql

select 'drop snapshot log on '||log_owner||'.'||master||';'
from   sys.dba_snapshot_logs
/

select 'create snapshot log on '||log_owner||'.'||master||
        ' storage (initial 1M next 1M maxextents unlimited pctincrease 0);'
from   sys.dba_snapshot_logs
/

select 'GRANT '||privilege||' ON '||owner||'.'||table_name
       ||' to '||grantee||';'
from   sys.dba_tab_privs
where  table_name like 'MLOG$%'
/

spool off

spool snap_&global_name..log
prompt Recreating snapshot logs for database &global_name.select 'Re
@snap_&global_name..sql
spool off

exit