Search code examples
oracle-databasejobsmaterialized-views

how to get the refresh job id by mview name?

I'm using something like
SELECT JOB FROM DBA_JOBS WHERE WHAT LIKE '%NAME_OF_MVIEW%'
to get the id of refresh job which refreshes the materialized view by the name of mv.
(the content of 'WHAT' is dbms_refresh.refresh('"SCHEMA_USER"."NAME_OF_MVIEW"'))

I wanna know if there is a table or view which holds both job id and mv name, so i can use select JOB ... where WHATEVER = 'NAME_OF_MVIEW'.
Or any better way to get the refresh job id.

Thanks.

Solution

  • you can run this:

    select m.*, r.job
  from dba_refresh r
       inner join dba_refresh_children rc
               on rc.rowner = r.rowner
              and rc.rname = r.rname
       inner join dba_mviews m
               on m.owner = rc.owner
              and m.mview_name = rc.name;

    and add an applicalbe where clause filtering on the name of the mview.