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

1、查看所有表空间使用情况（包含temp表空间）

set linesize 300
set pagesize 300
select t.tablespace_name,x.contents tablespace_type,t."SUM_SPACE(M)",t.SUM_BLOCKS,t."USED_SPACE(M)",t."USED_RATE(%)",t."FREE_SPACE(M)"
from (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)", 
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" 
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL 
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",SPACE - USED_SPACE "FREE_SPACE(M)" 
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_TEMP_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE,ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE FROM V$tempseg_USAGE GROUP BY TABLESPACE) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t,dba_tablespaces x
where t.tablespace_name=x.tablespace_name 
order by "USED_RATE(%)" desc;

2、sysaux表空间各组件占用率

 SELECT occupant_name"Item", round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",schema_name "Schema",move_procedure "MoveProcedure" 
 FROM  v$sysaux_occupants ORDER BY 2 Desc;

3、查看表空间中表的大小

col owner for a20 
col segment_name for a20
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE,TABLESPACE_NAME,HEADER_FILE ,HEADER_BLOCK,BYTES,BLOCKS ,EXTENTS ,INITIAL_EXTENT,NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS ,MAX_SIZE ,RETENTION ,MINRETENTION,PCT_INCREASE,FREELISTS ,FREELIST_GROUPS,RELATIVE_FNO,BUFFER_POOL,BYTES/1024/1024/1024 from dba_segments order by BYTES/1024/1024/1024 desc;

4、查看数据文件使用情况

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.file_name ;