Materialized View and timestamps /DMS-34049
SCO include state info of the instance of MV into its metadata in the form of the current refresh time (in relation to log files). That is very inconvenient
For MV present and identical on source and dest DB SCO will generate a deployment script as
-- -- Script generated by Schema Compare for Oracle 188.8.131.526 on 26/09/2018 12:30:42 -- SET DEFINE OFF DROP MATERIALIZED VIEW h6601.mv_fysrap_roff; CREATE MATERIALIZED VIEW h6601.mv_fysrap_roff ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH START WITH TO_DATE('2018-9-25 8:35:56', 'yyyy-mm-dd hh24:mi:ss') NEXT SYSDATE+30/(24*60*60) AS SELECT id, rapportnavn, format, logonidfast, bes_id, status, synlighed, logonid, ajourtid, behandler_id2, dyr_id, driftsenh_id FROM fysiskrapport WHERE frekvenstype IN (40, 41) AND synlighed <> 0;
There are several problems around this:
- The MV exists and is identically in definition on source and destination database (SCO has another problem around that, but that is a separate issue) and there is no need to recreate it. It can be quite resource demanding to recalc a MV.
- Dependent privileges (outside the deployment set of schemas) will get lost by this. (if there where genuine differences this would happen anyway)
- The metadata for this specifies using prebuilt table and refresh as of a timestamp. This is extracted from a source database and is the current state of the MV on the source, but the timestamp and the prebuild table refers to data on the source system and data is not part of the deployment only metadata. Especially this data as that is the log data for MV log it is relevant for. The corresponding data on the destination system will be used but that reflects the state on the destination system of the MV especially the existing (prebuild) table and the log table will be out of sync as of another time than at the source DB.
select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV_FYSRAP_BEH', 'H6601') from dual
CREATE MATERIALIZED VIEW "H6601"."MV_FYSRAP_BEH" ("ID", "RAPPORTNAVN", "FORMAT", "LOGONIDFAST", "BES_ID", "STATUS", "SYNLIGHED", "LOGONID", "AJOURTID", "BEHANDLER_ID2", "DYR_ID", "DRIFTSENH_ID") ON PREBUILT TABLE WITH REDUCED PRECISION USING INDEX REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE+30/(24*60*60) WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS select id, rapportnavn, format, logonidfast, bes_id, status, synlighed, logonid, ajourtid, behandler_id2, dyr_id, driftsenh_id from fysiskrapport where behandler_id2 is not null and synlighed <> 0
Which avoids the specific timestamp which is only useable if extracted from the same instantation of a MV which is being recreated.
This also is static over time where SCO will potentially extract different metadata every time it is comparing at is embed the timestamp of the last refresh from the state of the current instance of the MV.
Is it possible to avoid the timestamp?