Oracle-sql语句检测数据库日志文件占用磁盘空间过大问题

SELECT

TableName = obj.name,

TotalRows = prt.rows,

[SpaceUsed(KB)] = SUM(alloc.used_pages)*8

FROM sys.objects obj

JOIN sys.indexes idx on obj.object_id = idx.object_id

JOIN sys.partitions prt on obj.object_id = prt.object_id

JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id

WHERE

obj.type = 'U' AND idx.index_id IN (0, 1)

GROUP BY obj.name, prt.rows

ORDER BY TotalRows DESC;

--======================================================================

--查看数据库文件使用的磁盘空间使用情况

WITH T1 AS (

SELECT DISTINCT

REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,

CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,

CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs

)

SELECT

Drive_Name,

Total_Space_GB,

Total_Space_GB-Free_Space_GB AS Used_Space_GB,

Free_Space_GB,

CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent

FROM T1;