rem Name:    dealloc.sql
rem Purpose: Deallocate unused space from table segments
rem Usage:   @dealloc <%owner.table%> <%type%> <%tablespace%> <min_kb>
rem Subject: space
rem Attrib:  sql
rem Notes:   Only dealloc segments of <min_kb> size or larger
rem          Use with caution
rem          enh: Add functionality to specify KEEP parameter?
rem SeeAlso: @cantext @segs @space

@setup1
set heading off
define ty="upper('&&2')"
define ts="upper('&&3')"
define kb="to_number('&&4')"

spool dealloc.tmp
select
  'alter '||segment_type||' '||owner||'.'||segment_name||' deallocate unused;'
from sys.dba_segments
where owner like &&o1
and segment_name like &&n1
and segment_type like &&ty
and tablespace_name like &&ts
and bytes/1024>=&&kb
order by owner,segment_name
;
spool off
@doit
set feedback on
@dealloc.tmp

undef ty ts kb

@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