Oracle Invalid synonyms

My Oracle Public Synonyms are invalid. Is this can cause performance problems?
OranewAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
the first time you reference the synonym you may lose a few milliseconds while it resolves the object and updates the status back to valid but other than that, no.
Mark GeerlingsDatabase AdministratorCommented:
What happened to make them invalid?  Did you do a database version upgrade, or something else recently that made them invalid?
OranewAuthor Commented:
Appreciate your response on this but I wasn't aware when and how these public synonyms got invalid. I come to know when I queried one of the sql asked by oracle on one of the opened SR. There are more than 40k invalid public synonyms. Just to answer your question. .no ..not release upgrade but we do apply cpu patches quaterly
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

OranewAuthor Commented:
Also, these objects are frequently accessed but i am not sure if application will use synonyms or directly access the objects like tables or views. Also, to update that there are no users who directly logged in to production database. It is only through  online application
Mark GeerlingsDatabase AdministratorCommented:
You haven't told us anything about your application.  Some purchased applications use public synonyms and some do not.  Most newer applications do not use them now.  Do you have a purchased application, or a custom one?  If purchased, which one is it?
OranewAuthor Commented:
It is peoplesoft application
Mark GeerlingsDatabase AdministratorCommented:
I have no experience with PeopleSoft.  Maybe someone else can help you with public synonyms in a PeopleSoft system.

Here is a SQL query that can generate all of the commands needed to re-compile invalid synonyms.  This works in our Oracle11.2 system (which is not a PeopleSoft system) if the resulting commands are run when connected "as sysdba".

spool fix_syns.sql
select 'alter public '||object_type||' '||object_name||' compile;'
from dba_objects
where owner = 'PUBLIC'
and status = 'INVALID'
and object_type = 'SYNONYM';
spool off;

This will create a file named "fix_syns.sql" in your current directory.  Try one of two of these manually first.  If they work, run the script file as SYS.
slightwv (䄆 Netminder) Commented:
Peoplesoft used to access the objects directly.  Everything was owned by SYSADM and the app would connect to the database as that user.  So, public synonyms meant nothing.

To the question in general:
If the synonym is invalid it is likely because the underlying object isn't there any more.

If the app used a public synonym that couldn't be resolved then there would be bigger issues than an invalid public synonym.

I've worked on MANY different Oracle databases over the years.  I cannot think of any that didn't have some invalid objects.  Things get moved/renamed/etc... over the years and things like synonyms can be easily forgotten.


For example, create a table, create a public synonym.  Then drop the table.  The public synonym is still there but is INVALID.
drop table tab1 purge;
create table tab1(col1 char(1));

create public synonym ralph for tab1;

select status from dba_objects where object_name='RALPH';

drop table tab1 purge;

select status from dba_objects where object_name='RALPH';

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.