Toad World® Forums

Compare schema issues

I’m comparing an 11g schema to a 12c schema. We’ve expanded all TIMESTAMP columns from 6 to 9.

I’ve uncheck “Compare column scale, precision, length” but all TIMESTAMP column show up as differences. I assume this is because the DATA_TYPE column values have the scale values (6) or (9) embedded, in addition to having the value in the DATA_SCALE column (for whatever reason). Can the TIMESTAMP scale be eliminated from comparisons when box is unchecked?

Also, my partitioned tables are showing up as differing by “high value, indexing”. These tables do contain timestamp columns, but the List partitioning key is a NUMBER(38) column and the list values are the same. When I do a Show Differences Detail, the table structure differs (TIMESTAMP scale, BYTE/CHAR for VARCHAR2), but the partition clauses show no differences.

And third, if I select Synonyms, Private, for schema’s objects - this schema has 1291 private synonyms defined on it’s objects (mostly for the packages) - the compare indicates 664 differences. When I click to show difference details, there are no differences. When I show the sync script, it is dropping one user’s synonym and adding that synonym to another user. I’ve verifies via several queries that there are no differences in private synonyms pointing to object in my schema.

And, all multi-line comments show up as differences. No comment differences in the Show Differences Detail, just table datatype differences.

Sorry for the delay, Dale. I am working on some schema compare stuff right now and this is on my list. Will get back to you soon.

Hi Dale,

I just tested this and could not reproduce it. Is it possible that you have CR+LF for line breaks in some of the comments and just CR in the other? That would explain it. You can check that with this query:

select dump(comments)
from dba_tab_comments

I think can ignore these differences…I’ll make that change.

Another possible difference is trailing whitespace at the end of one of the lines.

Hi Dale,

Sorry for the delay and thanks for your patience.

I don’t think I should make the scale/precision/length option apply to timestamps (because, as you pointed out, Oracle decided to include that as part of the name, AND because there is also the extra element of with/without (local)timezone. So I added a “compare timstamp precision” option right underneath the other option. It’ll default to TRUE.

The Indexing option in partitions came along in 12c. So for Oracle 11, we have NULL for this property. So Toad is seeing that NULL on 11g and treating it as different than ON in 12c. I think because ON is the default in 12c, we should treat these as the same. If you had OFF for indexing in 12c, that should be a difference since OFF is non-default. This is fixed for next beta.

“High Value” just kind of snuck in there with “Indexing” and is a false alarm. This is fixed too.

Regarding “Synonyms, Private, for Schemas’ Objects” - I tried to reproduce this but could not. Could you give me a script that I can use to reproduce it?

John, thank you so much for ability to ignore TIMESTAMP precision. That is very useful during our extended migration.

One issue is that when the Schema compare Status indicates Done (or Done. Diff limit exceeded), the schema compare continues to chug for several minutes. I eventually get a toad error popup something like 11001: [11001] Host not found. Then the compare is really done and I can click on schema compare line to display the Difference Detail. If I click on the schema compare line before I get the error popup, I get a Difference Detail with no detail, and I’ve found no way to refresh the details other than redo the compare.

Partition table differences are fixed - thank you.

Synonyms and comments are still a problem. I’ll investigate further and get back to you.

You’re welcome, Dale. It sounds like it is timing out trying to send you results by email. Do you have that configured for output? I’ll make it skip that part if you cancel.

I didn’t change anything regarding Output. I just checked the Output options and none of the email boxes is checked.

Was this an action that was created with output defined the old schema compare window?

If so, on the first step, in the “Target Schemas” box, you’ll have more columns visible than just “Connection/file” and “Schema”.

If not, can you copy/paste the action out of automation designer and email it to me?


I don’t recall ever changing output related parameters in the old schema compare. Speaking of “old” schema compare, I did find it interesting that the 12.11 new schema compare is now the 12.12 legacy schema compare, and the 12.11 legacy schema compare is now the 12.12 new and improved schema compare. I tried the “other” compare once or twice but found it less intuitive (or maybe just less familiar), so never went back to it.

Here’s the AD copy/paste you requested:

object TarCompareSchemas
Enabled = True
ID = 4126
ParentID = 0
UserName = ‘Compare Multiple Schemas12’
ActionSetID = 0
LastRunDate = 42941.38582293981000000
EntitySetString =
‘Check Constraints, Cluster, Materialized View Comment, Materiali’ +
‘zed View Column Comment, Table Comment, Table Column Comment, Vi’ +
‘ew Comment, View Column Comment, Constraint, Context, Type, Data’ +
‘base Link, Dimension, FK Constraints, Index, Java Source, Librar’ +
‘y, Outline, Package, Package Body, Partition List, PK Constraint’ +
‘s, Policy, Policy Group, Private Synonyms, Procedure, Program, Q’ +
‘ueue, Queue Table, Refresh Group, Sequence, Materialized View, M’ +
‘aterialized View Log, Synonym, Table, Trigger, View, Function, G’ +
‘rant, Unique Constraint, Schedule, Scheduler Job, Audit Policy’
OptionSetString =
‘Comp Cache, Comp Compression, Comp Tablespace, Ignore Con Name D’ +
‘iffs, Ignore Case in PLSQL Comparisons, Include Schema Name, No ’ +
‘Comp Seq Minval, No White Space, Null=Blank for col defaults, No’ +
’ Double Quotes, Deps in Script Comments, Script Comments, Inc. S’ +
‘et Define Off, Sort, Storage, Drop/Create Seq, No Byte/Char, Log’ +
‘ging, NoLogging, Monitoring, NoMonitoring, Parallel, NoParallel,’ +
’ Cache, NoCache, Compress, NoCompress, LobParams, PrivObjSyns, C’ +
‘ompRowMovement, ResultCacheOn, ResultCacheOff, FlashCacheOn, Fla’ +
‘shCacheOff, CellFlashCacheOn, CellFlashCacheOff, InMemoryOn, InM’ +
‘emoryOff, ILMOn, ReadOnlyOn, NoTimestampPrec’
Schema = ‘FIRM_COMM’
ObjNamesLike = False
LikeStr = ‘A%;B%’
DiffLimit = 1000
OutputSourceSnapshotFile = False
OutputTargetSnapshotFiles = False
OutputComparisonResultDetailFiles = False
OutputComparisonResultSummaryFiles = False
OutputComparisonResultSyncFiles = False
OutputComparisonResultDetailEmail = False
OutputComparisonResultSummaryEmail = False
OutputComparisonResultSyncEmail = False
ManyManyRuntimeSchemaMatching = False
ManyManyOnlySchemasWithObjects = False
RunData = {
Logins = {
SchemaItems = {

Thanks. I found the problem and It should be fixed for next beta.

John, the multi-line comment differences are due to Oracle’s export to ddl trimming whitespace from the end of lines. It’s not ALL our multi-line comments, but for some reason we have a lot of these.

So to reproduce:

  1. make comments on a table with trailing whiespace

  2. “oracle export ddl” (is this sql developer?), run that in another schema.

  3. compare schema 1 to schema 2


If I were to introduce a new feature to ignore trailing whitespace on comments, I’m thinking it should be an option. But we are in “bug fix only” mode in the beta cycle now, so this will have to wait until 12.13 or whatever the next version number is.


Sorry, the “oracle export ddl” is actually datapump. expdp object(s) and use impdp SQLFILE= to extract the DDL from the export file. I’m not sure it’s worth the effort for this special case, since it’s really an Oracle issue, and odds of others doing this are probably slim.

I have a more pressing matter. Views whose underlying tables contain a VARCHAR2 column, which changed from BYTE to CHAR are showing up as differences. The differences list only the 1st VARCHAR2 column in each view but it’s the majority of our views. I do have the Compare BYTE/CHAR box unchecked.

Uncheck “Column Prec/Scale” under Views and the byte/char sensitiviity should go away.

That did it - Thanks. Seemed strange that only the 1st VARCHAR2 column showed up as a difference.

FYI, when view diffs show up, clicking ShowDifference Detail displays BEQUEATH DEFINER as a difference/addition on the 12c side. The Show Sync Script does not have the BEQUEATH line.

For the columns, it jumps out as soon as it finds a difference, since the whole view has to be rebuilt at that point anyway.

We don’t specify BEQUEATH DEFINER since it is the default.

I’m assuming you had BEQEATH CURRENT_USER for one view and BEQUEATH DEFINER for the other, is that right? Or are you saying that it should not have been a difference at all?

So BEQUEATH DEFINER should not be showing up in the Differences Detail?