Synonym Switching Technique and Tools

A 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
as
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:

-- 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.
synonym_util.switch_synonyms(
	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:

set serveroutput on

begin
    dbms_output.put_line('Old Schema: '||
        synonym_util.get_object_owner_for_synonym(
            p_synonym            => 'EMPLOYEES',
            p_synonym_owner        => 'PARSE_SCHEMA')
    );

    synonym_util.switch_synonyms(
        p_first_schema        => 'DATA_A',
        p_second_schema        => 'DATA_B',
        p_synonym_owner        => 'PARSE_SCHEMA',
        p_reference_synonym    => 'EMPLOYEES',
        p_create_views        => 'YES',
        p_object_types      => 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
        p_include_grants    => 'YES',
        p_grant_types        => 'SELECT:INSERT:UPDATE:DELETE:EXECUTE'
        );

    dbms_output.put_line('New Schema: '||
        synonym_util.get_object_owner_for_synonym(
            p_synonym            => 'EMPLOYEES',
            p_synonym_owner        => 'PARSE_SCHEMA')
    );
end;
/

Notes

• 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
as
    -- 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