Compare schema issues

Toad for Oracle - Beta
1

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.

2

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

3

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.

4

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
where…

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

5

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

6

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?

7

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.

8

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.

9

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

10

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?

Thanks.

11

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’
Connection = ‘DARDBA@DEVTOSS_NEW’
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 = {
545046300A54617252756E44617461000A416374696F6E4E616D65061A436F6D
70617265204D756C7469706C6520536368656D617331320B416374696F6E5365
74494402000D416374696F6E5365744E616D65060D416374696F6E2052656361
6C6C024944031E10084661696C4D6F6465070A666D436F6E74696E7565075275
6E44617465050090CC4AC562BDA70E400752756E557365720609726F65736368
6C6579104D657373616765732E537472696E6773010655372F32352F32303137
20393A31353A333520414D3A2020436F6D70617265204D756C7469706C652053
6368656D617331322028436F6D70617265204D756C7469706C6520536368656D
61732920737461727465642E063B372F32352F3230313720393A32333A303520
414D3A20204552524F523A2031313030313A205B31313030315D20486F737420
6E6F7420666F756E640006537461747573070C617352756E4661696C75726500
00}
Logins = {
545046300A544C6F67696E5265637300054974656D730A200100005450463009
544C6F67696E526563000B4175746F436F6E6E6563740805436F6C6F7204FFFF
FF0009436F6E6E656374417306064E4F524D414C084661766F72697465080447
55494406267B43414343373531332D373641312D343535352D393835322D3037
343333343342304244307D0B4C617374436F6E6E6563740500D0D0B41386BCA7
0E40064D6574686F640200064E756D62657202000A4F7261636C65486F6D6506
25433A5C4F7261636C655C70726F647563745C31312E322E305C636C69656E74
5F7836345F311052656C6174697665506F736974696F6E023E0F53657373696F
6E526561644F6E6C79080C5361766550617373776F7264080653657276657206
0B444556544F53535F4E45570455736572060644415244424100000000}
SchemaItems = {
545046300F54536368656D614974656D4C69737400054974656D730ACC000000
545046300B54536368656D614974656D0013536F75726365436F6E6E65637453
7472696E67061244415244424140444556544F53535F4E45570E536F75726365
50617373776F7264060F7265616473746F776E3230313730360C536F75726365
536368656D6106094649524D5F434F4D4D0D436F6E6E656374537472696E6706
0E44415244424140444556544F535306536368656D6106094649524D5F434F4D
4D0C456D61696C53756D6D617279080C456D61696C44657461696C73080B456D
61696C5363726970740800000000}
end

12

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

13

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.

14

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.

-John

15

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.

16

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

17

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.

18

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.

19

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?

20

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