Compare Schemas Issue

We have a schema whose only object is a database event trigger. When I start a Compare Schema operation, I cannot select that schema for comparison. Is this by design, or is it a bug in that tool?


The schemas listed in a schema dropdown are global in Toad. You may have to change the schema filter like this:

Where is that, John?

Right-clicking and changing settings in any schema dropdown will affect all of them.

In the screen shot above, I was in the schema compare window. I right-clicked in the "Target" section, chose "Add", then right-clicked on the schema dropdown in the dialog that appeared.

You can also right-click on the schema dropdown here:

Yeah, I already have that set, but the problem is not that I cannot SEE the schema but that I cannot check it. In the image below, I cannot check the highlighted box of the desired schema, and the target field for this schema is blank even though it does exist in the target database.



Oh, you've got it in multiple schema mode. I'm happy to see someone using that. :slight_smile:

ok, shooting from the hip here...

You should be able to edit the grid below if you uncheck "Match Schemas Automatically". If we're talking about the first row, You'll have to select a target schema, then you can check the "Include" column.

Hmm actually, I see that list of schemas is produced by this SQL:
select distinct owner username
from sys.DBA_OBJECTS
where owner not in (...a list of built-in schemas...)
and owner not like 'APEX%'
order by 1;

I guess that your schema has no rows in DBA_OBJECTS. I can fix that for next version, but you can work around by creating a dummy table or something. Hopefully that's doable.


Another workaround:

Use Compare Databases instead of Compare Schemas
Check Triggers as the only object type.

Replying to your previous post:

No sir. I still cannot check the box for that schema. The schema in question IS returned by your query. I use a query very similar to yours to get my list of users:

select unique(o.owner) "SCHEMA"
from sys.dba_objects o
where owner not in (select username from sys.dba_users where oracle_maintained = 'Y' or common = 'YES')
and owner != 'PUBLIC'
order by 1;

Either way, the affected schema IS in the list. This schema's only row in DBA_OBJECTS is for that database event trigger. It also has object grants, roles, and system privileges. I need to be able to compare all that.


Oh! Try unchecking the "only schemas with objects" box.

Well, that does let me check the box for that schema now so that I can move forward, but somehow the other still feels like a bug. It shows me a schema that it identified as owning objects but then would not let me select it to include in a compare operation.

Cheers, my friend. Thank you so much for all your hard work supporting TOAD.

Hi Russ,

I agree, it does seem like a bug. But I can't find the "unique(o.owner)" anywhere in our source. That isn't something you customized in, is it?

You're welcome. Thanks for reporting problems!


That is the query I have long used to get a list of my schemas. It is quite similar to yours, but I typically use unique(...) vs distinct(...), but there is no internal difference. The functions do the same thing under the hood. I do use a subquery instead of a hard-coded list for identifying the users to exclude ORACLE_MAINTAINED='Y' and COMMON='YES'.


Oh, gotcha. That's your SQL. I misread.

I'll check it out.

One thing that I would like to be able to do is compare multiple schemas using snapshots. I tried this by first Generate Schema Script of multiple schemas of two PDBs and then Compare Schemas selecting Snapshot. Unfortunately, this does not allow me to select multiple snapshot files. I can compare multiple live schemas, but I cannot compare multiple schemas as snapshots. This would be a great addition to the Compare Schemas feature.

BTW, John. Are you the only Quest technician working the Toad for Oracle forum?


Hi Russ,

Did you try the "Specify Folders" option? I think if you put one DB's snapshot files in one folder and the other DB's snapshot files in the other folder, Toad will match them up by filename.

More and more of Toad's code has become my responsibility over the years, so I guess that's why I'm the most active. I'm sure you've noticed others (Michael, Brad, Andre, Gary) step in from time to time. And we just hired a new developer, to start in a few weeks).

1 Like

I will try that specify folders.

Compare Schemas with Snapshot does not offer a Specify Folders option in 15.1. Bummer.


Make your settings like below. Then you can select the source/target folders. This is a screen shot of version 15.1.

Maybe you wanted to compare 1 live schema or 1 snapshot file to a folder full of snapshot files? I don't think there is a way to do that.

I need folder to folder only. I have to generate one snapshot in a restricted network and another in a separate 'less' restricted network and then bring the snapshots down to my workstation for a series of comparisons.

Well, bummer. Generate Schema Script created snapshot files by the formula [schema]@[CONNECT STRING].SDXML. Since these are two different connections having different connect strings, the names do not match between the two folders. Compare Schemas using multiple files in snapshots requires the SDXML file names to match, which makes sense but defeats me unless I go through and rename all the files manually.


[CONNECT STRING] above is actually the respective TNS aliases.