Schema compare default collation difference

Toad for Oracle
1

When comparing two Oracle schemas, one in Oracle Cloud Autonomous Transaction Processing database (18c 18.0.0.0.0), the other in Oracle Cloud Virtual machine oracle database (18c 18.0.0.0.0), Toad for Oracle schema compare finds differences of "default collation" on tables and views. SYS_Context for the USERENV SESSION_DEFAULT_COLLATION is null in both instances.

The sync script generates Alter Table ##### Default Collation ; which oracle rejects because it does not include a collation option. Plus, both instances/schema appear to have Null for default collation.

I tried unchecking the "Compare Storage Clause" options in the schema compare to no effect.

I google searched, but could not find any references to this issue.

I am using Toad for Oracle 13.1.0.78 and an update check says this is most current version.

Assistance please
Thanks
Joe

2

I believe this will be fixed in 13.2 (which is due out in a few weeks), but so I can be sure....

If you run "Select * from user_tables" in both DBs, what do you see in the DEFAULT_COLLATION column on each DB?

3

Both have "USING_NLS_COMP"

4

And this comes up as a difference on every table? or is just included on tables that have some other difference?

5

and it comes up on every view as well

6

Looking at Oracle parameters, does one DB have max_string_size = extended and the other have standard?

to check:

select value
from v$parameter
where name = 'max_string_size'

7

The virtual machine instance is STANDARD the ATP instance is EXTENDED

8

OK. I can reproduce that and unfortunately it still exists in 13.2. I'll log this to have it fixed for 13.3.

If you can make both DBs have EXTENDED, Toad 13.1 will not give that false positive difference. If you google on how to change that param, you'll find steps. It's easy to do, but you can't set it back once you've changed it. This setting also makes VARCHAR2 columns able to store up to 32K.

The other option is do a regex search/replace in the sync script to comment out the statements with alter table X default collation USING_NLS_COMP;

9

ok, thanks for your assistance and quick reply. Is there an ETA for 13.3?
Joe

10

No. 13.3 is not even in beta yet. 13.3 betas will likely begin shortly after 13.2 becomes available. I should be able to have this fixed in the 13.3 beta. I will post back here when that happens.

Update 12/9/2019: Fixed in Toad for Oracle 13.3 Beta

1 Like