Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Statistical query of oracle tablespace usage

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

-- query tablespace usage SELECT Upper (F.TABLESPACE_NAME) "tablespace name", D.TOT_GROOTTE_MB "tablespace size (M)", D.TOT_GROOTTE_MB-F.TOTAL_BYTES "used space (M)", To_char (Round ((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2) '990.99') | |'%'"usage ratio", F.TOTAL_BYTES "Free Space (M)", F.MAX_BYTES "maximum Block (M)" FROM (SELECT TABLESPACE_NAME, Round (Sum (BYTES) / (1024 * 1024), 2) TOTAL_BYTES Round (Max (BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, Round (Sum (DD.BYTES) / (1024 * 1024)) 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 1-query the free spaceselect tablespace_name of the tablespace, count (*) AS extends,round (sum (bytes) / 1024 / 1024, 2) AS MB,sum (blocks) AS blocks from dba_free_space group BY tablespace_name -- query the total capacity of the tablespace select tablespace_name, sum (bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name -query tablespace usage SELECT total.tablespace_name, Round (total.MB, 2) AS Total_MB, Round (total.MB-free.MB, 2) AS Used_MB, Round ((1-free.MB / total.MB) * 100,2) | |'%'AS Used_PctFROM (SELECT tablespace_name) Sum (bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, Sum (bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) totalWHERE free.tablespace_name = total.tablespace_name

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report