Can you cause schema names to be ignored?

Compares and synchronizes the schemas of Oracle databases

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

Can you cause schema names to be ignored?

Postby rspooner » Fri Sep 24, 2010 9:36 pm

Is there an option where the schema name (and any associated double quotes on the schema name and the object name) can be ignored if the schema name matches the name of the schema you entered in the Source/Target dialog?

I am using Schema Compare to compare the contents of a number of schemas, one at a time, against a "master" schema. Some of our PL/SQL contains schema names. When I compare a SOURCE schema against a TARGET schema, Schema Compare flags such lines as differences, which is not what I was hoping for. As an example, I have:

In SOURCE:
Code: Select all
CREATE TRIGGER "SOURCE".TRIGGER1 AFTER DELETE....


In TARGET:
Code: Select all
CREATE TRIGGER "TARGET".TRIGGER1 AFTER DELETE....


While I accept these two lines are lexically different, each schema would react identically when the PL/SQL is executed in each schema, and I don't want Schema Compare to flag such differences. There are probably over 3,000 such lines flagged by Schema Compare per comparison in my environment, which is rather a lot to go through and eyeball!

Developing this a stage further:

If there was another schema name specified which did not match the schema under scrutiny:

In SOURCE:
Code: Select all
CREATE TRIGGER "SOURCE".TRIGGER2 AFTER DELETE....


In TARGET:
Code: Select all
CREATE TRIGGER "MASTER".TRIGGER2 AFTER DELETE....


I would want this flagged as a difference.

Also:

If there a schema name was missing, but the schema name which was present matched the name of the schema under scrutiny, perhaps there should be a "sub-option" to determine whether or not a difference should be flagged:

In SOURCE:
Code: Select all
CREATE TRIGGER "SOURCE".TRIGGER3 AFTER DELETE....


In TARGET:
Code: Select all
CREATE TRIGGER TRIGGER3 AFTER DELETE....


But the following is definitely a difference, as you are specifying a schema name on the object which is not the same as the schema under scrutiny.

In SOURCE:
Code: Select all
CREATE TRIGGER "MASTER".TRIGGER3 AFTER DELETE....


In TARGET:
Code: Select all
CREATE TRIGGER TRIGGER3 AFTER DELETE....



Thanks in advance!
rspooner
 
Posts: 1
Joined: Fri Sep 24, 2010 8:10 pm
Location: San Diego, CA

Postby neil.anderson » Tue Sep 28, 2010 2:42 pm

Hi rspooner, thanks for trying out Schema Compare for Oracle.

Is there an option where the schema name (and any associated double quotes on the schema name and the object name) can be ignored if the schema name matches the name of the schema you entered in the Source/Target dialog?

I am using Schema Compare to compare the contents of a number of schemas, one at a time, against a "master" schema. Some of our PL/SQL contains schema names. When I compare a SOURCE schema against a TARGET schema, Schema Compare flags such lines as differences, which is not what I was hoping for. As an example, I have:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER1 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER "TARGET".TRIGGER1 AFTER DELETE....


While I accept these two lines are lexically different, each schema would react identically when the PL/SQL is executed in each schema, and I don't want Schema Compare to flag such differences. There are probably over 3,000 such lines flagged by Schema Compare per comparison in my environment, which is rather a lot to go through and eyeball!


My understanding is that Schema Compare already does this by default at the moment. PL/SQL that differs just by the schema name is not considered different and appears as an identical object, but if you select it, the SQL Differences pane will still highlight any textual differences in the creation script.

If you are seeing objects which are flagged as different and the only difference appears to be the schema name then it would be useful if you could provide us with further information so we can investigate.

Developing this a stage further:

If there was another schema name specified which did not match the schema under scrutiny:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER2 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER "MASTER".TRIGGER2 AFTER DELETE....


I would want this flagged as a difference.

Also:

If there a schema name was missing, but the schema name which was present matched the name of the schema under scrutiny, perhaps there should be a "sub-option" to determine whether or not a difference should be flagged:

In SOURCE:
Code:
CREATE TRIGGER "SOURCE".TRIGGER3 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER TRIGGER3 AFTER DELETE....


But the following is definitely a difference, as you are specifying a schema name on the object which is not the same as the schema under scrutiny.

In SOURCE:
Code:
CREATE TRIGGER "MASTER".TRIGGER3 AFTER DELETE....


In TARGET:
Code:
CREATE TRIGGER TRIGGER3 AFTER DELETE....


Thanks for your suggestions. We will consider this as a feature request for future releases of the tool.

Regards,
Neil Anderson
neil.anderson
 
Posts: 64
Joined: Tue Sep 28, 2010 2:17 pm

Postby Tom Harris » Fri Oct 22, 2010 1:57 pm

Hi there,

we are implementing this for the next update for Schema Compare for Oracle. Once we have a stable build we'll send you over a copy to test out

Many thanks for your detailed input

-Tom
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm

Postby Tom Harris » Fri Oct 29, 2010 1:45 pm

Hi,

good news! We do now have a build of Schema Compare for Oracle that ignores the schema name when visually 'diffing' objects. Please email oraclesupport@red-gate.com and we'll send over the details

Thanks, Tom
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm

Postby Tom Harris » Wed Jan 05, 2011 1:38 pm

Hi there,

this feature is now part of Schema Compare for Oracle v1.5. You can download from http://www.red-gate.com/products/oracle ... or-oracle/

Thanks, Tom

Tom Harris, Red Gate Software
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm

Postby Arden » Tue Jan 18, 2011 3:12 pm

We'd also like an option to remove the schema name from the deployment script. Is this currently possible?
Arden
 
Posts: 1
Joined: Tue Jan 18, 2011 2:48 pm

Postby Tom Harris » Tue Jan 18, 2011 4:40 pm

Hi there,

many thanks for your feedback. I'm afraid that this is not possible in the current version. This enhancement has already been requested for the next version of Schema Compare. I have added your 'vote' to the request. I can't make any promises at this stage about what will be included in the next version though.

Kind regards, Tom

Tom Harris - Red Gate Software
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm


Return to Schema Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests