Toad World® Forums

Schema Compare does not report correct number of objects


#1

Hello,
as a test I just compared 2 identical SCOTT schemas between 2 db instances and I found that the Total number of objects for most of the object types is always reporting 0 if there are no differences.

As I can see only the total number of Procedures and Materialized views is correct.
For the other object types, I see the correct total number only if there is at least one difference for that specific object type.

Thanks,
Paolo


#2

Hi Paolo,

Would you be able to send us a screen shot of this error so that we can see what it is displaying.

Thanks,

Tim


#3

Hi Paolo,

Can you please check to see that you have all the ‘condition filters’ turned on (see attachment).
If some are turned of, you will experience this outcome.
I have accidentally turned them off, and experienced your problem.

Jaime
condition_filters.jpeg


#4

Jaime,
I used the SCOTT schema, because it’s standard and you can easily check it. Just install it on your instances and run the Compare against it.

What I can see is that for sure the number of indexes is wrong (reports 0 instead of 3) and all the condition filters are on.

Check the attached picture with the results of the following query on the SCOTT schema:

select a.owner, a.object_name, a.object_type, a.status, a.temporary, a.generated
from all_objects a
*where a.owner = ‘SCOTT’
*

SCOTT AFTER_INS_UPD_ON_EMP TRIGGER VALID N N
SCOTT DEPT TABLE VALID N N
SCOTT EMP TABLE VALID N N
SCOTT EMP_SNAPSHOT TABLE VALID N N
SCOTT EMP_SNAPSHOT MATERIALIZED VIEW VALID N N
SCOTT GIVE_RAISE PROCEDURE VALID N N
SCOTT MLOG$_EMP TABLE VALID N N
SCOTT RUPD$_EMP TABLE VALID Y N
SCOTT SYS_C0094352 INDEX VALID N Y
SCOTT SYS_C0094353 INDEX VALID N Y
SCOTT SYS_C0094355 INDEX VALID N Y
capture26-2-2007-18.02.42.jpeg


#5

Hi Paolo,

In it’s current design, Schema Compare does not show indexes which are used to enforce constraints. We handle the indexes which are used by constraints by including the USING INDEX clause with our ALTER TABLE…ADD CONSTRAINT commands in our tables. This can be verified by running the following statement from SQL Nav:

SELECT *
FROM ALL_INDEXES I
WHERE OWNER = ‘SCOTT’
AND NOT EXISTS (SELECT ‘x’ FROM ALL_CONSTRAINTS C WHERE C.OWNER = I.OWNER AND C.INDEX_NAME = I.INDEX_NAME)

Whilst this may seem like we have missed something, if you perform a compare & synch of two full schemas, you will end up with the same number of indexes on both the target and source databases. The only difference will be that some of the indexes may not have the same name.

For example, if on the source you create a table by executing something like:

CREATE TABLE CUSTOMER

CREATE UNIQUE INDEX UI_CUSTOMER ON (CustomerID)…

ALTER TABLE CUSTOMER
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (CustomerID)

And on the target create the same table by excluding the CREATE UNIQUE INDEX statment, the structure of the table, indexes and constraints is the same. The only difference will be the name of the index. On the source database, the unique index will be called UI_CUSTOMER, whereas on the target database, the unique index will be called PK_CUSTOMER.

Tim


#6

Sorry Tim,
you are right, it makes sense to keep them like that.

Paolo