User profile for andersharder

Just for completeness The SCO script referred to by jaspernygaard lLooks like this: <div>-- -- Script generated by Schema Compare for Oracle 4.0.11.536 on 07/11/2018 14:41:05 -- SET DEFINE OFF CREATE OR REPLACE TYPE dcf_services."O_BESBEMAERKNING" AS OBJECT (id number, kode number, besnr number, dyreart number, bemaerkning varchar2(200), logonid varchar2(30), ajourtid date); / CREATE OR REPLACE TYPE dcf_services."TO_BESBEMAERKNING" AS TABLE OF dcf_services.o_besbemaerkning; / ALTER FUNCTION dcf_services.fpipe_besbemaerkning COMPILE; ALTER FUNCTION dcf_services.fpipe_besbemaerkning COMPILE; DROP TYPE dcf_services.to_besbemaerkning; </div><div></div> / comments

Just for completeness The SCO script referred to by jaspernygaard lLooks like this:<div>-- -- Script generated by Schema Compare for Oracle 4.0.11.536 on 07/11/2018 14:41:05 -- SET DEFINE OFF CREA...

0 votes

deleted - duplicate / comments

deleted - duplicate

0 votes

Hi Eddie, As a colleague of jaspernygaard and the originator of this query let me elaborate. There are actually 3 discussions in this. 1. Cache: cache option for tables is an operational directive that has no structural bearing on your datamodel. The reason it can be relevant to exclude is that is Oracle Edition dependent whether it is supported and thus destination dependent a bit in the same way as storage parameters are which you allow to ignore. This is a minor point and as we have a another way of handling this so this is not really an issue for us anymore. Nevertheless I believe it would be reasonable and simple improvement of SCO to have that as an option. I believe there are other Oracle Edition options that in the same way could be relevant to ignore, but the cache option is the one I have experienced so far. 2. Roles, Context, Directories (and users come to think of it) A DB based system as the one we develop consists of many things. By bulk this is the user (schema) owned objects such as tables, indexes, packages etc which is handled by SCO. But there are objects that are in DB system level name spaces like Directories, Contexts, Roles and the Users (Schemas) themselves etc. It would be nice to be able to handle these objects (and the generation of scripts for deployment and maintenance of these) within SCO. I realize this is a quite substantial augmentation request and this will actually take SCO beyond what is in the name (*Schema* compare for Oracle) :-) For our needs, these are faily static as objects anyway and we can manage outside SCO. 3. Roles, privileges and the 'Cross-schema Permission' option The 'Cross-schema permission' option restricts SCO to consider privileges for the objects it manages given to the schemas within the project. This is necessary to use this option if one works with Databases (instances) that host several systems (related to different SCO projects) Even if SCO is not to handle deployment of the schemas (create user) themselves or roles or other system level component it would be beneficial if it recognized that a DB based system consist of more than the schemas and what they own. Particularly when it comes to privilege management. Typically as privilege management is done via roles. It would be beneficial if one could give a list roles (in line with the list of schemas ones SCO project consists of) to the SCO project. Just so privileges on objects handled by SCO given to these roles are also managed when the 'Cross-schema permission' option is selected. Further privileges for these roles (e.g. system privileges or for system level objects like dictionaries) is not part of this (that would be part of item 2 above). For our needs this is the most important issue and it will require attention outside SCO for every deployment. Hope this clarify things a bit. Thanks, Anders / comments

Hi Eddie,As a colleague of jaspernygaard and the originator of this query let me elaborate.There are actually 3 discussions in this.1. Cache: cache option for tables is an operational directive th...

0 votes

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? " / comments

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...

0 votes

Hi Redgate, Would you care to comment on whether you think using a '...on prebuilt table ...start with timestamp' for a MV extracted at one point in time from one database make sense to deploy to or just compare to another database (or the same at a different point in time) For such a deployment to work the data in the 'prebuilt table' and data in the logtable should first be deployed (which redgate does not do). Thanks, Anders / comments

Hi Redgate,Would you care to comment on whether you think using a '...on prebuilt table ...start with timestamp' for a MV extracted at one point in time from one database make sense to deploy to or...

0 votes

I have tried doing the extract and comparison with 'ignore: materialized view START WITH value' It still embeds the start with clause in the comparison and in the created deployment script. As the deployment script does't deploy data (nor should it) in the underlying table or the mview log table that is used to make the incremental refresh with the time for the 'start with' is meaningless to transfer or compare. The deployment script will try to recreate the mview, but could fail or cause inconsistent data as the log table might not contain data since the 'start with' time (which comes from a different instantation of the mview on a different database) nor will the 'prebuilt table' contain data as of the 'start with' time. / comments

I have tried doing the extract and comparison with 'ignore: materialized view START WITH value' It still embeds the start with clause in the comparison and in the created deployment script.As the d...

0 votes

Regarding the 'ignore: materialized view START WITH value' option. The option does exclude the start with clause from comparison. I cannot foresee any situation where you would not want to ignore the start with clause when it is phrased like an absolut time. The problem is around the start with timestamp. When there is a genuine difference in the MVIEW then the deployment wizard takes the timestamp clause with absolut time from the source (LHS) database and uses that on the destination database. That does not make sense as it refers to validity of data in a potential prebuilt table and a mview log table which are on the destination database. / comments

Regarding the 'ignore: materialized view START WITH value' option. The option does exclude the start with clause from comparison. I cannot foresee any situation where you would not want to ignore t...

0 votes

How can we help you today?

andersharder

Comments