2

Essentially I have an MVIEW that doesn't need to be refreshed every time I run my report so as a test I'm just checking the minute interval where if it hasn't been refreshed in the last 20 min, I would refresh it. So, if I run this script by itself, it works properly:

declare
    LastRefresh float;
    myvar varchar2(10) := 'NO';
BEGIN

SELECT ROUND(ABS((LAST_REFRESH - SYSDATE)*24*60),0) 
        INTO LastRefresh
          FROM dba_snapshot_refresh_times 
        WHERE owner = 'ME'
          AND NAME = 'MATV_ADDRESS';

IF (LastRefresh > 20)
    THEN BEGIN myvar := 'YES'; END;
    --// THIS IS WHERE I WOULD REFRESH THE MVIEW
END IF;

dbms_output.put_line(myvar);

END;
/

The problem I'm having is when I try to run this within an Oracle procedure, I get the "Table or View does not exists" on dba_snapshot_refresh_times. I perform the EXECUTE IMMEDIATE to invoke the query but can't seem to find a way to insert into a variable with this method. Any clean alternative ways I could do do this?

Did a bit of research where I could potentially just use some other form of "flag" where I'd populate a temp table in order to check the value, but I figured I'd ask about a cleaner way / solution.

denisb
  • 787
  • 5
  • 18
  • 31
  • Is your access to `dba_snapshot_refresh_times` [granted via a role](https://stackoverflow.com/q/4198052/266304), rather than directly to your user? If you're only looking at your own objects the lesser-privileged versions - e.g. `user_snapshot_refresh_times` - would be enough anyway, as Littlefoot mentioned. I'm not sure what the rest is really asking - what flag? – Alex Poole Jan 12 '18 at 12:58
  • I'm logged in with the same user when attempting to run the script or compile the procedure if that's what you mean. Also attempted to "GRANT SELECT" to my user on that particular table, but that didn't work at the time. I think using the ALL_MVIEWS table might resolve my problem, running some tests now. – denisb Jan 12 '18 at 13:06
  • That isn’t what I meant; did you follow the link? Privileges granted via roles are not enabled in stored procedures with the default authid setting. – Alex Poole Jan 12 '18 at 13:27

2 Answers2

1

It appears that you're programming something that Oracle offers itself; why wouldn't you set materialized view to automatically refresh every 20 minutes? You'd use

alter materialized view matv_address 
refresh 
next sysdate + 20 / (60 * 24);

As of your problem (if I correctly understood the problem): DBA_ views are visible to users with DBA privileges. It seems that user you're connected to doesn't have those privileges. Instead, you could use USER_SNAPSHOT_REFRESH_TIMES (for materialized views belonging to you), or ALL_SNAPSHOT_REFRESH_TIMES (for materialized views you have access to, but belong to other users as well).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks for the input, and I also didn't know I could schedule the update that way directly within the MVIEW properties, good to know! I just found an alternative table (ALL_MVIEWS) which contains the LAST_REFRESH_DATE and appears to work within the procedure. Will run a few more tests to confirm. – denisb Jan 12 '18 at 13:04
1

Found my answer on this thread Materialized Views - Identifying the last refresh where I'm simply making use of an alternative table (all_mviews) to obtain the "Last Refresh Date" , so this appears to work within the Oracle procedure.

  SELECT ROUND(ABS((LAST_REFRESH_DATE - SYSDATE)*24*60),0) 
         INTO LastRefresh
         FROM all_mviews
        WHERE owner = 'ME'
          AND mview_name = 'MATV_ADDRESS';

   IF (LastRefresh > 20) THEN    
        BEGIN
          DBMS_SNAPSHOT.REFRESH(
            LIST                 => 'MATV_ADDRESS'
           ,PUSH_DEFERRED_RPC    => TRUE
           ,REFRESH_AFTER_ERRORS => FALSE
           ,PURGE_OPTION         => 1
           ,PARALLELISM          => 1
           ,HEAP_SIZE            => 1
           ,ATOMIC_REFRESH       => FALSE
           ,NESTED               => FALSE);
        END;
    END IF;
denisb
  • 787
  • 5
  • 18
  • 31