ORA-00942 when registering database

upliha

Hello,

when comparing a DB schema to a scripts folder it always aborts on step "Populating" when registering database one with "ORA-00942 table or view does not exist".
What am I missing? I'm connecting with the same user of the schema I'm comparing. The very user should have all privileges on its own objects.
So what table is missing? Does the connected user need special privileges or even DBA-privs?

Any advise is appreciated as I am evaluating this product and I'm confused that this first simple step doesn't work.

Regards
Uwe

upliha

It looks like Schema Compare has problems with tables using a IDENTITY columns.
I built up a schema with a simple table and it worked. Adding a table with GENERATED ALWAYS AS IDENTITY made Schema Compare come up with an error.
I'm using an Instand Client 12.1.0.2.0 on a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

So what now?

Regards
Uwe

Eddie D

Hi Uwe,

Thank you for your forum post and sorry to read that you have encountered a problem.

A support ticket has been created for you. The ticket created can you reviewed or updated via the support portal using this LINK.

Would you please enable the verbose logging as per this help document.

Repeat the comparison attempt, when the error occurs, please screen shot the error and locate the log file generated and either the support ticket via the support portal or attach the files to an email and send to support@red-gate.com quoting call reference #78294.

The user account specified for the Oracle connection, needs security permissions to several data dictionaries and I suspect the user account does not have these permissions. I will be able to confirm this once we have received the log file and screen shot requested.

Many Thanks
Eddie

upliha

Hi Eddie,

I found the "solution". The verbose log showed that the user needs the privilege to read system table SYS.OBJ$.
So when adding the SELECT_ANY_DICTIONARY priv everything works just fine.

You can close the ticket you opened. I wasn't able to login anyway since my email address didn't match with the password I use for this forum (in conjunction with my username).

Anyway, your advise with the verbose log helped a lot. Schema Compare works like a charm now.

Kind regards
Uwe

jgarec

Hi Eddie,

Could you update this part of the documentation : https://documentation.red-gate.com/sco4/requirements/permissions-required-to-use-schema-compare-for-oracle


Maybe you could describe the creation of a dedicated user with minimalist grants to allow SCO to work with identity columns. Is SELECT_ANY_DICTIONARY really necessary (which is ok as a workaround for us) ?

Best Regards,
Jérôme

Eddie D

Hi,
It's on my backlog of tasks to update the security permissions help document. When I get a quite moment I will update the document.

Many Thanks
Eddie

berny

Hi Eddie,

any chance that you will get this part of the documentation finally updated?

Regards,

Bernhard

David Atkinson

@berny - Sorry about this - it was updated a while back, but we forgot to fix the link above:
https://documentation.red-gate.com/sco4/requirements/permissions-required-to-use-schema-compare-for-oracle