app has its own system table, conflicts with oracle system table

[p7]

hi all,

We have a database that we maintain for our application, the dbas are upgrading to 10g for us but the found that a table called system
That our appuser owns and since oracle owns its own table called system they say its got to be dropped. But it we drop it the
Database wont work (here are some email comments from the vendor and our dba), also is there another way to upgrade If we don’t use export/import?

What do u think?

thx in advance

1. from our dba:

have created META10g on HPCB3A. We are having export problems with your databases. I am working with Oracle on that. I need you to drop the following synonym.

SQL> select owner,table_owner,table_name from dba_synonyms where synonym_name
='SYSTEM';

OWNER TABLE_OWNER TABLE_NAME

PUBLIC MVRUSER SYSTEM

As per Oracle, You can’t have a Synonym, or table_name named SYSTEM owned by any other users then SYS or SYSTEM. Therefore user MVRUSER must drop the table_name SYSTEM and SYNONYM. I would advice you to do this in METADEV and then when we resolve the issues in METADEV then we will do this in METAVWER also
Let me know when it is done. Give it your priority.

2. from the vendor:

Looking at the email, the issue seems to be our table called ‘SYSTEM’ is owned by ‘mvruser’ account which Oracle doesn’t like. They should be able to change the ownership of the that table to SYSTEM or SYS account then give ‘mvruser’ account full access to that table. I don’t think our system cares if mvruser owns the table or not. ‘mvruser’ account just needs full rights to that table which the DBA should be able to change and hopefully that should fix the issue.

3. from oracle support:

In cases similar to yours that I dealt with in the past there are not too many options.
Given that it invalidates certain packages in the database, we typically ask
that the object be dropped or renamed.

4. from the vendor:

Unfortunately there is no way to rename or to drop that table since the application depends on it. Only thing I can suggest is to change the ownership of that table and give it to Oracle sys or system account. I am know we have customers running our apps under Oracle 10G so I am sure there is a way to make it work but I haven’t heard this problem from any of the 10G customers.

[Patrick Wallek · Accepted solution]

It sounds like you need to get your vendors support and Oracle support on the phone together so they can hash this out.

There's no reason you need to be in the middle of this.

If the vendor has other customers running their apps under Oracle 10g, they need to figure out how it is done and let you know.

[Eric Antunes]

Hi Pasquale,

I suppose you are running the apps in Oracle 9i or 8i because there are no SYS or SYSTEM objects named SYSTEM in those versions.

In my opinion, you must enforce you apps vendor to be compliant with Oracle 10g: they must change the name of that table themselves if they want to be compliant...

Best Regards,

Eric Antunes

[klb]

Seems to me that all you need to do is get rid of the public SYNONYM called SYSTEM.

I've had vendors claim that they needed a public synonym for every one of their tables, but it's usually not necessary and always a bad idea!

Just get rid of the synonym and you'll probably be OK

[Wim Rombauts]

I would go with dropping the public synonym. You don't drop any data with it, and you can allways recreate it after the upgrade if this public synonym is really necessary.

I don't see how any table name in an application schema could be an upgrade issue. Because you have a public sysnonym on this SYSTEM table, SYS sees this table as if it is in it's own schema, and that is probably the only issue.

If your application uses other database accounts than MVRUSER while running, you can also create private synonyms in every specific schema that needs to access your application.