查看表空间使用率，表，用户大小

1.查看表空间使用率

set lin 200 pages 2000
col TABLESPACE_NAME for a32
col free_per for 999.00
col use_per for 999.00
col all_free_per for 999.00

select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024, 2) "allocate_size",
               round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "free_size",
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) "used_size",
               round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "free_per",
               100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "use_per",
               round(maxbytes /1024/1024, 2) Max,
               round((maxbytes-(a.bytes_alloc - nvl(b.bytes_free, 0)))/ 1024 / 1024,2) "all_free",
               round((1-(a.bytes_alloc - nvl(b.bytes_free, 0))/maxbytes)*100,2)  "all_free_per"
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name, sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+)
union all    ------ under is temp
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) /1024/1024, 2) "allocate_size",--MB
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1024/1024, 2) "free_size",
       round(sum(nvl(p.bytes_used, 0))/ 1024/1024, 2) "used_size",
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) "free_per",
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) "use_per",
       round(SUM(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) / 1024/1024, 2) max ,--MB
       round(SUM(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) / 1024/1024, 2)-round(sum(nvl(p.bytes_used, 0))/ 1024/1024, 2)"all_free",
       round(( (round(SUM(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) / 1024/1024, 2)-round(sum(nvl(p.bytes_used, 0))/ 1024/1024, 2)) / round(SUM(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) / 1024/1024,2) )*100,2)"all_free_per"
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 9;

2.查看表大小

set lin 200 pages 2000
col owner for a25
col TABLE_NAME for a30
col partitioned for a12
col local_index for a12

select t.name instance_name,
       t.owner,
       t.segment_name table_name ,
       t.GB,
       t.partitioned,
       decode(z.locality, 'LOCAL', 'Y', '', 'N') LOCAL_INDEX,
       sysdate
  from (select x.name,
               s.owner,
               s.segment_name,
               s.owner || '.' || s.segment_name table_name,
               sum(bt) GB,
               decode(y.partitioned, 'YES', 'Y', 'NO', 'N') partitioned
          from (select a.owner,
                       case
                         when a.segment_type = 'INDEX' or a.segment_type='INDEX PARTITION' or a.segment_name='LOBINDEX'  then
                          (select b.table_name
                             from dba_indexes b
                            where b.index_name = a.segment_name
                              and b.owner = a.owner)
                         when a.segment_type = 'LOBSEGMENT' or a.segment_type='LOBINDEX' or a.segment_type='LOB PARTITION'  then   
                          (select lb.TABLE_NAME 
                            from dba_lobs lb 
                            where lb.SEGMENT_NAME=a.segment_name  
                            and lb.OWNER=a.owner )     
                         else
                          a.segment_name
                       end segment_name,
                       round(bytes / 1024 / 1024 / 1024, 2) bt
                  from dba_segments a
                ) s,
               v$database x,
               dba_tables y
         where s.segment_name = y.table_name
           and s.owner = y.owner
           and --s.owner='PLT'
             s.owner not in 
('SYS','OGG','SCOTT','SYSTEM', 'OUTLN', 'WMSYS', 'DBSNMP', 'APPQOSSYS', 'EXFSYS','CTXSYS', 'XDB', 'ORDSYS',
'ORDDATA', 'ORDPLUGINS', 'MDSYS','OLAPSYS', 'SYSMAN', 'FLOWS_FILES', 'APEX_030200', 'DMSYS', 'TSMSYS')  
         group by x.name, s.owner, s.segment_name, y.partitioned
        having sum(bt) >= 0.1) t
  left join (select distinct owner || '.' || table_name table_name, locality
               from dba_part_indexes
              where locality = 'LOCAL') z
    on z.table_name = t.table_name
    order by t.GB desc;

3.查看用户大小

set lin 200 pages 2000
col USERNAME for a25
col ACCOUNT_STATUS for a18

select (select name from v$database) instacne_name,
       a.username,
       a.ACCOUNT_STATUS,
       decode(b.sizes, null, 0, b.sizes) sizes
  from dba_users a
  left join (select owner, round(sum(bytes) / 1024 / 1024 / 1024, 2) sizes
               from dba_segments
              group by owner) b
    on a.username = b.owner
 --where a.DEFAULT_TABLESPACE not in ('SYSAUX', 'SYSTEM')
   --and a.ACCOUNT_STATUS = 'OPEN'
 order by sizes desc;