How to view oracle table space

Overview

In Oracle database, table space is the basic unit for storing data. When table space usage is too high, database performance and availability will be affected. Therefore, it is important to monitor table space usage in a timely manner. In this article, we will introduce how to view Oracle's tablespaces.

1. Check the size and usage of the table space

To check the size and usage of the table space, you can use the following SQL query:

SELECT tablespace_name, sum(bytes) / 1024 / 1024 size_mb, sum(bytes) / 1024 / 1024 - sum(decode(autoextensible, 'YES', maxbytes, bytes))/ 1024 / 1024 used_mb, round((sum(bytes) / 1024 / 1024 - sum(decode(autoextensible, 'YES', maxbytes, bytes)))/ sum(bytes)* 100,2) used_pct
FROM dba_data_files
GROUP BY tablespace_name;

The output of the above SQL query statement will display the name, total size, used size and usage rate of each table space.

2. View the objects that occupy the largest space in each table space

To view the objects that occupy the largest space in each table space, you can use the following SQL query:

SELECT
    T.tablespace_name,
    TO_CHAR(SUM(NVL(T.bytes, 0)) / 1024/1024, '99,999,990.99') || ' MB' AS "Tablespace Size",
    TO_CHAR(SUM(NVL(F.bytes, 0)) / 1024/1024,'99,999,990.99') || ' MB' AS "File Size",
    TO_CHAR(SUM(NVL(T.bytes, 0)) - SUM(NVL(F.bytes, 0)) / 1024/1024,'99,999,990.99') || ' MB' AS "Used MB",
    ROUND((SUM(NVL(T.bytes, 0)) - SUM(NVL(F.bytes, 0))) * 100 / SUM(NVL(T.bytes, 0)),2) || '%' AS "Pct. Used"
FROM
    dba_tablespaces T,
    (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) F
WHERE
    T.tablespace_name = F.tablespace_name (+)
GROUP BY
    T.tablespace_name;

The output of the above SQL query statement will display the name, total size, file size, used space and usage rate of each table space.

3. View the table space and size of each data file

To view the table space and size of each data file, you can use the following SQL query:

SELECT
    F.file_name,
    T.tablespace_name,
    TO_CHAR(F.bytes / 1024/1024, '999,999,990.99') || ' MB' AS "File Size"
FROM
    dba_data_files F
    LEFT OUTER JOIN dba_tablespaces T ON F.tablespace_name = T.tablespace_name
ORDER BY
    1;

The output of the above SQL query statement will list the name, table space and size of each data file.

Conclusion

It is very important to check the Oracle table space, because it can help the database administrator detect the usage of the table space in a timely manner and take necessary measures to ensure the performance and availability of the database. In this article, we introduce three different ways to view Oracle's tablespaces, each of which can help you better monitor and manage your database's tablespaces.

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