Run the below query to lit all the materialized views in a schema in Redshift database.
select name from STV_MV_INFO where schema='schemaname' ;
You can also query STV_MV_INFO to find out if a particular MV is stale using below sql statement. Value is ‘t’ if the data in MV is stale and ‘f’ if the data is upto date.
select name, is_stale from STV_MV_INFO where schema='schemaname' ;
You can alter a materialized view to refresh automatically as below where mv_name is the name of the materialized view.
alter materialized view mv_name auto refresh YES;You can also disable auto refresh of a materialized view as below:
alter materialized view mv_name auto refresh NO;