Andrew Fraser DBA

Timeout SQL materialized view refresh

Problem today where one hourly cron MV refresh hung (waiting on “SQL*Net message from dblink”), causing the other refreshes to queue up waiting on locks, eventually using up all the session so users got “ORA-00018: maximum number of sessions exceeded”

To prevent this happening in the future, it is possible to timeout the MV refresh using a profile.

-- Create profile, 50mins connect time
create profile mv_refresh_limit_time limit connect_time 50 ;
-- Create user with that profile
create user mv_refresh_limit_time identified by ***** profile finance_refresh_limit_time ;
grant create session to mv_refresh_limit_time ;
-- Give the user the power to refresh materialized views
grant alter any materialized view to mv_refresh_limit_time ;

Then cron script runs as that time limited user:

conn mv_refresh_limit_time/*****
exec dbms_mview.refresh ( 'owner.mv_name' )

Note that parameter resource_limit must be set to true for this limit to take effect – and by default it is not set to true. Check/change it with:

sho parameter resource_limit
alter system set resource_limit=TRUE scope=both sid='*';

h/t Kamal Kiishore and OrionNet.