0

I'm using 4 materialized views on my oracle 12c - all of them are updated manually by a stored procedure call.

All views include a lookup to context variable in where clause including a time span (from and to).

Context was created by

CREATE OR REPLACE CONTEXT myContext using prepare_export;

No view has any relation to other views except using the same user context but different variable names.

Execution Chain is (only one view should be refreshed):

execute prepare_export('MyViewName','20180131','20180231');

  1. entry in data_export table is generated for data_export logging

    INSERT INTO DATA_EXPORT 
(SOURCE_,EXPORT_TIME_,EXPORT_FROM_,EXPORT_TO_,user_name,user_ip) 
VALUES (viewName,systimestamp,date_From,date_To,(select user from dual),(select sys_context('userenv','ip_address') from dual));`

  2. context variables are set (from:20180131 and to:20180231) 

    dbms_session.set_context('myContext',viewName || '_from',date_from);
dbms_session.set_context('myContext',viewName || '_to',date_to);

  3. view MyViewName gets refreshed (atomic refresh false)

    dbms_mview.refresh(viewName,'c',atomic_refresh=>FALSE);

  4. data_export entry gets updated with count(*) of last refresh

    execute immediate 'UPDATE data_export set resultset_size_=' ||viewRowCount ||' where id_ = (select max(id_) from data_export where source_=''' || viewName || ''' group by source_)';

After the procedure has completed all views become invalid and need to be recompiled manually...

Any help out there?

Thx, Joe

Joe K
  • 1
  • 2

2 Answers2

0

I wouldn't know. However:

... all of them are updated manually by a stored procedure call (...) After the procedure has completed all views become invalid and need to be recompiled manually

Why wouldn't you include your manual recompilation command into the above mentioned stored procedure?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • sure this may work - but it fixes not the cause. By the way - I tried this already but sometimes it did not work - only if I call the recompile some seconds after completion of the stored procedure – Joe K Feb 21 '18 at 09:44
0

... I reduced the stored procedure contents to just update a very simple (select count(*)) and new materialized view - without any other insert, update, context...blabla

==> all views needs recompile afterwards...

Joe K
  • 1
  • 2
  • ok - its reproducible with simple materialized views and simple master. Sometimes the views become invalid after master DML happens sometimes not - it depends on if a refresh of view was done before and no compile happened... But it's not 100 percent deterministic... – Joe K Feb 22 '18 at 10:00