Options

Materialized View and Reduced Precision /DMS-34050

jaspernygaardjaspernygaard Posts: 38 Bronze 1
edited January 11, 2019 12:58PM in Schema Compare for Oracle

SCO does not consistently extract properties of MV for creation scripts.

E.g.

The use of REFRESH with specific timestamp is a separate issue.

Here i concentrate on 1 other error around the 'WITH REDUCED PRECISION' clause.

on RHS DB I can extract the MV with dbms_metadata with

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV_FYSRAP_BEH', 'H6601') from dual

Which gives 

 

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
 

This demonstrate that the 'WITH REDUCED PRECISION' is present at the RHS DB but for some reason SCO does not reflect that in the extracted script.

Also I note that the column aliasing clause of the SCO script is absent. I have not checked what would happen if it actually aliased column names from the original query to the MV. SCO could have verified that was the case and omitted it for that reason.

Any work around for this?

Tagged:
· Share on Twitter

Best Answers

  • Options
    andersharderandersharder Posts: 7 Bronze 1
    Answer ✓
    For anyone else seeing this thread. 
    This has been resolved.
    Qouting Eddie from the ticket to what solved our specific problem.(note you might have same symptoms due to other underlying issues)

    "There is an error which is permissions related trying to obtain the Materialized View Reduced Precision. I suspect you may already know this, our tools query a number of data dictionaries and system objects as listed in this help article:
    https://documentation.red-gate.com/sco5/requirements/permissions-required-to-use-schema-compare-for-oracle

    The query being run is as follows:
    SELECT s.sowner as owner, s.vname as mview_name, decode(bitand(s.flag, 262144), 262144, 'Y', 'N') as reduced_precisions 
      FROM sys.snap$ s WHERE ((s.sowner = 'H6601' OR s.sowner = 'DCFO' OR s.sowner = 'DCF_SERVICES' OR s.sowner = 'H0915' OR s.sowner = 'SNP' OR s.sowner = 'GLRCHR' OR s.sowner = 'INVOICING' OR s.sowner = 'REPORTING' OR s.sowner = 'STAGING' OR s.sowner = 'H6601ADM' OR s.sowner = 'H6601FEJL') );
    I suspect the user of your RHS does not have security permissions to the system object 'sys.snap$'  which results in a ORA-00942 error.  Just before this point in the log file the process indicates it is populating object privileges, types, triggers, clusters, materialized views.

    Does giving the user for the RHS permissions to 'sys.snap$' help to resolve the problem?
    "
    · Share on Twitter
  • Options
    jaspernygaardjaspernygaard Posts: 38 Bronze 1
    Answer ✓
    For reference, a ticket was created (125725). However resolved with a user with the correct permissions.
    · Share on Twitter

Answers

  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi, thank you for your forum post.

    See screen shot below, I cannot reproduce the problem you have described above.  I am using V5.0.0.916 of SCO.  A different support ticket has been created for you for this problem.  I will private message you the ticket details and request a copy of the verbose log file.

    Many Thanks
    Eddie

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.