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?
 
sdstuberConnect With a Mentor Commented:
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.