1.查询表空间使用信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
set linesize 1000

select decode(status, 0, 'ONLINE', 'OFFLINE') as "Status      ",
       tablespace_name as "Tablespace Name    ",
       ts_type "TS_Type       ",
       trunc(ts_total) as "Tablespace Size",
       trunc(ts_total-used) as "Free (in M)",
       trunc(used) as "Used (in M)",
       trunc(pct_used) as "Pct. Used"
  from (select df.name as tablespace_name,
               'PERMANENT' as ts_type,
               status$ as status,
               max_size as ts_total,
               DECODE((max_size - used_size_t),  null, 0, max_size - used_size_t) as free,
               DECODE(used_size_t, null, 0, used_size_t) as used,
               DECODE(used_size_t, null, 0, round(cast(used_size_t / MAX_SIZE as decimal) * 100)) as pct_used
          from (select a.name,
                       a.id,
                       a.status$,
                       sum(cast(b.total_size - b.free_size as decimal) *
                           page() / 1024 / 1024) as used_size_p,
                       sum(case AUTO_EXTEND
                             when 0 then
                              cast(b.TOTAL_SIZE as decimal) * page() / 1024 / 1024
                             WHEN 1 THEN
                              b.MAX_SIZE
                           END) AS MAX_SIZE
                  from v$datafile b, v$tablespace a
                 where b.group_id = a.id
                   and a.name not in ('TEMP', 'ROLL')
                 group by a.name, a.id, a.status$) df
          left join (select ts_id,
                           sum(n_full_extent + n_free_extent + n_frag_extent) *
                           SF_GET_EXTENT_SIZE() * page() / 1024 / 1024 as used_size_t
                      from v$segment_infos
                     group by ts_id) seg
            on seg.ts_id = df.id
        union all
        select tablespace_name,
               'UNDO' as ts_type,
               status,
               ts_total,
               free,
               (total - free) used,
               round(cast((total - free) as DECIMAL) * 100 / ts_total) pct_used
          from (select t.NAME tablespace_name,
                       t.STATUS$ status,
                       sum(cast(free_size as decimal) * page() / 1024 / 1024) as free,
                       sum(cast(d.total_size as decimal) * page() / 1024 / 1024) as total,
                       sum(case AUTO_EXTEND
                             when 0 then
                              cast(d.TOTAL_SIZE as decimal) * page() / 1024 / 1024
                             WHEN 1 THEN
                              d.MAX_SIZE
                           END) AS ts_total
                  from v$tablespace t, v$datafile d
                 where t.ID = d.GROUP_ID
                   and t.name in ('ROLL')
                 group by t.NAME, t.STATUS$)
        union all
        select tablespace_name,
               'TEMPORARY' as ts_type,
               status,
               ts_total,
               free,
               (total - free) used,
               round(cast((total - free) as DECIMAL) * 100 / ts_total) pct_used
          from (select t.NAME tablespace_name,
                       t.STATUS$ status,
                       cast(free_size as decimal) * page() / 1024 / 1024 as free,
                       cast(d.total_size as decimal) * page() / 1024 / 1024 as total,
                       CASE (select sys_value
                           from v$parameter
                          where name = 'TEMP_SPACE_LIMIT')
                         WHEN 0 THEN
                          99999999
                         ELSE
                          (select sys_value
                             from v$parameter
                            where name = 'TEMP_SPACE_LIMIT')
                       end as TS_TOTAL
                  from v$tablespace t, v$datafile d
                 where t.ID = d.GROUP_ID
                   and t.name in ('TEMP')))
 order by ts_type,pct_used desc;

2.查询temp表空间使用信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select
       (b.total_size - b.free_size)*b.page_size/1024/1024 as used_mb,
        b.total_size*b.page_size/1024/1024 as total_mb,
        trunc(round(cast(b.total_size - b.free_size as decimal)/b.total_size * 100 )) as pct_used
from
        v$tablespace a,
        v$datafile b
where
        a.name = 'TEMP'
and     a.id   = b.group_id;

3.查询ROLL表空间使用信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000
set linesize 1000

select   decode(status, 0, 'ONLINE', 'OFFLINE') as "Status      ", 
         tablespace_name, 
         trunc(ts_total) as "Tablespace Size",
         trunc(ts_total-(total - free)) as "Free (in M)",
         trunc(total - free) as "Used (in M)",      
         round(cast((total - free) as DECIMAL) * 100 / ts_total) as "Pct. Used",
         total as HWM_mb,
         round(total * 100 / ts_total) hist_max_pct_used
       from (select t.NAME tablespace_name,
                       t.STATUS$ status,
                       sum(cast(free_size as decimal) * page() / 1024 / 1024) as free,
                       sum(cast(d.total_size as decimal) * page() / 1024 / 1024) as total,
                       sum(case AUTO_EXTEND
                             when 0 then
                              cast(d.TOTAL_SIZE as decimal) * page() / 1024 / 1024
                             WHEN 1 THEN
                              d.MAX_SIZE
                           END) AS ts_total
                  from v$tablespace t, v$datafile d
                 where t.ID = d.GROUP_ID
                   and t.name in ('ROLL')
                 group by t.NAME, t.STATUS$);

4.查询某表空间大于200M的前30个段对象信息

select segment_name, size_mb
from 
(
   select   owner || '.' || SEGMENT_NAME segment_name, 
            trunc(sum(BYTES)/1024/1024) size_mb 
   from     dba_segments 
   where    upper(tablespace_name)=upper('&v_tbsname')
   group by owner,SEGMENT_NAME
   having   sum(BYTES)/1024/1024>200
   order by 2 desc
)
where rownum<31;

5.查询某个段对象的大小

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select owner,
       segment_name,
       partition_name,
       bytes/1024/1024 size_m
from   dba_segments
where  upper(segment_name)=upper('&v_seg_name')
order  by 1,2,3;