How to query oracle table space size

How to query the size of table space in oracle: 1. Use the DBA_TABLESPACE view. This view stores all table space information, including the name, size, remaining available space, etc. of the table space; 2. Use the storage provided by Oracle Procedure DBMS_SPACE.SPACE_USAGE, this stored procedure returns detailed information of the table space, including the total size of the table space, used size, remaining free space, etc.

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

Oracle is a very famous relational database management system, which provides a convenient way to query the size of the table space. In Oracle, a tablespace is a logical structure in a database that stores tables, indexes, and other objects.

In Oracle, there are several ways to query the size of a table space. Below I will introduce two commonly used methods.

The first method is to use the DBA_TABLESPACE view. This view stores all table space information, including table space name, size, remaining free space, etc.

The following is a sample SQL statement to query the size of the table space:

SELECT tablespace_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS "Size(MB)",
ROUND(SUM(bytes - freespace) / 1024 / 1024, 2) AS "Used(MB)",
ROUND(SUM(freespace) / 1024 / 1024, 2) AS "Free(MB)"
FROM (SELECT df.tablespace_name,
df.bytes,
SUM(fs.bytes) AS "freespace"
FROM (SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) AS bytes
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) AS bytes
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
GROUP BY df.tablespace_name, df.bytes)
GROUP BY tablespace_name;

The above query statement obtains the size information of the table space by connecting the DBA_DATA_FILES and DBA_FREE_SPACE views. It displays the size of the table space by counting the number of bytes and converting it to megabytes (MB).

Another method is to use the stored procedure DBMS_SPACE.SPACE_USAGE provided by Oracle. This stored procedure returns detailed information about the table space, including total table space size, used size, remaining free space, etc.

The following is an example of using a stored procedure to query the size of a table space:

SET SERVEROUTPUT ON;
DECLARE
total_size NUMBER;
used_size NUMBER;
free_size NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE('TABLESPACE_NAME', total_size, used_size, 
free_size);
DBMS_OUTPUT.PUT_LINE('Total Size: ' || ROUND(total_size / 1024 / 1024, 2) || 
' MB');
DBMS_OUTPUT.PUT_LINE('Used Size: ' || ROUND(used_size / 1024 / 1024, 2) || ' 
MB');
DBMS_OUTPUT.PUT_LINE('Free Size: ' || ROUND(free_size / 1024 / 1024, 2) || ' 
MB');
END;
/

The above stored procedure passes the table space name as a parameter to DBMS_SPACE.SPACE_USAGE, and returns the total size of the table space, the Size used and remaining free space. Query results can be displayed on the console using the DBMS_OUTPUT.PUT_LINE statement.

No matter which method you use, these queries can help you understand the size of the table spaces in your Oracle database. By understanding table space usage, you can better manage and optimize your database's storage space.

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