526互联

oracle查询表空间占用sql

发布时间 2023-11-15 09:54:15作者: 许孟

查询表空间占用

SELECT
	tablespace_name "表空间名",
	100 "已使用空间(MB)",
	0 "剩余空间(MB)",
	100 "总的预分配空间(MB)",
	100 || '%' "已使用百分比",
	0 || '%' "剩余百分比"
FROM
	(
	SELECT
		tablespace_name
	FROM
		dba_tablespaces
	WHERE
		contents = 'PERMANENT'
MINUS
		SELECT
			DISTINCT tablespace_name
		FROM
			dba_free_space )
UNION ALL
SELECT
	fs.tablespace_name "表空间名",
	(df.totalspace - fs.freespace) "已使用空间(MB)",
	fs.freespace "剩余空间(MB)",
	df.totalspace "总的预分配空间(MB)",
	round(100 *((df.totalspace - fs.freespace)/ df.totalspace), 2)|| ' %' "已使用百分比",
	round(100 *(1-(df.totalspace - fs.freespace)/ df.totalspace), 2)|| ' %' "剩余百分比"
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;