CREATE OR REPLACE PACKAGE "FLATBED_DW"."PKG_TEST1" authid current_user AS
  
  procedure pr1; 
  
  end;
/

CREATE OR REPLACE PACKAGE BODY "FLATBED_DW"."PKG_TEST1" 
IS

  procedure  pr1
  as
  BEGIN 
  FOR I IN (
             SELECT * FROM ALL_TABLES J 
             WHERE not exists (
                                SELECT 1 
                                FROM V$MVREFRESH O 
                                WHERE O.CURRMVOWNER = j.owner AND O.CURRMVNAME = j.table_name
                              )
           )
  LOOP
  RETURN; --GATHER STATS
  END LOOP; 
  END; 
END; 
/

SELECT * FROM USER_ERRORS WHERE NAME LIKE '%TEST1%'

SELECT *
FROM V$MVREFRESH O 

SYS> create role r;

Role created.

SYS> grant select on v_$mvrefresh to r;

Grant succeeded.

SYS> grant create session, create procedure to r;

Grant succeeded.

SYS> create user test identified by test;

User created.

SYS> grant r to test;

Grant succeeded.

SYS> CREATE OR REPLACE PACKAGE michel.PKG_TEST1 authid current_user AS
  2    procedure pr1;
  3  end;
  4  /

Package created.

SYS> show error
No errors.
SYS> CREATE OR REPLACE PACKAGE BODY michel.PKG_TEST1
  2  IS
  3    procedure  pr1
  4    as
  5    BEGIN
  6    FOR I IN (
  7               SELECT * FROM ALL_TABLES J
  8               WHERE not exists (
  9                                  SELECT 1
 10                                  FROM V$MVREFRESH O
 11                                  WHERE O.CURRMVOWNER = j.owner AND O.CURRMVNAME = j.table_name
 12                                )
 13             )
 14    LOOP
 15    RETURN; --GATHER STATS
 16    END LOOP;
 17    END;
 18  END;
 19  /

Package body created.

SYS> show error
No errors.
SYS> grant execute on michel.pkg_test1 to r;

Grant succeeded.

SYS> conn test/test
Connected.
TEST> SELECT * FROM V$MVREFRESH;

no rows selected

TEST> exec michel.PKG_TEST1.pr1

PL/SQL procedure successfully completed.

SYS> drop user test cascade;

User dropped.

SYS> drop role r;

Role dropped.

SYS> create role r;

Role created.

SYS> grant select on v_$mvrefresh to r;

Grant succeeded.

SYS> grant create session, create procedure to r;

Grant succeeded.

SYS> create user test identified by test;

User created.

SYS> grant r to test;

Grant succeeded.

SYS> conn test/test
Connected.
TEST> CREATE OR REPLACE PACKAGE PKG_TEST1 authid current_user AS
  2    procedure pr1;
  3  end;
  4  /

Package created.

TEST> show error
No errors.
TEST> CREATE OR REPLACE PACKAGE BODY PKG_TEST1
  2  IS
  3    procedure  pr1
  4    as
  5      tables sys.odcivarchar2list;
  6    BEGIN
  7      execute immediate
  8        'SELECT owner||''.''||table_name FROM ALL_TABLES J
  9               WHERE not exists (
 10                                  SELECT 1
 11                                  FROM V$MVREFRESH O
 12                                  WHERE O.CURRMVOWNER = j.owner AND O.CURRMVNAME = j.table_name
 13                                )'
 14        bulk collect into tables;
 15    FOR I IN tables.first..tables.last
 16    LOOP
 17      dbms_output.put_line (tables(i)); --GATHER STATS
 18    END LOOP;
 19    END;
 20  END;
 21  /

Package body created.

TEST> show error
No errors.
TEST> SELECT * FROM V$MVREFRESH;

no rows selected

TEST> exec PKG_TEST1.pr1

PL/SQL procedure successfully completed.