Shulou(Shulou.com)05/31 Report--

This article will explain in detail the script about how to query the use of tablespaces in the database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

The script is as follows:

Select Distinct a.Tablespace_Name

Trunc (Free_Space) / Total_Space) * 100) | |'%'

To_Char (Free_Space / 1024 / 1024 / 1024, '9999999990.99') | |' G' Free_Space

To_Char (Total_Space / 1024 / 1024 / 1024, '9999999990.99') | |' G' Total_Space

C.autoextensible

C.maxbytes/1024/1024/1024 | |'G' maxbytes

(c.increment_by*8192) / 1024 Compact 1024 | |'M'

From (Select Tablespace_Name, Sum (Bytes) Free_Space

From Dba_Free_Space

Group By Tablespace_Name) a

(Select Tablespace_Name, Sum (Bytes) Total_Space

From Dba_Data_Files

Group By Tablespace_Name) b

Dba_data_files c

Where a.Tablespace_Name = b.Tablespace_Name and a.tablespace_name = c.Tablespace_Name and c.tablespace_name = b.Tablespace_Name

Order by 2

The explanation is as follows:

Tablespace_Name-the name of the tablespace.

Trunc (Free_Space) / Total_Space) * 100) | |'%'- the free rate of the tablespace. The smaller the ratio, the larger the remaining space of the data, and the larger the proportion, the smaller the remaining space of the data.

To_Char (Free_Space / 1024 / 1024 / 1024, '9999999990.99') | |' G' Free_Space-- how much space is left in the tablespace. The size is G, and you can adjust it according to your actual situation. Can To_Char (Free_Space / 1024 / 1024, '9999999990.99') | |' M 'Free_Space, the calculated result is calculated as M.

To_Char (Total_Space / 1024 / 1024 / 1024, '9999999990.99') | |' G' Total_Space-the total size of the tablespace. The size is G, and you can adjust it according to your actual situation.

C.autoextensible,-whether automatic growth is enabled for data files. Yes means it is enabled, while ON means it is not enabled.

C.maxbytes/1024/1024/1024 | |'G' maxbytes,-the maximum size of the data file. The unit is G, to put it bluntly, that is, after the data file grows to this value, it will no longer grow automatically. In general, the maximum size of a data file is 32g. In a production environment, a data file is generally added, and a data file is 30g, which does not grow automatically.

(c.increment_by*8192) / 1024amp 1024 | |'M'-how much does the data file increase each time after the automatic growth is enabled? the size is M.

Query the specific information of a table space

Select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 | | 'from dba_data_files where TABLESPACE_NAME=' from dba_data_files where TABLESPACE_NAME=' Tablespace name'

This is the end of the article on "how to query the use of tablespaces in the database". I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.