Oracle - Tablespaces, Schemas, and Tables

A tablespace in Oracle corresponds to a large unit of logical storage. Oracle automatically creates a tablespace called a SYSTEM that contains general information about the structure and contents of each database. User data is typically placed in other tablespaces. The data objects within a tablespace are often related in some way. For example, a database for a large university may contain tablespaces titled COURSES, STUDENTS, FEES, and SYSTEM. A tablespace may sometimes accommodate more than one database. Tablespaces conceptually correspond to folders on a computer. They may be created and deleted, and their contents may be merged. Each tablespace is made up of one or more files on the hard disk of the system. These files, called data files, actually contain the data that the tablespace logically represents.

Tablespaces are used to limit or extend disk space allocation for the data of a particular database or user, perform partial backups or recovery, and even allocate data storage among devices (such as tape drives, CD ROM drives, USB hard drives and so on) that in some cases improves performance.

A schema corresponds to a collection of data objects like tables, views (data organized according to user preferences for viewing), stored procedures (programs that re used to per from sequentially organized groups of commands) and so on and is usually associated with a single user. Schema objects may be created and modified through the SQL query language.

An Oracle database table conceptually corresponds to a relational table. It is a basic unit of logical data storage in an Oracle database. This means that the way a table looks is just a logical representation; it has nothing to do with the way the table is actually organized on the hard disk. Tables may be Indexed on one or more columns. An Index corresponds to an internal mapping of the column(s)'s values with the corresponding location on the hark disk in bytes (just like a book's index consists of keywords with the corresponding pages) and allows for faster data retrieval.

Each user is assigned a default tablespace and schema for the objects to be created and is selectively allowed to access other objects. When an Oracle User is created, a schema for the user is also created. This schema serves as a logical container for all the database objects that the user may create. This schema has the same name as the user and may be used to find all the objects the user owns.