USER != SCHEMA

Most of us tend to bandy around the terms USER and SCHEMA is if they were synonyms, but in Oracle they are different objects. USER is the account name, SCHEMA is the set of objects owned by that user. True, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but it is quite easy to demonstrate that they are different things.

SQL> conn b/b
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> grant select on my_tab to a
2 /

Grant succeeded.

SQL> conn a/a
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80404 ACTOR
80414 ACTOR_NT
45707 AP
45708 AP_PK
52765 ASSIGNMENT
49747 A_ID
52768 A_OBJTYP

7 rows selected.

SQL> alter session set current_schema=b
2 /

Session altered.

SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> select username, schemaname
2 from v$session
3 where sid in (select sid from v$mystat)
4 /
USERNAME SCHEMANAME
--------- -----------
A B

SQL>

The important thing to remember about alter session set current_schema is that it only changes the default schema. It does not change the privileges we have on that schema and it does not change the results when we issues queries that depend upon username, for instance against the USER_ views.

SQL> conn u2/u2
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2
T3

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> grant select on t1 to u1
  2  /

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 AAAAAAAAAA

SQL> alter session set current_schema=U2
  2  /

Session altered.

SQL> select * from t1
  2  /
      COL1 COL2
---------- ----------
         1 BBBBBBBBBB

SQL> select * from t2
  2  /
select * from t2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from u1.t2
  2  /

no rows selected

SQL> select table_name from user_tables
  2  /
TABLE_NAME
------------------------------
T1
T2

SQL> 

Of course, the confusion stems partly from the fact that there is a one-to-one correspondence between USER and SCHEMA, and a user's schema shares its name. But the fact that people who ought to know better use them interchangeably (me included) doesn't help matters.

Update

If you found this interesting you might also want to read a piece I have published on the CREATE SCHEMA statement.