[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);