• Login
• Become a member
• RSS
• Part of the TechTarget network

This is part two of a multi-part series on Oracle database consolidation. The first part looked at database sprawl and why database consolidation is a paradigm shift.

Oracle provides several built-in features that enable database consolidation.  One such feature is session schema management.  The following use case will demonstrate how session-level schema controls can transparently enable multiple schemas to share the same database promoting multi-tenancy.

The ABC Gum company’s customer information system has each of its software development lifecycle (SDLC) schemas in different databases.  In an effort to improve efficiency and reduce cost the company is looking to consolidate all non-production schemas into a single database.  To be successful, the project must meet the following two requirements: project management controls and no application changes.

The DBA team first looks at application impact, specifically unqualified SQL code.  Although separate schemas with similar objects provide logical separation, consolidation introduces an issue called schema name collision. With three SDLC environments now sharing the same database, there are three “customer” tables.  The DBA team’s long-enforced best practice of using public synonyms to resolve unqualified references of schema objects will no longer work.  They consider creating private synonyms for each developer and assigning them to a single SDLC environment.  However, the developers frequently move between environments and forgetting to drop and recreate their private synonyms would jeopardize integrity and lead to chaos.  Synonyms don’t seem to be the answer for this dynamic development environment.

Fortunately, Oracle provides the ability for users to change schemas within a session, eliminating the need to qualify references to schema objects.  See below:

ALTER SESSION SET CURRENT_SCHEMA = <Schema>;

The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema.

The setting persists for the duration of the session or until you issue another ALTER command.  This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.                     

Reference: Oracle® Database SQL Language Reference

The following code shows how a developer’s schema can change during a session to one of the three ABC application schemas without changing unqualified SQL code.  As an added bonus, the team consolidates the three “state” look-up tables into one and uses a public synonym to provide access to developers regardless of their current schema.  By using security roles, a single user can have privileges in multiple schemas, eliminating the need for multiple Oracle accounts.

-- Logon as Jeff the developer

select substr(sys_context('USERENV', 'SESSION_USER')  ,1,10) "Session User"

      ,substr(sys_context('USERENV', 'CURRENT_SCHEMA'),1,10) "Current Schema"

  from dual;\

Session User         Current Schema

----------------     ---------------------

JEFF                     JEFF

select customer_name from customer;

                SQL Error: ORA-00942: table or view does not exist

-- Set current schema to Development

alter session set current_schema = abc_dev;

alter session set succeeded.

select substr(sys_context('USERENV', 'SESSION_USER')  ,1,10) "Session User"

      ,substr(sys_context('USERENV', 'CURRENT_SCHEMA'),1,10) "Current Schema"

  from dual;

Session User         Current Schema

----------------     ---------------------

JEFF                     ABC_DEV 

select customer_name from customer;

CUSTOMER_NAME                 

------------------------------

Jaime

-- Set current schema to Test

alter session set current_schema = abc_test;

select substr(sys_context('USERENV', 'SESSION_USER')  ,1,10) "Session User"

      ,substr(sys_context('USERENV', 'CURRENT_SCHEMA'),1,10) "Current Schema"

  from dual;

Session User         Current Schema

----------------     ---------------------

JEFF                     ABC_TEST

alter session set succeeded.

select customer_name, state_name

 from customer inner join val_state

   on customer.state_code = val_state.state_code;

CUSTOMER_NAME              STATE_NAME                    

------------------------------    ------------------------------

Rebecca                                    Connecticut      

With unqualified SQL code now resolving to the correct schema, the project administrator wants to control the assignment of a developer’s schema.  The DBAs propose a table-driven solution that maps a developer to a schema.  The schema assignment is managed by a project administrator and executed at logon via a logon trigger.  The following code shows how a developer’s schema can be automatically changed from their authenticated schema to an assigned schema at logon.

-- Step 1: Create user to schema mapping table

create table abc_user_schema

 (

  abc_user    varchar2(30) not null

 ,abc_schema  varchar2(30) not null

 ,constraint  abc_user_pk primary key (abc_user) enable validate

)

organization index

pctfree 0;

-- Step 2: Populate user to schema mapping table

insert into abc_user_schema (abc_user, abc_schema) values ('JEFF','ABC_DEV');

commit;

-- Step 3: Create logon trigger

create or replace trigger abc_user_schema_assignment

  after logon

  on database when (sys_context('USERENV', 'ISDBA') = 'FALSE')

  declare

     l_session_user    abc_user_schema.abc_user%type := sys_context('USERENV', 'SESSION_USER');

     l_assigned_schema abc_user_schema.abc_schema%type;

  begin

    begin

      select abc_schema into l_assigned_schema

        from abc_user_schema

        where abc_user = l_session_user;           

      execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = '|| l_assigned_schema;       

      exception

        when NO_DATA_FOUND then NULL;

        when others        then raise;

    end;

    exception

        when others then raise_application_error(-20001,'UM : Logon Trigger - Error - '||SQLERRM(sqlcode));

  end;

Jeff McCormick is an architecture director at a major health service company and president of the Connecticut Oracle User Group. McCormick has worked in IT for more than 20 years as a data and infrastructure architect/administrator. He holds several Oracle, Microsoft and Sybase professional certificates.

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

All Rights Reserved,Copyright 2003 - 2013, TechTarget