Oracle: synonym table DDL refresh.

Hello all,

If  the DDL of a table is altered then would its  synonym table's DDL also alter automatically?  Regardless if private or public?
Or, should someone manually refresh the DDL?
thx

JohnE
John EsraeloDatabase / SQL DeveloperAsked:
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:
Altering the object a ddl points to does not change the ddl of the synonym itself.


Maybe I'm not understanding your question.  Do you have a specific example you're concerned about?
John EsraeloDatabase / SQL DeveloperAuthor Commented:
You have understood the question perfectly.

One of the programmers had created a syn table based on another.  He went ahead and added 2 columns to the main table and he was expecting to see those 2 columns in the syn table , but, those 2 did not show up.
John EsraeloDatabase / SQL DeveloperAuthor Commented:
And, of course his argument is that those 2 columns should have appeared in the syn table :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
well, if you query a synonym pointing to a table that has 2 new columns, you should see the 2 new columns (assuming synonym is valid and permissions allow of course)

so, yes, the 2 columns should have appeared when viewed by sql

but no, the 2 new columns are not part of the synonym's ddl.  They still only exist in the real table.


Maybe the problem is due to jargon.
 There is no such object as a "synonym table".  An object can be a synonym or it can be a table (or other object.)  It can't be both.
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I don't think there is any ambiguities in here.
The object type of the syn table is   synonym and the original table type is a table

So, besides the permission, what other possibilities are out there they have cause the 2 columns not to show on the syn side ?
johnsoneSenior Oracle DBACommented:
There is only one way that I can think of that what you are describing is happening.  The synonym isn't pointing to what you think it is.

I would check DBA_SYNONYMS to see where it is pointing and be sure that was the object that was changed.
Kanti PrasadCommented:
Hi

One option is tp try  
If it is a private synonym RENAME synonym  TO synonymtest;
if it is public one drop and recreate it;
flow01Commented:
And /or check
select * from DBA_OBJECTS WHERE OBJECT_NAME = 'XXX'
where XXX is your synonym name and table name
to get information on the existing objects and the schema they are in
awking00Information Technology SpecialistCommented:
You might also try -
select dbms_metadata.get_ddl('SYNONYM','SYNTABLENAME','SYNOWNER') from dual;
Where SYNTABLENAME is the name of what you refer to as the syn table and SYNOWNER is PUBLIC for a public synonym or the SCHEMANAME that created the private synonym.
This should return something like
CREATE OR REPLACE [PUBLIC] SYNONYM "SYNTABLENAME" FOR "SCHEMA"."TABLENAME"

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.