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.