Oracle Database Administration: The Essential Refe

Oracle Tablespaces

The tablespace is a logical construct used by Oracle to manage disk space. Tablespaces store various types of objects, including:

• Tables

• Indexes

• Stored procedures, functions, and packages (in the SYSTEM tablespace)

• Rollback segments

• Temporary segments

A tablespace is constructed by Oracle from one or more operating system files through the use of the CREATE TABLESPACE command. Every Oracle database consists of at least one tablespace, called SYSTEM.

When a tablespace is created, the names of the operating system files that will make up that tablespace are specified. For example, suppose that the following command is executed in a Unix environment:

CREATE TABLESPACE user_data
DATAFILE '/disk05/oracle/oradata/userdata01.dbf' SIZE 50M;

A datafile called userdata01.dbf will be created in /disk05/oracle/oradata. This file will have a size of 50 megabytes and will be initialized by Oracle into Oracle blocks. The size of each Oracle block is determined by the parameter DB_BLOCK_SIZE, which is specified at the time the database is created. This is one of the only database parameters that cannot be changed, so take care when specifying the blocksize.

Once the tablespace is created, all access to the underlying datafiles is controlled by Oracle. No user has any reason to read from or write to Oracle datafiles, so file protections should be set so that only the Oracle owner account has access to these files. In a Unix environment, Oracle datafiles should be owned by the account “oracle”, ...