I know it was not efficient, but due to my privileges on an Oracle database I had tablespace on, I had been running an SSIS package to drop and recreate about 20 tables daily. Tables used for reporting queries. It took 2 hours. I was running SSIS from SQL SERVER to create table on JOSH@DabaseA by joining tables from JOSH@Clarity (where no users are allowed to create tables). This week I got them to allow me materialized views on JOSH@DatabaseA. I created them using "on prebuilt table" clause. I can see the MVs in my tablespace, but they did not refresh today as programmed and when I manually refresh them it only allows a complete refresh.
Below is what I get under the "SQL" tab in SQL Developer when I am evaluating one of the views. Below that is the MV creation script. I have searched but can't find an answer to the error that is listed. Also I don't know what the error is relating to. The MV was initially created. I was able to force a complete refresh, but not a fast refresh. I don't know what happened when it didn't refresh this AM. Is that what this error is about? And why is it coming as a comment? Did this comment appear when I created the MV or when it tried to refresh? I called my DB just to ask if I had privileges to DBMS_METADATA and he said, "probably yes on JOSH@DatabaseA and probably no on JOH@DatabaseB", but he didn't know if that was even related to what I am seeing. What is this error and is it preventing me from updating?
-- <b>Unable to render MATERIALIZED VIEW DDL for object JBERRIS.PASASTATUS with DBMS_METADATA attempting internal generator.</b>
CREATE MATERIALIZED VIEW PASASTATUS
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOCACHE
NOPARALLEL AS
Select unique
facte.AN_EPISODE_ID,
first_value(conce.CONCEPT_VALUE) over (partition by facte.an_episode_id order by conce.cur_value_datetime desc) as ASAstatus,
facte.AN_Date as AnesDate
from
F_AN_RECORD_SUMMARY@clarity facte
inner join HNO_INFO@clarity notee on facte.AN_53_ENC_CSN_ID = notee.PAT_ENC_CSN_ID
inner join NOTE_CONCEPT@clarity conce on notee.NOTE_ID = conce.HNO_ID
where
notee.IP_NOTE_TYPE_C = 24 /* 24 is preop note, 25 is post op */
and conce.CONCEPT_ID = 'EPIC#10040'
and facte.AN_EPISODE_ID in (select an_episode_id from F_AN_RECORD_SUMMARY@clarity fan3 inner JOIN OR_LOG@clarity orlclz2 ON orlclz2.LOG_ID = fan3.LOG_ID Where orlclz2.LOC_ID IN (4060020, 4060021,4060022, 1170004))
and facte.AN_DATE >='01-oct-15'
MV Creation SQL
CREATE MATERIALIZED VIEW "JBERRIS"."PASASTATUS" ("AN_EPISODE_ID", "ASASTATUS", "ANESDATE")
ON PREBUILT TABLE WITH REDUCED PRECISION
USING INDEX
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT (Sysdate + 1) + 7/24
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS Select unique
facte.AN_EPISODE_ID,
first_value(conce.CONCEPT_VALUE) over (partition by facte.an_episode_id order by conce.cur_value_datetime desc) as ASAstatus,
facte.AN_Date as AnesDate
from
F_AN_RECORD_SUMMARY@clarity facte
inner join HNO_INFO@clarity notee on facte.AN_53_ENC_CSN_ID = notee.PAT_ENC_CSN_ID
inner join NOTE_CONCEPT@clarity conce on notee.NOTE_ID = conce.HNO_ID
where
notee.IP_NOTE_TYPE_C = 24 /* 24 is preop note, 25 is post op */
and conce.CONCEPT_ID = 'EPIC#10040'
and facte.AN_EPISODE_ID in (select an_episode_id from F_AN_RECORD_SUMMARY@clarity fan3 inner JOIN OR_LOG@clarity orlclz2 ON orlclz2.LOG_ID = fan3.LOG_ID Where orlclz2.LOC_ID IN (4060020, 4060021,4060022, 1170004))
and facte.AN_DATE >='01-oct-15';
That error is coming back SQL Developer not from the database. It's wanting to use DBMS_METADATA to get some DDL, and it failed (most typically insufficient privileges to access the package itself or some elements of the data dictionary) so SQL Dev fell back onto querying the data dictionary itself and not using DBMS_METADATA.
So this is unrelated to whether you can do a fast refresh or not. To best see why a MV can (or can not) be fast refreshed, check out the DBMS_MVIEW.EXPLAIN_MVIEW facility.