How to query the space size of Oracle database

Oracle database is a very popular relational database management system, which is widely used in enterprises. In order to ensure the performance and stability of the database, we need to frequently monitor the space usage of the database. This article will introduce how to query the space size of Oracle database.

First, we need to use Oracle's own tool - SQL*Plus to connect to the database. Open a terminal window and enter the following:

sqlplus 用户名/密码@数据库实例名

where the username and password are the account and password you use to log in to the database, and the database instance name is the name of the instance you want to connect to.

After the connection is successful, we can use the following SQL statement to query the space usage of the database:

SELECT 
  FS.TABLESPACE_NAME, 
  ROUND(SUM(DS.BYTES) / 1024 / 1024, 2) AS "SIZE(MB)", 
  ROUND(SUM(FS.BYTES) / 1024 / 1024, 2) AS "FREE(MB)",
  ROUND(SUM((DS.BYTES - NVL(FS.BYTES, 0))) / 1024 / 1024, 2) AS "USED(MB)",
  ROUND((SUM(DS.BYTES - NVL(FS.BYTES, 0)) / DS.BYTES) * 100, 2) AS "USED_RATIO(%)"
FROM 
  DBA_FREE_SPACE FS RIGHT OUTER JOIN DBA_DATA_FILES DS 
    ON FS.TABLESPACE_NAME = DS.TABLESPACE_NAME 
GROUP BY 
  FS.TABLESPACE_NAME;

The above statement queries the space usage of each table space, including the table space name, total Size (in MB), free space size (in MB), used space size (in MB), and percentage of used space. This information will be output in table form.

In the output results, we can see the situation of each table space. For example, the total size of the table space named "USERS" is 100MB, the available space is 30.93MB, the used space is 69.07MB, and the percentage of used space is 69.07%. We can also modify the statement according to actual needs, and only query the status of a certain table space, or query the status of data files, etc.

After we query the space usage of the database, we also need to pay attention to manually cleaning unnecessary data and logs to avoid occupying unnecessary space and control the table space occupancy within a reasonable range.

In short, through the above SQL statement, we can easily query the space usage of the Oracle database and manage and maintain it in a timely manner. This can avoid system crashes due to insufficient space and ensure the security and stability of corporate data.

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