scripts/LJCOPY/SGG/Post-Upgrade/D1_MV_REFRESH_PROC.sql

create or replace PROCEDURE D1_MV_REFRESH_PROC (mv_name varchar2, est_percent number)
IS
	str varchar2(300);
	schma_name varchar2(20);
	tbl_owner varchar2(20);
BEGIN
	begin
		SELECT USER INTO schma_name FROM DUAL;
		SELECT TABLE_OWNER INTO tbl_owner   FROM ALL_SYNONYMS WHERE SYNONYM_NAME='D1_MV_REFRESH_PROC' AND OWNER= schma_name ;
	exception
		when no_data_found then
		SELECT USER INTO tbl_owner FROM DUAL;
	end;
	str := 'BEGIN DBMS_MVIEW.REFRESH('''||tbl_owner||'.'||mv_name||''',''C''); END;';
	EXECUTE IMMEDIATE str ;
	if (est_percent is null ) then
		str := 'BEGIN dbms_stats.gather_table_stats('''||tbl_owner ||''','''||mv_name||''', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>''for all indexed columns size auto'',CASCADE=>True,Degree => 32, Granularity => ''All''); END;';
	else
		str := 'BEGIN dbms_stats.gather_table_stats('''||tbl_owner ||''','''||mv_name||''', ESTIMATE_PERCENT=>'||est_percent||',METHOD_OPT=>''for all indexed columns size auto'',CASCADE=>True,Degree => 32, Granularity => ''All''); END;';
	end if;
	EXECUTE IMMEDIATE str ;
END;
/