rem Name:    cantext.sql
rem Purpose: List segments which cant extend because of space or extents
rem Usage:   @cantext <%owner.segment_name%> <%type%> <%tablespace%> <within>
rem Subject: space
rem Attrib:
rem Descr:
rem Notes:   if within<100 then within=#extents else within=space #kb
rem SeeAlso: @segs @segs2 @segs3

@setup1
define ty="upper('&&2')"
define ts="upper('&&3')"
define adj="to_number(replace('&&4','%','0'))"
define adjexts="decode(sign(&&adj-100),1,0,&&adj)"
define adjbyts="decode(sign(&&adj-100),1,&&adj*1024,0)"

column sname   format a19 heading "Segment Name"
column segment_type  format a10 heading "Type"
column tablespace_name format a12 heading "Tablespace"
column sizein  format a5  heading "Initl"
column sizenx  format a5  heading "Next"
column pct_incr   format a4  heading "%Inc"
column exts  format a7  heading "Ext/Max"
column extfree  format a4  heading "Ext%"
column sizebl  format a5  heading "Size"

column blocksize new_value bs
set termout off
select blocksize from sys.ts$&&db WHERE rownum < 2;
set termout on

select
  &&segown||'.'||s.segment_name sname,
  s.segment_type,
  s.tablespace_name,
  decode(trunc(s.initial_extent/(1024*1024)), 0,
    to_char(s.initial_extent/1024, '990')||'k',
    to_char(s.initial_extent/(1024*1024), '990')||'M') sizein,
  decode(trunc(s.next_extent/(1024*1024)), 0,
    to_char(s.next_extent/1024, '990')||'k',
    to_char(s.next_extent/(1024*1024), '990')||'M') sizenx,
  ltrim(to_char(s.pct_increase, '990'))||'%' pct_incr,
  ltrim(to_char(s.extents, '990'))||'/'||
    ltrim(to_char(s.max_extents, '990')) exts,
  ltrim(to_char(s.extents/DECODE(s.max_extents, NULL, 121, 0, 121,
    s.max_extents)*100, '990'))||'%' extfree,
  ltrim(decode(trunc(s.bytes/(1024*1024)), 0,
    to_char(s.bytes/1024, '9990')||'k',
    to_char(s.bytes/(1024*1024), '990')||'M'))
    sizebl
from &oru._segments s
where &&segown like &&o1
and s.segment_name like &&n1
and s.segment_type like &&ty
and s.tablespace_name like &&ts
and (
  s.extents>=s.max_extents-&&adjexts
  or not exists (
    select 0
    from &&oru._free_space
    where tablespace_name=s.tablespace_name
    and s.next_extent<=bytes-&&adjbyts
  )
)
order by &&segown, s.segment_name;

undef ty ts adj adjext adjbyts

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips