Schema compare: why aren't Refresh Groups 'seen' in compare?

This bit me the other day. The two databases are copies of each other. Does anyone know the reason for this?

Old DB;

New DB (notice no Refresh Groups):

Schema Compare:

Result of Schema Compare:

Well…I can’t seem to get the screenshots to show up.

The end result is that the compare result states that zero objects exist in both but are different, zero objects exist in one but not other.

In other words…it doesn’t ‘see’ the objects to compare.

I see them in 12.12. What version of Toad are you using, and sorry to ask the obvious....but....you do have "Refresh Groups" checked under "Object Types", right?

Hi John,

I’m using 12.11.0.95 and I did select the refresh groups (after the alert log started spawning errors about them missing).

Is there a tutorial on posting screenshots somewhere? I’ve tried several methods and nothing seems to work. I’ll try an attachment.
Toad Refresh group problem.docx (96.6 KB)

Hi John, here is the output that I attempted to use in the post:

Old DB:

New DB:

Schema Compare:

Thank you,

Dan O’Leske

MIS Infrastructure Admin [ Oracle / SAP ]

Dassault Falcon Jet

Office:…+ 1 501 301 2126

Little Rock National Airport

3801 E10th Street

Little Rock, Arkansas 72202

USA

www.dassaultfalcon.com

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: December 04, 2017 1:04 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Schema compare: why aren’t Refresh Groups ‘seen’ in compare?

 
This email is external to Dassault. Caution with links and attachments.

RE: Schema compare: why aren’t Refresh Groups ‘seen’ in compare?

Reply by John Dorlon

I see them in 12.12. What version of Toad are you using, and sorry to ask the obvious…but…you do have “Refresh Groups” checked under “Object Types”, right?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Dan,

You didn't say, but it looks like you are using Toad 12.11. Correct?

I tried this version and it worked OK for me. Do you have any filters configured here?

If that's not it, the only other thing that I can think of is the obvious - make sure you have the same source and target schemas as what you are looking at in Schema Browser.

And if that's not it, turn on spool SQL and run the compare again, so we can see if what queries are being send to the database. Database -> Spool SQL -> Spool to Screen.

To post screen shots, I use the web interface, not email, to put a message on the forum…

I haven’t found a way to paste the images from clipboard. I always have to save them to file first. I click the “Insert/Edit Media” button in the toolbar above the area where you type in the message.

Thanks guys. The spool output was enlightening:


– Session: DB1
– Timestamp: 08:05:10.381
Select *
from sys.user_refresh
where 1=1
order by rname;


– Session: DB1
– Timestamp: 08:05:10.395
Select owner, name, type, rname, rownum
from sys.user_refresh_children
where 1=1
order by rname;


– Session: DB1
– Timestamp: 08:05:10.464
Select name, text
from sys.user_source
where type = ‘TRIGGER’
order by name, line;


– Session: DB2
– Timestamp: 08:05:10.898
Select *
from sys.user_refresh
where 1=1
order by rname;


– Session: DB2
– Timestamp: 08:05:10.908
Select owner, name, type, rname, rownum
from sys.user_refresh_children
where 1=1
order by rname;


– Session: DB2
– Timestamp: 08:05:10.916
Select name, text
from sys.user_source
where type = ‘TRIGGER’
order by name, line;

I have no idea why it’s going after triggers.

Let’s not get sidetracked on triggers. That the query for trigger source is a bug which was fixed in 12.12, but it not related to your problem with refresh groups.

It looks like you have no filtering enabled, otherwise your queries would have more of a “where” clause.

If you run these 2 queries in the editor, they come back with rows in one of your databases but not the other?

Select *
from sys.user_refresh
where 1=1
order by rname;

Select owner, name, type, rname, rownum
from sys.user_refresh_children
where 1=1
order by rname;

Correct, no filtering except for the 1000 row limitation. Both the above queries return data in both databases.

I’ve selected refresh groups only. Nothing else. (see original screenshot) So why is Toad trying to query Trigger information instead of Refresh Group information?

It must be related to my setup somehow if you can’t reproduce it. [View:/cfs-file/__key/communityserver-discussions-components-files/10/7317.Toad-Refresh-group-problem.docx:320:240]

It’s not trying to query triggers instead***,*** it’s querying triggers in addition to refresh groups, and it’s not using the data returned from the user_source query anyway. I assure you that it is unrelated to your refresh group problem.

The 2 queries that we are running…the ones against user_refesh and user_refresh_children, are all we need to compare refresh groups.

What are your database versions? I have been testing this on 12.1.0.2, but maybe the problem is version related.

I see. I get the same results from 10.2 -> 10.2 and 12.1 -> 11.2.

It works OK for me with Oracle 10.2.0.1 too. If you click on the script tab in your schema browser, are the scripts for the refresh groups displayed for all of them? Can you email one of them to me, john.dorlon@quest.com, without obscuring anything as you have done in your screen shots? Maybe also the results of those 2 queries from each database? (Exported to excel would be good)

Both the above queries return data in both databases.

OK, wait a minute...in your original post, you said that the refresh groups only existed in one database. Is it possible that they exist in both and there are no differences between them? That would explain the schema compare not showing either one.

Thanks

John

Well, foo. My original post was from production databases and they most definitely did not exist. So I created them in the target database so the mview refreshes would work (time sensitive, so I couldn’t wait).

But now I’m left with no other examples. As you correctly point out, if there are no differences then the compare result of zero different objects is valid.

I’ll see if it is reproducible on a dev box.