Script to generate creation of snapshots

Generate Oracle snapshots using the script below. It will check tablespaces for materialized
view logs and place the snapshots in the appropriate tablespaces (if you follow the Oracle's
recommendation to use different tablespaces for differently sized segments). This script is to
be run on spanshotlog site.

set head off verify off pagesize 0 linesize 300

--
-- Replace these with your own variables
--
define log_owner=LOGUSER
define view_owner=VIEWUSER

define log_tb1=TBSMALL
define log_tb2=TBMEDIUM
define log_tb3=TBLARGE

define view_data_tb1=TB_TAB01
define view_data_tb2=TB_TAB02
define view_data_tb3=TB_TAB03

define view_ind_tb1=TB_IND01
define view_ind_tb2=TB_IND02
define view_ind_tb3=TB_IND03

define linkname=DBLINK

SELECT 'create materialized view &view_owner' || '.' || a.master || ' tablespace ' ||
	CASE
		WHEN b.tablespace_name = '&log_tb1'
			THEN '&view_data_tb1'
		WHEN b.tablespace_name = '&log_tb2'
			THEN '&view_data_tb2'
		WHEN b.tablespace_name = '&log_tb3'
			THEN '&view_data_tb3'	
	END || ' using index tablespace ' ||
	CASE
		WHEN b.tablespace_name = '&log_tb1'
			THEN '&view_ind_tb1'
		WHEN b.tablespace_name = '&log_tb2'
			THEN '&view_ind_tb2'
		WHEN b.tablespace_name = '&log_tb3'
			THEN '&view_ind_tb3'
	END || ' refresh FAST start with sysdate next sysdate + 1/48 ' ||
	'AS SELECT * FROM ' || a.log_owner || '.' || a.master || '@&linkname;'
FROM dba_snapshot_logs a, dba_segments b
WHERE a.master = b.segment_name
AND a.log_owner = b.owner 
AND a.log_owner = '&log_owner'
/