Schemas, Users and Tablespaces

Users Own Objects

In Oracle, a schema is the set of objects owned by a user. Creating a user and giving it space is the first step of any project.

Creating a User

CREATE USER app_user IDENTIFIED BY app_password;
GRANT CONNECT, RESOURCE TO app_user;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_user;
ALTER USER app_user QUOTA UNLIMITED ON USERS;

Tablespaces

A tablespace is logical storage made of datafiles on disk. Oracle creates default tablespaces called SYSTEM, SYSAUX, USERS and TEMP.

SELECT tablespace_name FROM dba_tablespaces;
CREATE TABLESPACE app_data DATAFILE '/u01/app/oracle/app_data01.dbf' SIZE 500M;

Symptoms of No Quota

Without a quota, you get ORA-01950: no privileges on tablespace when inserting data. The QUOTA line above fixes exactly this.

Key Points

• A schema equals a user and its objects in Oracle.
• Users need CONNECT/RESOURCE and a tablespace quota.
• Tablespaces group physical datafiles.
• USERS is the default tablespace for application data.