Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

How to shrink space in Oracle tablespaces after large DELETE operations

...

Shrink tables / adjust high-water-mark

...

-- Enable row movement.

...


ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

...


-- Recover space and amend the high water mark (HWM) for the object and all dependant objects.

...


ALTER TABLE scott.emp SHRINK SPACE CASCADE;

This can be generated like:

...

select 'alter table '||owner||'.'||table_name||' shrink space cascade;'

...

 
 from dba_tables

...

 
 where (table_name like '%'

...

 
 and owner like '%');

resize datafiles

...

-- This script was written by Tom Kyte and retrieved from asktom.oracle.com

...


set pages 0

...


set lin 150

...


set verify off

...


column file_name format a60 word_wrapped

...


column smallest format 999,990 heading "Smallest|Size|Poss."

...


column currsize format 999,990 heading "Current|Size"

...


column savings  format 999,990 heading "Poss.|Savings"

...


column sum format 999,999,999

...


break on report

...


compute sum of savings on report

...


column value new_val blksize

...


select value from v$parameter where name = 'db_block_size'

...


/
select file_name,

...


      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

...


      ceil( blocks*&&blksize/1024/1024) currsize,

...


      ceil( blocks*&&blksize/1024/1024)

...

 -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

...


from dba_data_files a,

...


    ( select file_id, max(block_id+blocks-1)

...

 hwm
        from dba_extents

...


       group by file_id ) b

...


where a.file_id = b.file_id

...

(+)
/
column cmd format a95 word_wrapped

...


select 'alter database datafile ||file_name|| resize ' ||

...


      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd

...


from dba_data_files a,

...


    ( select file_id, max(block_id+blocks-1)

...

 hwm
        from dba_extents

...


       group by file_id ) b

...


where a.file_id = b.file_id

...

(+)
 and ceil( blocks*&&blksize/1024/1024)

...

 -
     ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

...


/

Table shrinking is redo log intense, so it is an option to disable archive logging, if possible, before performing a larger operation of this kind.