Tablespaces and the Structure of the Oracle Database

on July 30, 2015

People who have worked with computers for any period of time are familiar with the concept of a file; it’s a place on disk where information is stored, and it has a name. Its size is usually not fixed: If you add information to the file, it grows larger and takes up more disk space, up to the maximum available. This process is managed by the operating system and often involves distributing the information in the file over several smaller sections of the disk that are not physically near each other. The operating system handles the logical connection of these smaller sections without your being aware of it at all. To you, the file looks like a single whole.

Oracle uses files as a part of its organizational scheme, but its logical structure goes beyond the concept of a file. A datafile is an operating system file (or file within an Automatic Storage Management disk group) used to store Oracle data. Each datafile is assigned to a tablespace—a logical division within the database. Tablespaces commonly include SYSTEM (for Oracle’s internal data dictionary), SYSAUX (for auxiliary internal objects), USERS (for user objects), and others for application tables, indexes, and additional database structures.

Datafiles can have a fixed size or they can be allowed to extend themselves automatically when they are filled, up to a defined limit. To add more space to a tablespace, you can manually extend your datafiles or add new datafiles. New rows can then be added to existing tables, and those tables may then have rows in multiple datafiles.

Each table has a single area of disk space, called a segment, set aside for it in the tablespace. Each segment, in turn, has an initial area of disk space, called the initial extent, set aside for it in the tablespace. Once the segment has used this space, the next extent, another single area of disk space, is set aside for it. When it has used this up as well, yet another extent is set aside. This process continues with every table until the whole tablespace is full. At that point, someone has to add a new datafile to the tablespace or extend the existing tablespace’s files before tables can get any bigger.

Every database also contains a SYSTEM tablespace, which contains the data dictionary as well as the names and locations of all the tablespaces, tables, indexes, and clusters for this database. The objects within the SYSTEM tablespace are owned by the SYS and SYSTEM users; no other users should own objects in this tablespace because those objects could have an adverse performance impact on the database.


You can rename a tablespace via the ALTER TABLESPACE command.

Related Posts

Leave a Reply