oracle查询数据占用过大,[oracle] 查看oracle数据库表空间使用率，定位数据size较大对象...

=========================================

1.查看表空间使用情况

=========================================

with ts as (SELECT d.tablespace_name "Name", d.status "Status",

TO_CHAR (NVL (a.MAXBYTES / 1024 / 1024 /1024, 0), '99,999,990.90') "Maxsize (G)",

TO_CHAR (NVL (a.BYTES / 1024 / 1024 /1024, 0), '99,999,990.90') "Size (G)",

TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024 /1024,

'99999999.99'

) USE,

TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.MAXBYTES * 100, 0),

'990.00'

) "Used %"

FROM SYS.dba_tablespaces d,

(SELECT   tablespace_name, SUM (BYTES) BYTES,SUM (greatest(BYTES,MAXBYTES)) MAXBYTES

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT   tablespace_name, SUM (BYTES) BYTES

FROM dba_free_space

GROUP BY tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')

UNION ALL

select c.tablespace_name as name,'tempts',

to_char(c.maxbytes/1024/1024/1024,'99,999.999') "Maxsize (G)",

to_char(c.bytes/1024/1024/1024,'99,999.999') "Size (G)",

to_char(d.bytes_used/1024/1024/1024,'99,999.999') USE,

to_char(d.bytes_used*100/c.maxbytes,'99.99') "Used %"

from  (select tablespace_name,sum(bytes) bytes,sum(greatest(maxbytes,bytes)) maxbytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from gv$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name)

select * from ts order by 6 desc;

=========================================

2.查看segment使用情况

=========================================

col segment_name for a50

select tablespace_name,segment_name,round(sum(bytes/1024/1024/1024),1) as sizeG from  dba_segments where tablespace_name in ('OLS_DATA','OLS_LOB','OLS_BAT') group by segment_name,tablespace_name order by 3;

=========================================

3.查看具体表空间lob使用情况

=========================================

col segment_name for a50

col table_name for a50

select l.table_name,l.segment_name,round(sum(s.bytes/1024/1024/1024),1) as sizeG from  dba_segments s,dba_lobs l where s.segment_name = l.segment_name and s.tablespace_name = 'OLS_LOB' group by l.table_name,l.segment_name order by round(sum(s.bytes/1024/1024/1024),1);