View the table space usage in Oracle

Today, we will mainly describe how to use Oracle to view tablespaces. We hope you will learn how to use Oracle to view tablespaces. If you are interested in the actual operations, you can click to view the following articles. The following is an introduction to the text.

SELECT df. tablespace_name "tablespace ",

(Df. totalspace-NVL (fs. freespace, 0) "used (MB )",

NVL (fs. freespace, 0) "remaining (MB )",

Df. totalspace "Total (MB )",

ROUND (100 * (1-NVL (fs. freespace, 0)/df. totalspace), 2) "usage (% )"

 
 

  
  
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace  
  
  
FROM dba_data_files  
  
  
GROUP BY tablespace_name) df,  
  
  
(SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) freespace  
  
  
FROM dba_free_space  
  
  
GROUP BY tablespace_name) fs  
  
  
WHERE df.tablespace_name = fs.tablespace_name(+)  
  
  
ORDER BY 5 DESC;  
  
  
/*  
 
 

Oracle queries whether the table space needs to be compressed. SQL

Rem because Free Space Fragmentation is composed of several parts, such as the number of ranges and the maximum size of ranges, we can use FSFI -- Free Space Fragmentation Index) to intuitively reflect

Rem can change the default storage parameter pctincrease of a tablespace to a value other than 0. Generally, it is set to 1, so that SMON will automatically merge the free range. You can also manually merge the free range.

 
 

  
  
rem FSFI Value Compute  
  
  
column FSFI format 999,99  
  
  
*/  
  
  
select tablespace_name,sqrt(max(blocks)/sum(blocks))
*(100/sqrt(sqrt(count(blocks)))) FSFI  
  
  
from dba_free_space  
  
  
group by tablespace_name order by FSFI desc;  
 
 

The preceding content describes how to view the table space usage in Oracle. We hope it will help you in this regard.