如何查看ORACLE空间占用问题
最近,项目上ORACLE数据库越来越大,需要查看下数据库数据量占用大的原因。
1、查看ORACLE表空间及表数据大小
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents group By Segment_Name order by Sum(bytes)/1024/1024 desc;
2、从返回的结果看,有一个segment名为"SYS_LOB0000053810C00004$$"的对象占用了大量的空间;即LOB(BLOB和CLOB)对象占用的大小对象
3、根据segment_name,就可以从 dba_lobs 表里查到是哪个表,哪个字段;
select * from dba_lobs where segment_name like 'your_segment_name';
里面显示的TABLE_NAME和COLUMN_NAME为表名和字段名。
set pagesize 200
column "数据文件名称" format a25
column "表空间名称" format a18
column "容量(M)" format a12
column "使用率%" format a12
column "最大容量(M)" format a15
column "可扩充容量(M)" format a15
column "空闲(M)" format a12
column "自动扩展" format a10
select a.FILE_NAME "数据文件名称",a.TABLESPACE_NAME"表空间名称",
to_char(100-round((nvl(b.free_bytes,0)/a.BYTES)*100,2)) "使用率%",
to_char(round(a.BYTES/1024/1024,2)) "容量(M)",
to_char(round(nvl(b.free_bytes,0)/1024/1024,2)) "空闲(M)",
to_char(round(decode(a.autoextensible,'YES',a.maxbytes/1024/1024,'NO',a.bytes/1024/1024),2)) "最大容量(M)",
a.autoextensible "自动扩展",
to_char(round(decode(a.autoextensible,'YES',a.maxbytes/1024/1024,'NO',a.bytes/1024/1024)-a.BYTES/1024/1024,2)) "可扩充容量(M)"
from dba_data_files a,
(select FILE_ID,sum(bytes) free_bytes from dba_free_space group by FILE_ID) b
where a.FILE_ID=b.FILE_ID
order by to_number("可扩充容量(M)") asc;