How to query table space size in oracle

In Oracle, you can use the SELECT statement to query the size of the table space. The syntax is "SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE ...GROUP BY..."; Table space is just a logical concept, and data files are what actually store data.

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

How to query the table space size in oracle

View the name and size of the table space

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

View the name and size of the table space physical file

Expand knowledge;

Oracle tablespaces (tablespaces) are a logical concept. What actually stores data is data files. An Oracle database can have one or more table spaces, and a table space corresponds to one or more physical database files.

Table space is the smallest unit for Oracle database recovery, housing many database entities, such as tables, views, indexes, clusters, rollback segments, temporary segments, etc.

1. Features of Oracle table space:

(1) Control database data disk allocation;

(2) Restrict users in the table space The amount of disk space that can be used;

(3) The table space has online, offline, readonly, and readwrite attributes.

2. Classification of table spaces:

Permanent table space: Some objects to be stored permanently in the database, such as tables, views, and stored procedures

Temporary table space: The intermediate execution process during database operations. After the execution is completed, the stored content will be automatically released.

UNDO table space: used to save the old values ​​​​of transaction modified data, which can be used for data processing Rollback

Segment (segment) refers to the general name of the data file space occupied, or the collection of space used by database objects; segments can include table segments, index segments, rollback segments, temporary segments and cache segments wait.

Extent (interval/extension), any continuous block allocated to an object (such as a table) is called an interval; the interval is also called an extension, because when it uses up the allocated interval, new records will be inserted. New extents must be allocated (that is, some blocks are extended); once the extent is allocated to an object (table, index, and cluster), the extent cannot be allocated to other objects.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query table space size in oracle. For more information, please follow other related articles on the PHP Chinese website!