(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC
--也可以从视图DBA_TABLESPACE_USAGE_METRICS 查询如果表空间不是自动扩展则两者查询结果相同，如果是自动扩展的话则两者有偏差，以上面的查询为准
--查看用户使用了那些表空间
SELECT OWNER, OBJECT_TYPE, TABLESPACE_NAME
FROM (SELECT DISTINCT 'TABLE' OBJECT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_TABLES
UNION
SELECT DISTINCT 'INDEX' OBJECT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_INDEXES)
WHERE TABLESPACE_NAME IS NOT NULL
AND OWNER = 'EZOFFICE'
ORDER BY 1, 2, 3;
SELECT T.SEGMENT_NAME, T.TABLESPACE_NAME, BYTES / 1024 / 1024
FROM DBA_SEGMENTS T
WHERE T.SEGMENT_NAME IN
('')
AND OWNER = 'USER'
--可以查看回收的数据文件大小
SELECT 'ALTER DATABASE DATAFILE ''' || A.FILE_NAME || ''' RESIZE ' ||
ROUND(A.FILESIZE - (A.FILESIZE - C.HWMSIZE - 100) * 0.8) || 'M;',
A.FILESIZE || 'M' AS "数据文件的总大小",
C.HWMSIZE || 'M' AS "数据文件的实用大小"
FROM (SELECT FILE_ID, FILE_NAME, ROUND(BYTES / 1024 / 1024) AS FILESIZE
FROM DBA_DATA_FILES) A,
(SELECT FILE_ID, ROUND(MAX(BLOCK_ID) * 8 / 1024) AS HWMSIZE
FROM DBA_EXTENTS
GROUP BY FILE_ID) C
WHERE A.FILE_ID = C.FILE_ID
AND A.FILESIZE - C.HWMSIZE > 100;