The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it?

This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

In the simplest terms,

an Oracle user is a database  account with login access to the database; 
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.

Here is Tom Kyte’s take on the subject: Asktom: What is the meaning of schema?.

Though user and schema differ in their definitions, for most practical purposes the words can be used interchangeably. When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user. The schema name is the same as the user name. So the schema owned by user SCOTT is also SCOTT.

But…

USER and SCHEMA are not always identical

When you login to a database using a particular Oracle user, you connect by default to that user’s schema. However, in Oracle it is possible to be connected to the database via one Oracle user and switch to another Oracle user’s schema.

The alter session set current_schema command in Oracle can be used to switch to another schema while being connected to a particular Oracle user. Take a look at the article User != Schema for a lucid illustration of how this can be achieved.

CREATE USER vs CREATE SCHEMA

Let’s revisit a statement made earlier in this post:

When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user.

This begs the question: if CREATE USER creates a schema, what then does CREATE SCHEMA do?

Given that CREATE TABLE creates a table, CREATE INDEX creates an index, and so on, one might assume that CREATE SCHEMA would create a schema.

Not so.

CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction.

Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view.

1 2 3 4 5 6 7 8 9 10 11

SQL> CREATE SCHEMA AUTHORIZATION hr   2  -- must be the same as the current schema name   3     CREATE TABLE test1   4        (col1 VARCHAR2(10) PRIMARY KEY   5       , col2 NUMBER)   6     CREATE VIEW test2   7        AS SELECT col1   8           FROM test1 WHERE col2 = 3   9     GRANT select ON test2 TO dip;   Schema created.

In Conclusion

• Oracle schema = Oracle user + database objects owned by that user.
• When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default.
• There is a one-to-one correspondence between Oracle user name and Oracle schema name.
• While user = schema in most circumstances, that isn’t true all the time.

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table?

Answer: Even or odd rows are meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query.

Once the order is specified, then the query to retrieve odd rows or even rows can be written in this form:

1. Write a subquery with an ORDER BY clause. Along with the data columns, select the pseudocolumn rownum with an alias, say rn.
2. In the outer query, reference the alias rn and use the mod function to get odd rows or even rows.

An example using the SCOTT emp table: let’s say my result set is ordered by employee numbers.

Odd Rows

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> select * from   2  (select empno, ename, sal, rownum rn   3  from emp   4  order by empno)   5  where  mod (rn, 2) <> 0;        EMPNO ENAME             SAL         RN ---------- ---------- ---------- ----------       7369 SMITH             800          1       7521 WARD             1250          3       7654 MARTIN           1250          5       7782 CLARK            2850          7       7839 KING             5000          9       7876 ADAMS            1100         11       7902 FORD             3000         13   7 rows selected.

Even Rows

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> select * from   2  (select empno, ename, sal, rownum rn   3  from emp   4  order by empno)   5  where  mod (rn, 2) = 0;        EMPNO ENAME             SAL         RN ---------- ---------- ---------- ----------       7499 ALLEN            1600          2       7566 JONES            2975          4       7698 BLAKE            2850          6       7788 SCOTT            3000          8       7844 TURNER           1500         10       7900 JAMES             950         12       7934 MILLER           1300         14   7 rows selected.