Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Synonym

A synonym is a database object, which is used as an alias for a table, view or sequence.

TYPES
Private
Public

Private synonym is available to the particular user who creates.
Public synonym is created by DBA which is available to all the users.

ADVANTAGES

           Hide the name and owner of the object.
           Provides location transparency for remote objects of a distributed database.

Syntax for Private Synonym

      Create Synonym sysnonym_name for  table_name;

Syntax for Public Synonym

                           Create Synonym Public sysnonym_name for  table_name;

CREATE AND DROP

SQL> create synonym s1 for emp;
SQL> create public synonym s2 for emp;
SQL> drop synonym s1;
Share this article :

3 comments:

  1. NayHoh,


    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

    I am using SQL server 2014 standard edition.
    I am using Drill Through action to display a detail report for my cube data, which is working fine. Only issue I see is when I drill through year and quarter it displays data for months in a haphazard manner instead of sorted by month. Ho do I set it to sort by month, and Item Number (which is a fact dimension for this measure) within month
    Once again thanks
    for your tutorial.

    Cheers,

    ReplyDelete
  2. "Hi There,

    So bloody thorough! Ah! So happy and blissed out! I feel redeemed by reading out Oracle Forms Customization Question and Answers. Keep up the good work!

    I have this query :

    Code (SQL):

    SELECT md.PCB_TYPE, sm.STEP_MACHINE, sm.DESCRIPTION AS STATION, md.MODEL_NUMBER AS MODEL, md.MODELID, md.NUM_OF_BOARDS AS PANELS, ct.CYCLE_TIME
    FROM ECU_STEP_MACHINE sm
    LEFT OUTER JOIN ECU_STEP_LINES sl ON sm.LINE_ID = sl.LINE_ID
    LEFT OUTER JOIN ECU_LINE_ASSIGN la ON sl.LINE_NUMBER = la.LINE_NUMBER
    INNER JOIN ECU_MODEL_DEFINITION md ON la.MODEL_NUMBER = md.MODEL_NUMBER
    AND la.MODEL_YEAR = md.MODEL_YEAR
    AND la.PROD_TYPE = md.MODEL_PROD_TYPE
    AND la.DESIGN_LEVEL = md.PROD_DESIGN_LEVEL
    LEFT OUTER JOIN OEE_CYCLETIME ct ON sm.STEP_MACHINE = ct.STEP_MACHINE AND md.MODELID = ct.MODELID
    WHERE sm.STEP_MACHINE > 50000 AND sl.LOCATION_ID = 3 AND md.ACTIVE = 1 AND md.MODEL_PROD_TYPE = 'M'
    GROUP BY md.PCB_TYPE, sm.STEP_MACHINE, sm.DESCRIPTION, md.MODEL_NUMBER, md.MODELID, md.NUM_OF_BOARDS, ct.CYCLE_TIME
    ORDER BY sm.DESCRIPTION;
    1) returns:
    AC1 75006 AGING6 8161 12781 4
    AC1 75006 AGING6 8307 11380 4
    AC1 75006 AGING6 8335 9219 4
    AC1 75006 AGING6 8336 10231 4
    AC1 75006 AGING6 8337 10763 4
    AC1 75006 AGING6 8339 9231 4
    AC1 75006 AGING6 8348 11386 4
    AC1 75006 AGING6 8349 11393 4
    AC1 75006 AGING6 8350 10778 4
    AC1 75006 AGING6 8351 10784 4
    AH2 75006 AGING6 6469 13038 4
    AH2 75006 AGING6 8168 13136 4
    AH2 75006 AGING6 8342 13674 4

    What I want is all the pcb_types grouped (AC1 AH2, etc.)

    Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.

    Thank you,
    Preethi
    "

    ReplyDelete
  3. Ni Hau,


    Such vivid info on the SQL Synonym! Flabbergasted! Thank you for making the read a smooth sail!


    I need to insert 10lakhs record using bulk collect. I got an error in say 96000th record how to solve it????? Using stored procedure and collections Kindly share the program if anyone knows.

    Great effort, I wish I saw it earlier. Would have saved my day :)


    Thanks a heaps,
    Radhey

    ReplyDelete