asked on

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..

Sean Stuber

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.

marrowyung

ASKER

"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 ?

Sean Stuber

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.

marrowyung

ASKER

"
no, creating a synonym doesn't copy the object."'

what is inside? what is the different from the originate talbe ?

Sean Stuber

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.

marrowyung

ASKER

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

"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 ?

Sean Stuber

yes that's one use

marrowyung

ASKER

very good answer.

marrowyung

ASKER

what else the use of it?