Oracle 查看数据库空间使用情况

-- 1、查看所有表空间大小
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

-- 2、未使用的表空间大小
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

-- 3、使用的表空间大小
SELECT
	TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') 日期,
	df.*,
	fs.Free_MB,
	( df.Total_MB - fs.Free_MB ) Used_MB 
FROM
	( SELECT tablespace_name, SUM( dba_data_files.bytes )/1024/1024 Total_MB FROM dba_data_files GROUP BY tablespace_name ) df,
	( SELECT tablespace_name, SUM( dba_free_space.bytes )/1024/1024 Free_MB FROM dba_free_space GROUP BY tablespace_name ) fs 
WHERE
	df.TABLESPACE_NAME = fs.TABLESPACE_NAME 
	-- AND df.TABLESPACE_NAME='HIS_DATA'
ORDER BY
	df.TABLESPACE_NAME;

-- 以下 SQL 语句有 Bug：dba_free_space 表中同一 tablespace_name 有多条记录时，会导致 Total_MB SUM 计算出错
SELECT
SELECT
	df.tablespace_name,
	SUM( df.bytes ) / 1024 / 1024 Total_MB,
	SUM( fs.bytes ) / 1024 / 1024 Free_MB,
	SUM( df.bytes - fs.bytes ) / 1024 / 1024 Used_MB
FROM
	dba_data_files df,
	dba_free_space fs 
WHERE
	df.tablespace_name = fs.tablespace_name 
GROUP BY
	df.tablespace_name 
ORDER BY
	df.tablespace_name;