Oracle SYNONYM

Dear Sir,

after reading this: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

I still don't know what Synonym for ?

could you please give example on why we need it and how to use it ?

we have script to create  a table:

ALTER TABLE ADMGCUK.AFR_ADDR
 DROP PRIMARY KEY CASCADE;

DROP TABLE ADMGCUK.AFR_ADDR CASCADE CONSTRAINTS;

CREATE TABLE ADMGCUK.AFR_ADDR
(
  ADDRTYPE_ID   NUMBER(2)                       NOT NULL,
  CUSTOMER_ID   NUMBER(7)                       NOT NULL,
  WNP_ID        NUMBER(10)                      NOT NULL,
  ADDR_STREET1  VARCHAR2(40 BYTE),
  ADDR_STREET2  VARCHAR2(40 BYTE),
  ADDR_ZIP      VARCHAR2(10 BYTE),
  ADDR_TOWN     VARCHAR2(32 BYTE),
  ADDR_TEL      VARCHAR2(20 BYTE),
  ADD_FAX       VARCHAR2(20 BYTE),
  CONTACT_ID    NUMBER(4)
)
TABLESPACE GCUK_DENVER
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;





DROP SYNONYM USRGCUK.AFR_ADDR_Audit;

CREATE SYNONYM USRGCUK.AFR_ADDR_Audit FOR ADMGCUK.AFR_ADDR_Audit;


ALTER TABLE ADMGCUK.AFR_ADDR_Audit ADD (
  CONSTRAINT PK_AFR_ADDR
 PRIMARY KEY
 (ADDRTYPE_ID, CUSTOMER_ID)
    USING INDEX 
    TABLESPACE GCUK_DENVER_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE ADMGCUK.AFR_ADDR_Audit ADD (
  CONSTRAINT FK_AFR_ADDR_HASADDR_AFR_CUST 
 FOREIGN KEY (CUSTOMER_ID) 
 REFERENCES ADMGCUK.AFR_CUSTOMER (CUSTOMER_ID),
  CONSTRAINT FK_AFR_ADDR_ADDRHASTY_AFR_ADDR 
 FOREIGN KEY (ADDRTYPE_ID) 
 REFERENCES ADMGCUK.AFR_ADDR_AuditTYPE (ADDRTYPE_ID));

Open in new window


I can't see why we need that synonm..
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
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:
You never "need" a synonym.  

Often people use them simply as shortcuts so they don't have to type the schema before referencing an object.

However, if you had two objects both named AFR_ADDR you could use a synonym to alternate usage between them.  For example, if you were upgrading an application, you could create a new schema as a copy of the old one and make changes there.  Then, as you had completed testing of each component, change synonyms to point from the old schema to the new one, thus allowing for an easier phased implementation.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Often people use them simply as shortcuts so they don't have to type the schema before referencing an object."

thanks, it really means I odn't need that and the link just say a reference to the real object.

"However, if you had two objects both named AFR_ADDR you could use a synonym to alternate usage between them."

This is one thing that existing in our enviornment, why we can create 2x same object ?? this is exactly what we are having .

"For example, if you were upgrading an application, you could create a new schema as a copy of the old one and make changes there."

Once the synonym is created, another object create with the same structure created ? this i s not a pointer any more. Why don't simply create other table ? creating another table do not copy the respective data inside ?
0
sdstuberCommented:
why we can create 2x same object

lots of reasons:
 multiple development paths, each in their own schema
 multiple client installations, each with their own schema
 multiple applications that happen to have similar object names
    (for example, many applications have a "customer" table)
 multiple versions of an application.


Once the synonym is created, another object create with the same structure created ?

no, creating a synonym doesn't copy the object.  You use a synonym to differentiate between two already existing objects.
0
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.

marrowyungSenior Technical architecture (Data)Author Commented:
"
no, creating a synonym doesn't copy the object."'

what is inside? what is the different from the originate talbe ?
0
sdstuberCommented:
it's not a table at all. there is nothing "inside" it

a synonym is just another name for the something
it's a pointer
it's a reference

my home, my house, the building at my address - all different synonyms (name, descriptions) for the place where I live.  Giving a new name didn't create a copy of my house.  It's still just the same one structure, I simply call it different things.


create table test (n number);
create synonym test2 for test;
create synonym test3 for test;

test2 is a distinct object but it holds no data, it's simply points to test.

If I select from test2, I get test data.
If I insert into test2 the data gets put into test.
If I delete from test2 the data is deleted from test.
If I update test2 the data in test is updated.

Same for test3.  It's just another name for test.
0
marrowyungSenior Technical architecture (Data)Author Commented:
but you said:

"if you were upgrading an application, you could create a new schema as a copy of the old one and make changes there."

so creating one more for this also make change to the source table and it seems it don't give much help at all, right?
0
sdstuberCommented:
so creating one more for this also make change to the source table


no

I have schema 1

I create a copy schema 2
I make changes in schema 2

I create synonyms that point to schema 2 instead of schema 1.

Now my application uses schema 1 except for a few upgraded objects that now use schema 2 because the synonyms point there.  

You're over thinking this.

A synonym is just a name.

my_customers   could be a table, it could be view, it could be a synonym pointing to a table called "your_customers"

my code references my_customers by doing   "select * from my_customers"

using a synonym I can change my_customers to point to "his_customers"  and my code doesn't change but I get a whole new set of data.

same with a function, procedures, packages or any other object.

"calculate_account"  it could be a synonym pointing to an object in schema 1 (the old version)
it could be a synonym pointing to an object in schema 2 (the new version)

your code simply calls "calculate_account",  change the synonym and your application is upgraded
0

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
marrowyungSenior Technical architecture (Data)Author Commented:
"your code simply calls "calculate_account",  change the synonym and your application is upgraded "

so you means that this feature can provide separation between DB upgrade and application change?

so when new application create new table but the production application can still point to the old one, once then application done the DB side can help redirecting the call to new table ?

some kind of usage like view which DBA/developer can change the underlaying table name but the view still the same name ?
0
sdstuberCommented:
yes that's one use
0
marrowyungSenior Technical architecture (Data)Author Commented:
very good answer.
0
marrowyungSenior Technical architecture (Data)Author Commented:
what else the use of it?
0
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.