查看表空间使用情况

SELECT 
	TABLESPACE_NAME AS 表空间名,
	ROUND(SUM(TOTAL_SIZE) / 1024.0 / 1024.0, 2) AS "总大小(MB)",
	ROUND(SUM(USED_SIZE)/ 1024.0 / 1024.0, 2) AS "已使用大小(MB)",
	CAST(ROUND(SUM(USED_SIZE) * 1.0 / SUM(TOTAL_SIZE) * 100,2) AS VARCHAR) || '%' AS 使用率
FROM
(
	SELECT 
		UPPER(T1.TABLESPACE_NAME) AS TABLESPACE_NAME,
		T1.BYTES AS TOTAL_SIZE,
		T1.BYTES - T2.BYTES AS USED_SIZE
	FROM
		DBA_DATA_FILES T1,
		DBA_FREE_SPACE T2
	WHERE
		T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
		AND T1.FILE_ID = T2.FILE_ID
)
GROUP BY
	TABLESPACE_NAME;

查看表空间同数据文件对应关系

SELECT 
	TS.NAME AS 表空间名,
	DF.PATH AS 数据文件 
FROM 
	V$TABLESPACE AS TS,
	V$DATAFILE AS DF
WHERE 
	TS.ID = DF.GROUP_ID
ORDER BY 
	1;

查询表占用大小

SELECT
	S2.NAME AS 模式名,
	S1.NAME AS 表名,
	TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空间(MB)"
FROM
	SYSOBJECTS S1,
	SYSOBJECTS S2
WHERE
	S1.SCHID = S2.ID
	AND S1."SUBTYPE$" = 'UTAB'
	AND S2."TYPE$" = 'SCH'
ORDER BY
	3 DESC;