Toad World® Forums

Compare Schemas in different versions of Oracle

I’ve an Oracle 11gR2 based schema which has been imported into Oracle 12c.

If I run a Database|Compare|Schema between schemas, Toad shows differences:

  • for Tables differs by inmemory, however there is not an option in Oracle 11
  • for Views differs by columns because of “BEQUEATH DEFINER” keyword

Do you have an adjustable compatible parameter for the generated "Show sync script ", or the generated script is the version of the Source schema by default?

.

I just generated a schema script from the SCOTT schema in 11gR2 (without storage options) and ran it on a 12.1.0.2 exadata db. I also created a view on both schemas. When I ran the compare, it didn’t find any differences other than storage options, which I can disable.

Are you actually using inmemory options for these tables in 12c? I think that’s why you see a difference. I can add an option to ignore those diffs, but it doesn’t exist as of now.

As for BEQUEATH DEFINER - I don’t see that coming in as a diff. My guess is on this one that you are getting a diff for something else (columns) but when you do the side-by-side viewer, you see BEQUEATH DEFINER in the 12c script but not the 11gR2 script, so it jumps out there. If the views are otherwise the same, they won’t get flagged as different. Try unchecking “column prec/scale” under “Views” in the schema compare.

Oh, or is the problem just that when a table/view exists in source (12c) but not target (11gr2), then the sync script has 12c features in it which causes errors when you run it in 11gr2? Hmmm…yeah, seems like the target script version ought to be 11gR2 in this case…

John!

I turned off column prec/scale option under Views, but I got the same result.

Two of my views were in invalid state in Oracle 11gR2 because of ORA-01417 error. Toad compares both side of Views but the invalid view have UNDEFINED column type under sys views. That could cause the difference.

Toad displays View columns| differ by datatype and Views|differ by columns, but there is no information about invalid object status.

For your last reply, yes, also this is a problem for me. The main development takes on 12c and from time to time I need a sync script for 11gr2 schema. That’s why I asked you about compatible parameter.

Off course, this Parameter option would be great for Database| Export | Generate schema script.

The option exists in Generate Schema Script. Look on the “Script Options” tab.

I’m looking at it for schema compare, but it will be trickier to get it to work there, especially in cases where there are multiple target schemas and they aren’t all the same version. So I might not be able to drop that one in so quickly. I’m looking at it though.

Oh, I haven’t seen that option. Great.

Thanks for the others.

OK, next beta: Storage clauses and view scripts in sync scripts will be compatible with the target DB version. You don’t have to set it anywhere in the GUI. I also fixed the bug about view’s status not showing as a diff. Give it a try next beta and let me know. There may be some other places in sync scripts to fix but it will be easy to do.

I’ve found other one, for example SEQUENCES: see more at oracle-base.com/…/session-sequences-12cr1. These keyword are not applicable in 11gR2 environment.

Thanks.

John,

What about this situation?

oracle-base.com/…/default-values-for-table-columns-enhancements-12cr1

Can you remove DEFAULT clause and give some warning to the user in the sync script?

For sequences, Toad identifies SESSION/GLOBAL as a difference, but that keyword doesn’t appear in the sync script unless the target is 12c or newer.

For default values, I think the error message in this case is a pretty good warning. Anything else might go unnoticed. This one seems a bit more sensitive and it should require the user to decide what’s best, since it could ultimately affect table data.