Home » Other » General » Database Diff shows diff on a table - but the tables are the same . . . . (SQL Developer 2.1.1.64 comparing two 10.2.0.3 db's on Windows Itanium)
Database Diff shows diff on a table - but the tables are the same . . . . [message #466972] Wed, 21 July 2010 17:46 Go to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
I have a table showing up as needing to have two columns modified on the target system, but the table is exactly the same on the target as the source. The table definitions below are simply cut/paste of the output from sqlplus from both systems. The table name has been omitted to protect the innocent Wink.

From the 'source' box:

Name Null? Type
--------
MPE_ID NOT NULL NUMBER(15)
CONTAINER_TYPE NOT NULL VARCHAR2(32)
PROC_ACTION NOT NULL VARCHAR2(128)
ULID NOT NULL VARCHAR2(128)
ULID_TYPE NOT NULL VARCHAR2(32)
ULASSIGN VARCHAR2(128)
ULASSIGN_TYPE VARCHAR2(32)
UNIT_MEASURE VARCHAR2(128)
UNIT_MEASURE_TYPE VARCHAR2(32)
DATA_DATE NOT NULL DATE
DATA_DAY NOT NULL NUMBER(2)
CHANGE_DATE NOT NULL DATE
ULID_2 VARCHAR2(128)
ULID_TYPE_2 VARCHAR2(32)
ULASSIGN_2 VARCHAR2(128)
ULASSIGN_TYPE_2 VARCHAR2(32)
FILE_ID NUMBER(15)
RECNO NUMBER(15)
RECSEQ NUMBER(15)
SPOOLREC_ID NUMBER(15)
EVENTNUM NUMBER(15)
STATE VARCHAR2(64)
RUNNUM NUMBER(15)

From the target box:
Name Null? Type
--------
MPE_ID NOT NULL NUMBER(15)
CONTAINER_TYPE NOT NULL VARCHAR2(32)
PROC_ACTION NOT NULL VARCHAR2(128)
ULID NOT NULL VARCHAR2(128)
ULID_TYPE NOT NULL VARCHAR2(32)
ULASSIGN VARCHAR2(128)
ULASSIGN_TYPE VARCHAR2(32)
UNIT_MEASURE VARCHAR2(128)
UNIT_MEASURE_TYPE VARCHAR2(32)
DATA_DATE NOT NULL DATE
DATA_DAY NOT NULL NUMBER(2)
CHANGE_DATE NOT NULL DATE
ULID_2 VARCHAR2(128)
ULID_TYPE_2 VARCHAR2(32)
ULASSIGN_2 VARCHAR2(128)
ULASSIGN_TYPE_2 VARCHAR2(32)
FILE_ID NUMBER(15)
RECNO NUMBER(15)
RECSEQ NUMBER(15)
SPOOLREC_ID NUMBER(15)
EVENTNUM NUMBER(15)
STATE VARCHAR2(64)
RUNNUM NUMBER(15)


The two columns showing up are the ULID_2 and the ULID_TYPE_2 columns - from the diff report:

alter table "UNIT_LOAD_TRANSACTION" modify ( "ULID_2" VARCHAR2(128) );
alter table "UNIT_LOAD_TRANSACTION" modify ( "ULID_TYPE_2" VARCHAR2(32) );


I have looked at both systems at this table for hours - I can't see what the difference is that the DIFF report thinks there is. Is this a bug in SQL Developer, known issue, or does someone see something I don't? (Wouldn't be the first time - sometimes you look at things too long . . . . )

I am running Sql Developer on 64-bit Windows 7. Although I am not sure that is contributing to this problem - I get the same result running Sql Developer on 32-bit Linux as well . . . . .
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #466973 is a reply to message #466972] Wed, 21 July 2010 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The table name has been omitted to protect the innocent Wink.
Really?

>alter table "UNIT_LOAD_TRANSACTION" modify ( "ULID_2" VARCHAR2(128) );
>alter table "UNIT_LOAD_TRANSACTION" modify ( "ULID_TYPE_2" VARCHAR2(32) );

(select column_name, data_type, data_length from all_tab_columns@DEV where table_name = 'UNIT_LOAD_TRANSACTION' and owner='<app_schema>'
MINUS
select column_name, data_type, data_length from all_tab_columns@PROD where table_name = 'UNIT_LOAD_TRANSACTION' and owner='<app_schema>')
UNION
(select column_name, data_type, data_length from all_tab_columns@PROD where table_name = 'UNIT_LOAD_TRANSACTION' and owner='<app_schema>'
MINUS
select column_name, data_type, data_length from all_tab_columns@DEV where table_name = 'UNIT_LOAD_TRANSACTION' and owner='<app_schema>')
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #466996 is a reply to message #466972] Wed, 21 July 2010 23:32 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Could it be that one is defined as varchar2(n byte) where the other is defined as varchar2(n char)?
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #467216 is a reply to message #466996] Thu, 22 July 2010 12:36 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
LOL - so I was 'trying' to be a little careful in hiding things like table names - not that it is earth shattering - just wanted to protect my customer's data. I am trying your suggestions now - thanks for the feedback Wink.
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #467230 is a reply to message #467216] Thu, 22 July 2010 15:10 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
I am not able to do dblinks, but executing the query on both shows the same size (DATA_LENGTH) for both ULID_2 and ULID_TYPE_2. If they were defined differently (n byte) (n char) I would think I would also see that in the DDL for the tables. Regardless, this looks like a bug - I appreciate your feedback.
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #474102 is a reply to message #467230] Thu, 02 September 2010 11:00 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
I am not sure if anyone cares, but on the outside chance someone else is having the same issue, know that it turns out that the DIFF functionality in SQL Developer is currently broken. All I can get out of Oracle is that the issues are known and will be fixed in the next release. No clear idea on 'when' the next release of SQL Developer will be. So, went trolling the internet and found a neat little freeware tool called TOYS (Tool for Organizing Your Schema) www.impacttoys.com It's a little rough around the edges, but it does a fantastic job of comparing two schemas and telling you EVERY difference between the two. I was able to use this to finish my task, and found it very useful. Highly recommended. FYI.
Re: Database Diff shows diff on a table - but the tables are the same . . . . [message #474104 is a reply to message #474102] Thu, 02 September 2010 11:20 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thanks for the feedback
Previous Topic: Diff between locally managed and Dictionary managed
Next Topic: Re: SIMTABLE Edit
Goto Forum:
  


Current Time: Fri Mar 29 04:45:14 CDT 2024