Thursday, April 3, 2014

Oracle - Tablespace vs Schema

This may not be a new things to someone work with Oracle for ages, however, I feel confused when I come across the term tablesapce and it is different from schema

The main catch is there is no relationship between schemas and tablespaces. Ok... that surprised me a little.

But comes to think of it, it is 2 different concepts.

By Oracle definition

Schema - A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes.

Tablespaces - A tablespaces is a collection of logical storage units in a database. It groups related logical structures together. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

So, in fact, a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces.

Below are 2 very good figure from Oracles to explains Schema Objects, Tablespaces, and Datafiles

Text description of cncpt041.gif follows

Description of Figure 3-1 follows

Now, an example between schema and tablespaces. Below is an example of a single schema that exists in 2 different tablespaces

CREATE TABLE hr.payroll (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         salary        NUMBER(9) ENCRYPT)
   TABLESPACE payroll_tbs;

CREATE TABLE hr.emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         ssn        NUMBER(9) ENCRYPT)
   TABLESPACE emp_tbs;

The above statement create 2 tables payroll and emp under hr schema and owned by me. Although it is under the same schema, hr.payroll exists in payroll_tbs tablespaces and hr.emp exists in emp_tbs tablespaces.

Some good readings and references:

http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c11schem.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm#sthref70