Tablespace Information (Oracle)

You can obtain information about tablespaces from the following data dictionary views:

1. The dba_tablespaces view shows tablespace names, default storage attributes, online/offline status.
2. dba_datafiles view shows filenames, file sizes, online offline status, associated tablespace.
3. dba_free_space view shows free extents within a tablespace.
4. dba_extents view shows used extents within a tablespace. The column names within these views make them fairly self-explanatory.

A tablespace in Oracle is a logical storage unit that groups together related data structures, such as tables and indexes, within an Oracle database. It helps manage and organize the storage of data, and allows for efficient allocation and deallocation of disk space. In this regard, a tablespace can be viewed as a container for storing different database objects. A tablespace list, therefore, provides crucial information about the tablespaces in an Oracle database, which typically includes the following details:

1. Tablespace Name: This refers to the unique identifier for each tablespace within the database. It helps in differentiating between tablespaces and managing their respective data structures.
2. Datafile(s): Datafiles are the physical files on disk that store the actual data within a tablespace. A tablespace can have one or more datafiles associated with it. The list includes the names, file paths, and sizes of these datafiles.
3. Status: This indicates the current state of the tablespace, such as ONLINE, OFFLINE, or READ ONLY. The status helps in understanding whether the tablespace is available for use, undergoing maintenance, or set to prevent modifications.
4. Type: This refers to the type of tablespace, which can be either PERMANENT, TEMPORARY, or UNDO. Each type serves a specific purpose within the database, such as storing user data, managing temporary data during query execution, or handling undo logs for transactions.
5. Block Size: This is the size, in bytes, of the basic data storage unit within a tablespace. Block size affects the efficiency of I/O operations and influences the overall performance of the database.
6. Extent Management: This specifies the method used to manage the allocation and deallocation of space within the tablespace. It can be either LOCAL or DICTIONARY. LOCAL management implies that space management is done using bitmap structures, while DICTIONARY management relies on data dictionary tables.
7. Segment Space Management: This indicates the approach used to manage free and used space within segments. It can be either MANUAL or AUTO, with AUTO being the recommended method as it helps in preventing fragmentation and improving performance.
8. Allocation Type: This signifies whether the tablespace uses UNIFORM or SYSTEM allocation for creating extents. UNIFORM allocation means that all extents within the tablespace have the same size, while SYSTEM allocation allows extents to have varying sizes.
9. Size and Free Space: This provides information about the total size of the tablespace and the available free space. It helps in assessing the storage capacity and planning for future growth.

By understanding the information provided in a tablespace list, database administrators can effectively manage and monitor the storage aspects of an Oracle database, ensuring optimal performance and efficient utilization of resources.

The following query uses the dba_tablespaces view to return a list of tablespaces and their status: SELECT tablespace_name, status FROM dba_tablespaces;

The dba_data_files view returns information about data files. The following query will list all the data files in a database, organize them according to tablespace, and include the size and status of each file: SELECT tablespace_name, file_name, bytes, status FROM dba_data_files ORDER BY tablespace_name, file_name;

The dba_free_space view tracks the space in a tablespace that has not yet been assigned to an object . When you first create a tablespace, it will have one large free extent. Over time, that free space may become fragmented. This query sums all the free extent sizes to display the total amount of free space remaining:

SELECT tablespace_name, SUM(bytes) 
FROM dba_free_space 
GROUP BY tablespace_name;

The dba_free_space view returns information only for tablespaces that have free space. Tablespaces that are not listed when you execute this query are full and have no remaining free space.

The following SELECT statement queries the dba_extents view to display the amount of space in a tablespace that has been used for objects such as tables and indexes:

SELECT tablespace_name,
SUM(bytes) FROM dba_extents GROUP BY tablespace_name;

Tablespaces without objects are not listed in dba_extents. If you have a tablespace that doesn't appear in the query results, then all the space within that tablespace is available for use. You will learn how to add more space to a tablespace in the next lesson.

Click the Quiz link below to test your knowledge of creating tablespaces.
Tablespace Info - Quiz