Schema Compare "NO_COMMON_DATA"

Brian.Lieb

Hello, I don't know what happened, but recently someone made a change to a development database that cannot be checked in to source control, or compared against other databases. In fact, the error causes schema compare to not work at all against the specific schema.

First, let me give you the background. When comparing an unchanged schema with the offending instance we get an ORA-00942 error. This usually means the user doing the comparing does not have access to a table being compared. However, in this case that is not the case. The process that is in place, including the Schema Control project has been in place for several years and has never given this error.

So, I got this into a controlled environment and ran schema compare and then looked at the SQL from v$sql in the oracle database that was causing the error and found this query:

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS FROM NO_COMMON_DATA(SYS."INT$DBA_COL_COMMENTS") "INT$DBA_COL_COMMENTS" WHERE "INT$DBA_COL_COMMENTS"."COMMENTS" IS NOT NULL AND "INT$DBA_COL_COMMENTS"."OWNER"='<SCHEMANAME>'

Where <SCHEMANAME> is the oracle schema being compared.

If I take this query out, and run the query in SQL Developer as the user I am using to do the compare, lo and behold, I get the ORA-00942 error.

That same user is, however, able to:

select * from SYS."INT$DBA_COL_COMMENTS"'

The user is also able to:

select * from NO_COMMON_DATA(user.table);

At least to quite a few tables, I haven't tried every view and table in the schema, but the first 15 I tried worked.

Finally, the initial query:

SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS FROM NO_COMMON_DATA(SYS."INT$DBA_COL_COMMENTS") "INT$DBA_COL_COMMENTS" WHERE "INT$DBA_COL_COMMENTS"."COMMENTS" IS NOT NULL AND "INT$DBA_COL_COMMENTS"."OWNER"='<SCHEMANAME>'

Can be run as SYS on the development database and does return rows, but I have no idea what to do then.

This seems related to the NO_COMMON_DATA function, which is something I know nothing about and can find very little information about in online.

Anyway, this is unfortunate because it is part of a rather large release, that seems to be working fine in development, but I am unable to get committed to Source Control, and therefore we are delayed in getting it to testing. Which delays our production release schedule.

I am running:
Schema Compare for Oracle v. 3.1.5.1321
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Can someone tell me either,

a) What NO_COMMON_DATA is and why a schema owner of the schema being compared would not be able to run the query noted?
or
b) What may have changed that would cause this query to be run now, when it was working until yesterday.