How To Switch Schemas With Unknown Password

BACKGROUND

This document describes how to reference objects in another schema without switching users, fully qualifying SQL or creating synonyms. This is useful when asked to run a script containing ALTER statements or procedures but the Schema Owner password is unknown as well as the impact of changing it.

HOW TO TEMPORARILY SET THE SCHEMA

   SQL> SHOW USER

      USER is "SYS"

   SQL> ALTER SESSION SET current_schema=scott;

      Session altered.

CHECK WHICH THE CURRENTLY 'MOONLIGHTED' SCHEMA

   SQL> SELECT sys_context('USERENV','SESSION_SCHEMA') FROM dual;

      SYS_CONTEXT('USERENV','SESSION_SCHEMA')
      ----------------------------------------------------------
      SCOTT

A WORKED EXAMPLE

   SQL> SHOW USER

      USER is "SYS"

   SQL>  ALTER SESSION SET current_schema=FRED;

      Session altered.

   SQL> SHOW USER

      USER is "SYS"

   SQL> SELECT sys_context('USERENV','SESSION_SCHEMA') FROM dual;

      SYS_CONTEXT('USERENV','SESSION_SCHEMA')
      --------------------------------------------------------------
      FRED

   SQL> CREATE TABLE moonlight (col1 char(10));

      Table created.

   SQL> SELECT owner FROM dba_tables WHERE table_name='MOONLIGHT';

      OWNER
      ------------------------------
      FRED

   SQL> INSERT INTO moonlight VALUES ('Test');

      1 row created.

   SQL> SELECT * FROM moonlight;

      COL1
      -----
      Test
© copyright 2001-2014 ABCdba.com | all rights reserved