Howdy,

In 13.2.0.59, I’m doing a Compare Schemas and it’s coming up with false positives on columns defaults differing. It seems like it’s only on columns that have a default value when it’s the last column in the table. 13.1.0.78 GA works fine.

After my initial compare, I filtered to just a single table and it still flagged the last column as differing on default value between the two schemas, even though they are exactly the same.

I can provide more troubleshooting info if needed – wanted to get this posted before the weekend…

Thanks!

Hey Rich -

Send some details when you can. For me, the last column of the table is not triggering a false diff on data default.

-John

I am seeing something similar in the .64 beta. Under “objects which differ - columns” are three columns from three different tables, all “differs by” default. The columns with defaults are not necessarily the last column in the table and the difference details do not show any differences between the tables.

I just created a pair of tables like this, then compared those schemas and got no differences using the .64 beta.

create table comp1.tables as select * from dba_tables;
create table comp2.tables as select * from dba_tables;

ALTER TABLE COMP1.TABLES MODIFY(PCT_FREE DEFAULT 27);
ALTER TABLE COMP1.TABLES MODIFY(DEGREE DEFAULT ‘x’);
ALTER TABLE COMP1.TABLES MODIFY(LAST_ANALYZED DEFAULT sysdate);

ALTER TABLE COMP2.TABLES MODIFY(PCT_FREE DEFAULT 27);
ALTER TABLE COMP2.TABLES MODIFY(DEGREE DEFAULT ‘x’);
ALTER TABLE COMP2.TABLES MODIFY(LAST_ANALYZED DEFAULT sysdate);

I suspect there is some actual difference that just isn’t apparent.

SInce DATA_DEFAULT is a LONG column, you can’t use DUMP on it to see the “real” data inside it, but you can convert LONG to CLOB in a “Create Table as Select” statement, then use DBMS_LOB.SUBSTR on the created table, like below.

create table default_vals as
select owner, column_name, to_lob(data_default) data_default_clob
from dba_tab_columns
where owner in (‘COMP1’, ‘COMP2’)
and table_name = ‘TABLES’
and default_length > 0

select owner, column_name, data_default_clob, dump(dbms_lob.substr(data_default_clob, 2000))
from default_vals
order by 2, 1

I created a default_vals table per your suggestion for the three tables identified in my schema diff and eyeballed the text value vs the hex value of the clob column.

There were three different representations for the text value ‘N’:

hex: 27 00 4E 00 27 00 0D 00 0A 00
hex: 27 00 4E 00 27 00
hex: 27 00 4E 00 27 00 0A 00

There was only 1 row in the table with default text value ‘Y’:

hex: 27 00 59 00 27 00 20 00

well, given what was in the data dictionary, that may be a bug. I mean…if your default value includes a line feed…do you want Toad to decide that you didn’t mean to do that?

Do you know how those line feeds got in there? Could it have been as with my EMP example?

if your column were VARCHAR2(3) instead of VARCHAR2(1), I think you’d get line feeds in there as part of your default value. So “functionally equivalent” seems a but fuzzy.

In 11.2.0.4, creating a table with a column definition such as …

ACTIVE_YN VARCHAR2(1 BYTE) DEFAULT ‘Y’ NOT NULL

… results in dba_tab_columns.data_default containing not only ‘Y’ but also all of the trailing spaces up to ‘NOT NULL’

Let's see if blockquote preserves the spaces:

ACTIVE_YN VARCHAR2(1 BYTE) DEFAULT 'Y' NOT NULL

It doesn't, but just assume that there are several spaces between 'Y' and NOT NULL in my example.

I definitely see how a mishmash of extra spaces and line feeds is best left ignored. And certainly no sane person would want to add the spaces or line feeds, right? (famous last words)

My only hesitation is that if the source schema is “clean” (no junk chars) and target schema is “dirty” (junk chars) , some users might cry “bug” that we aren’t syncing to remove the junk chars. Or maybe they’ll be like you with a “no real diffs, leave it alone” attitude.

I’m thinking this isn’t worthy of an option, either, but it would be nice to solve.

I’ll play with this tomorrow. If the trailing whitespace is never added to the table, then I think it’s safe to ignore.

I created a similar not null column with default value for a number(12) and the results were similar. There are no single-quotes (it is a number), but there are trailing spaces in data_default.

The database must rtrim this value before using it as the length of the string in data_default including the spaces exceeds the length of the column’s datatype (in my case varchar2(1) and number(12)).

I made a change recently to the query that pulled data defaults. I Inadvertently removed a trim!

Should be fixed next beta.