declare
  sqls varchar(1000);
      cursor tb1 is select t."name" as name from sysdatabases@master t where t."name" like 'USER%' 
      order by t."name";
      begin
      for i in tb1
      loop
          sqls:='CREATE MATERIALIZED VIEW '||i.name||'.test1 nologging '||
               'TABLESPACE USER2009 '||
               'BUILD DEFERRED '||
               'as  select * from test1@'||i.name||
               ' where rownum=rownum ';  
               execute immediate sqls;
          
      end loop;
      end;

create or replace procedure USER2009 as
begin
   declare
       sqls varchar(1000);
            cursor tb1 is select t."name" as name from sysdatabases@master t where t."name" like 'USER%' order by t."name";
            begin
            for i in tb1
            loop     
                sqls:='begin dbms_mview.refresh('''||trim(i.name)||'.test1''); end;';
            execute immediate sqls;
            end loop;
            end;
end;