Oracle数据库查询表空间使用情况

SELECT
	a.a1 表空间名称,
	c.c2 类型,
	c.c3 区管理,
	b.b2 / 1024 / 1024 表空间大小M,
	(b.b2-a.a2)/ 1024 / 1024 已使用M,
	substr((b.b2-a.a2)/ b.b2*100, 1, 5) 利用率
FROM
	(
	SELECT
		tablespace_name a1,
		sum(nvl(bytes, 0)) a2
	FROM
		dba_free_space
	GROUP BY
		tablespace_name) a,
	(
	SELECT
		tablespace_name b1,
		sum(bytes) b2
	FROM
		dba_data_files
	GROUP BY
		tablespace_name) b,
	(
	SELECT
		tablespace_name c1,
		contents c2,
		extent_management c3
	FROM
		dba_tablespaces) c
WHERE
	a.a1 = b.b1
	AND c.c1 = b.b1;

SELECT
	b.file_name 物理文件名,
	b.tablespace_name 表空间,
	b.bytes / 1024 / 1024 大小M,
	(b.bytes-sum(nvl(a.bytes, 0)))/ 1024 / 1024 已使用M,
	substr((b.bytes-sum(nvl(a.bytes, 0)))/(b.bytes)* 100, 1, 5) 利用率
FROM
	dba_free_space a,
	dba_data_files b
WHERE
	a.file_id = b.file_id
GROUP BY
	b.tablespace_name,
	b.file_name,
	b.bytes
ORDER BY
	b.tablespace_name

SELECT
	*
FROM
	(
	SELECT
		segment_name 表名称,
		bytes/1024/1024 大小MB
	FROM
		dba_segments
	WHERE
		owner = 'YBXHBCP'
	ORDER BY
		bytes DESC )
WHERE
	rownum <= 30;