Changing the Current Schema

It is  time consuming process for me to put the schema name in front of the objects from that schema when i am doing maintenance processes on the objects of the schema. DBA ‘s usually work with system or sys users and they have to do this alias process for not to face with ORA-00942 error (table or view does not exist). But if the connected user doesn’t have permission on the current schema object you will still get ORA-00942.

To solve this problem you can use current_schema session parameter.

alter session set current_schema=SCHEMA_NAME /*without quotes*/

Usage;

idle> connect scott/tiger@xe;
Connected.
scott@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

scott@XE> create table t1 as select * from user_sys_privs;

Table created.

scott@XE> select * from t1;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

scott@XE> grant select on t1 to hr; –for testing with hr

Grant succeeded.

scott@XE> connect hr/hr

Connected.

hr@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

hr@XE> create table t1 as select table_name from user_tables;

Table created.

hr@XE> select * from t1; –its own table

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

hr@XE> alter session set current_schema=scott;

Session altered.

hr@XE> select * from t1; –t1 from the scott schema

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

hr@XE> connect system/*****
Connected.
system@XE> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

system@XE> alter session set current_schema=scott;

Session altered.

system@XE> select * from t1;

USERNAME PRIVILEGE ADM
—————————— —————————————- —
SCOTT CREATE VIEW NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT ALTER SESSION NO
SCOTT CREATE SESSION NO

system@XE> alter session set current_schema=hr;

Session altered.

system@XE> select * from t1;

TABLE_NAME
——————————
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
MUNEVVER
COSKAN
FLASHBACK_TEST
COUNTRIES

10 rows selected.

!!!!! Don’t forget to change your current schema settings after your operations on other schemas.

Use the query below to get your current_schema info

system@XE> SELECT SYS_CONTEXT(‘USERENV’,'CURRENT_SCHEMA’) from DUAL;

SYS_CONTEXT(‘USERENV’,'CURRENT_SCHEMA’)

—————————————————————————————————-

HR

About these ads