In a DBMS (database management system) a tablespace is a logical group of data files in a database. Common database contains at least one tablespace but usually two or more. In a database, a tablespace plays a role similar to that of a folder on the hard drive of a computer by grouping similar functional objects. Oracle, automatically create a tablespace called SYSTEM that contains most of the information about the structure and contents of the database. A small database could be entirely contained in the SYSTEM tablespace (before 10g because after 10g Oracle needs at least 2 tablespaces, the SYSTEM and the SYSAUX).

Usually Oracle Database also has a TEMP tablespace (temporary) used for sorting data on disk in case you run out of memory assigned for sorting data when you query database. You may also have an UNDO tablespace to hold the before image of any changed data (used in rollback operations, flashback query, etc).

Oracle Database templates also come with USERS tablespace. This tablespace is a tablespace for containing your tables and all other objects in case you don’t create your own tablespace to contain them.

In order to an user create objects in a tablespace it must be granted quota space on that tablespace. By default when a user is created he does not have quota on any tablespace, so it must be granted explicitally by setting the size of quota or unlimited quota. There’s also a role called UNLIMITED TABLESPACE that assigns unlimited size on all database tablespaces to the user that’s granted, although I think it’s stupid to do so… i’m a security freak !

You can see any users quota by querying dba_ts_quotas dictionary view

select tablespace_name, max_bytes
from dba_ts_quotas
where username='MYUSER';

You can grant quota on tablespace to an user with the following command:

alter user MYUSER quota 100M on MYTABLESPACE;

There’s Locally managed tablespaces and dictionary managed tablespaces. I don’t want to be boring about comparing both, but the main differences are:

Locally Managed:

• available since Oracle 8i
• db blocks managed in the datafile
• uniform extent management allowed
• no undo generated
• fragmentation reduced

.

Dictionary Managed:

• currently available for backward compatibility
• db blocks managed in data dictionary
• no mechanism for uniform extent creation
• undo created resulting in locks and competition for resources

.

By the way, you can also have read-only tablespaces and big file tablespaces, two new types since 10g. May be someday I talk about them.

I hope I gave a simple explanation about tablespaces… If someone want to add something about the subject just comment this post.

---

Possibly related posts: (automatically generated)

• RMAN 06193: connected to target database (not started). WRONG!
• Oracle Tablespaces