Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Synonym Switching Technique and Tools

Posted by Tyler Muth on October 11, 2010

AriaA long time ago (about 7 years), in a land far, far away (Reston, VA), I worked in a group that supported an Oracle internal application called “Aria People”. That group became the APEX development team (managed by Mike Hichwa) and at the time reported to Tim Hoechst. Tom Kyte and his team also worked for Tim at the time and the day to day management of Aria was passed around to various members of Mike and Tom’s team. Aria is a simple employee directory that is wildly popular inside Oracle. You can even download a version of Aria here as an APEX packaged application. I’m not sure who’s idea it was (Tim, Tom, Mike…) but Aria used a very effective technique to refresh its data from our global HR database. The application ran (parsed) as one schema, lets call it PEOPLE_PARSE, and that schema had synonyms that pointed to one of two data schemas, PEOPLE_A and PEOPLE_B. PEOPLE_PARSE didn’t actually own any tables or procedures. PEOPLE_A and PEOPLE_B owned all of the tables and procedures that contained the data (simplified explanation). Each night a PL/SQL package would truncate and refresh one of the schemas. Then when it was complete it would drop and recreate all of the synonyms in PEOPLE_PARSE to point to the most recently refreshed schema. In short, the source of data would alternate between PEOPLE_A and PEOPLE_B every night.  This has a couple of key advantages including the ability to truncate and load one schema while running against the other and the ability to switch back to the previous days data schema if anything went wrong. Tim mentioned many times that we should publish this technique in some form or another as it was relatively simple and exceptionally useful. APEX even uses a modified version of this technique for it’s own upgrades, just with public synonyms.

Seven years later, and I now have a current customer that could potentially benefit from this technique, so I decided to create a utility package to automate it. I see two primary scenarios for the schema layout. The 3 schema layout like Aria uses and a 4+ schema layout that introduces a synonym administrator schema.  The latter would be beneficial if you plan to add many new data schemas, such as a new schema each week or month that is a copy of production data, not just flip back and forth between 2 schemas.

Below is a short summary of the package.  Each procedure can be used independently, such as for 1-time operations or you can use the switch_synonyms procedure which calls all of the other procedures.

create or replace package synonym_util
procedure drop_user_views ...
procedure drop_views_for_target ...
procedure drop_user_synonyms ...
procedure drop_synonyms_for_target ...
procedure create_views_for_target ...
procedure grant_object_privs ...
procedure revoke_object_privs ...
procedure create_synonyms ...
function  get_object_owner_for_synonym ...
procedure switch_synonyms ...

Scenario 1

Lets run through Scenario 1 to provide you with an example:

  1. Install the synonym_util package in all 3 schemas
  2. Run the included grants_minimal.sql as sys to give PARSE_SCHEMA just enough privs to function.
  3. Run:
    data_a.synonym_util.grant_object_privs(p_grantee => ‘PARSE_SCHEMA’, p_objects_owner => ‘DATA_A’);
    data_b.synonym_util.grant_object_privs(p_grantee => ‘PARSE_SCHEMA’, p_objects_owner => ‘DATA_B’);
  4. You can now drop the package from DATA_A and DATA_B as we only needed it there for the grants.
  5. Run the following block as PARSE_SCHEMA:
  6. -- The first time you run this it will create synonyms pointed to p_first_schema.
    -- Each subsequent run will look at where the “EMPLOYEES” synonym is pointing,
    -- then flip the synonyms to the other schema.
    	p_first_schema        => 'DATA_A',
    	p_second_schema       => 'DATA_B',
    	p_synonym_owner       => 'PARSE_SCHEMA',
    	p_reference_synonym   => 'EMPLOYEES');


Scenario 2

Lets run through Scenario 2 to provide you with an example of using a SYNONYM_ADMIN schema:

  1. Install the synonym_util package in the SYNONYM_ADMIN schema.
  2. As sys, run the included grants_super_user.sql file.  Warning, you are giving SYNONYM_ADMIN a lot of power, so use with caution.
  3. Run the following code as synonym_admin:
  4. set serveroutput on
        dbms_output.put_line('Old Schema: '||
                p_synonym            => 'EMPLOYEES',
                p_synonym_owner        => 'PARSE_SCHEMA')
            p_first_schema        => 'DATA_A',
            p_second_schema        => 'DATA_B',
            p_synonym_owner        => 'PARSE_SCHEMA',
            p_reference_synonym    => 'EMPLOYEES',
            p_create_views        => 'YES',
            p_include_grants    => 'YES',
            p_grant_types        => 'SELECT:INSERT:UPDATE:DELETE:EXECUTE'
        dbms_output.put_line('New Schema: '||
                p_synonym            => 'EMPLOYEES',
                p_synonym_owner        => 'PARSE_SCHEMA')


  • There are schema restriction globals in the package body.  If you plan on using Scenario 2, I strongly suggest you set those globals to restrict use of this utility.
  • This is a “definers rights” package. When owned by a powerful user it can be a security risk.
  • You can download the full package here: synonym_util.zip (5 kb)

Package Spec (for preview)

create or replace package synonym_util
authid definer
    -- Drop all views for a given user (p_view_owner)
    procedure drop_user_views(
        p_view_owner     in varchar2 default user);

    -- Drop all views for a given user (p_view_owner) that reference objects in
    -- a target schema (p_target_owner)
    procedure drop_views_for_target(
        p_target_owner    in varchar2,
        p_view_owner     in varchar2 default user);

    -- Drop all synonyms for a given user
    procedure drop_user_synonyms(p_synonym_owner in varchar2 default user);

    -- Drop synonyms for a given user (p_synonym_owner) that point to a "target" schema (p_target_owner)
    procedure drop_synonyms_for_target(
        p_target_owner  in varchar2,
        p_synonym_owner in varchar2 default user,
        p_drop_views    in varchar2 default 'YES' -- YES | NO

    -- Create views in a schema (p_view_owner) that point to objects in a
    -- target schema (p_target_owner).  You can pass in a colon separated list
    -- of object types: 'TABLE:VIEW:MATERIALIZED VIEW'
    procedure create_views_for_target(
        p_target_owner    in varchar2,
        p_view_owner     in varchar2 default user,
        p_object_types  in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW');

    -- Grant object privs from p_objects_owner to p_grantee.
    -- Package must be owned by p_objects_owner or by a user that has system privs
    procedure grant_object_privs(
        p_grantee        in varchar2,
        p_objects_owner    in varchar2 default user,
        p_object_types    in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
        p_grants        in varchar2 default 'SELECT:INSERT:UPDATE:DELETE:EXECUTE');

    procedure revoke_object_privs(
        p_grantee        in varchar2,
        p_objects_owner    in varchar2 default user);

    -- Create synonyms in a schema (p_synonym_owner) that point to all of the objects
    -- in a target schema (p_objects_owner).
    -- By default it will create VIEWS instead of synonms for TABLES, VIEWS and MATERIALIZED views
    -- as the describe of the view is much more useful than the describe of the synonym.
    -- You can optionionally choose which object types to reference.
    procedure create_synonyms(
        p_objects_owner in varchar2,
        p_synonym_owner in varchar2 default user,
        p_create_views    in varchar2 default 'YES', -- YES | NO
        p_object_types  in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE');

    -- For a given synonym and synonym owner, return the schema / owner that the synonym points to
    function get_object_owner_for_synonym(
        p_synonym            in varchar2,
        p_synonym_owner        in varchar2 default user)
        return varchar2;

    -- This is the rollup of all or most of the other procedures in this package. It's meant for situations where you
    -- want to switch back and forth between 2 object owner schemas from a synonym schema
    procedure switch_synonyms(
        p_first_schema        in varchar2,
        p_second_schema        in varchar2,
        p_synonym_owner        in varchar2,
        p_reference_synonym    in varchar2,
        p_create_views        in varchar2 default 'YES', -- YES | NO
        p_object_types      in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
        p_include_grants    in varchar2 default 'NO', -- YES | NO
        p_grant_types        in varchar2 default 'SELECT:INSERT:UPDATE:DELETE:EXECUTE' -- 'SELECT:INSERT:UPDATE:DELETE:EXECUTE'

end synonym_util;
show errors

12 Responses to “Synonym Switching Technique and Tools”

  1. Matt Nolan said

    Excellent post Tyler and a big thanks for sharing the code. Good design never dies, it’s a great technique and one that I’m going to remember.


  2. Goodness, grants_super_user.sql does have some powerful stuff in it — either of “GRANT ANY OBJECT PRIVILEGE” and “execute any procedure” would be enough to have its use prohibited at many places I’ve worked at.

    Synonym switching is a very handy technique though. You can use partition exchanges (within a single schema of course) to do something similar if you have the license for it, but synonym switching is somewhat easier to pick the bones out of if it all goes pear-shaped half way through and you’re not sure what version of data you’re looking at for each object.

    • Tyler Muth said


      I agree 100% about the security risk, that’s why I broke out those rights into a separate script and put warnings all over the place. The customer I’m working with is looking for a solution to have multiple copies of the production schema in their dev database. The applications they run have the schema name burned-in. They also don’t deal with sensitive data. For them, the SYNONYM_ADMIN scenario makes sense. I don’t think it EVER makes sense on a production server.


  3. Tyson Jouglet said

    My work uses a similar technique when loading large amounts of data into our data warehouse. Rather then swapping schemas though we swap partitions on the tables. Create table x and table stg_x. Load all the new warehouse data into stg_x and then swap partitions for x to stg_x. I like this schema swapping idea a lot because you have more flexibility. great tip.

  4. Stew Stryker said


    Thanks for the great explanation of these terrific techniques. I’ve never gotten this “physical” with Oracle, but will keep this in my back pocket for future use.

    We had a need to instantaneously swap in new data for old a couple years ago, but we solved it by creating a shadow table and a dummy table. We loaded the new data into the shadow. Then we renamed the tables in a round-robin to make the shadow become the production table. It all happens in an instant and allows us to provide updates to our 24/7 web app without any down-time.

    I like your technique even better because it would mean one fewer table in the mix.


  5. Keylogger…

    […]Synonym Switching Technique and Tools « Tyler Muth’s Blog[…]…

  6. Noons said

    I missed this one, pity.

    Much, much simpler:

    – create a login trigger with some PL/SQL code that recognizes the PEOPLE_PARSE login.
    – in the PL/SQL code, read a global setting from a table defining which schema they should connect to.
    – simply run execute immediate ‘alter session set current_schema=PEOPLE_A’, or PEOPLE_B, depending on the setting of that table, if the login is PEOPLE_PARSE.
    – change the setting on the table as you need.

    Bingo: no more need for any synonym drops/creates and the upheaval that does to the SGA.
    In fact, no need for ANY synonyms and the PEOPLE_PARSE login can be under control of a role that only lets it, for example, read data from some tables and read/write in others: the role will still be active after the reset of the current_schema.

    It’s how I control which schema each login uses, in all my databases: works like magic and doesn’t need a single synonym!

  7. Hello! Someone in my Myspace group shared this site with us so I came to give it a look.
    I’m definitely loving the information. I’m bookmarking and will
    be tweeting this to my followers! Terrific blog and fantastic

  8. Hi! I know this is kinda off topic but I was wondering if you knew
    where I could locate a captcha plugin for my comment form?

    I’m using the same blog platform as yours and I’m having difficulty finding one?
    Thanks a lot!

  9. Their services are valuable to you simply because they take the guess work out of the
    training and nutrition your body needs to be healthy for life.
    Now you already know what can affect this fat loss, but maybe yourself not sure whether you can do this or not.
    Of course, you should always consult your doctor for a thorough evaluation and prescription.

  10. Abhishek said

    Are you suggesting to run switch_synonyms procedure just before the schema refresh start? I was a bit confused when we would have to execute it?

  11. samara said

    thank you for this superbe article.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: