Oracle Database - TableSpace

A tablespace is a logical data structure which is physically represented by one or more datafiles.

• System tablespace: The system tablespace is reserved for the data dictionary.

ALTER DATABASE DEFAULT TABLESPACE "USERS";

When you specify a “temporary” tablespace, the tablespace cannot be used to hold any permanent segments; it can only be used for temporary segments created during queries. The first sort to use the temporary tablespace allocates a temporary segment within the temporary tablespace; when the query completes, the space used by the temporary segment is not dropped. Instead, the space used by the temporary segment is available for use by other queries; this allows the sorting operation to avoid the costs of allocating and releasing space for temporary segments. This will reduce the temporary segment lock allocation contention and will save the cost of the 10 INSERT/UPDATE/DELETE statements involved in temporary segment allocation.

To dedicate a tablespace as a temporary tablespace, specify TEMPORARY in the CREATE TABLESPACE or ALTER TABLESPACE command. The example shown in the following listing changes an existing tablespace named TEMP_1 to a TEMPORARY tablespace.

ALTER tablespace TEMP_1 TEMPORARY;

To change the TEMP_1 tablespace out of TEMPORARY state, and allow permanent objects to be stored in it, use the PERMANENT keyword in the ALTER TABLESPACE command, as shown in the following listing.

ALTER tablespace TEMP_1 permanent;