Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Command to free unused space in a data file

Re: Command to free unused space in a data file

From: <bdbafh_at_gmail.com>
Date: 2 Feb 2006 17:29:55 -0800
Message-ID: <1138930195.924596.172560@f14g2000cwb.googlegroups.com>


This statement will work, if all of the tablespaces block size is the same as the db_block_size.
Its probably not well tuned, but its not something that I typically run.

select file_name, trunc(total_blocks*vp.value/1048576) total_mb,

       trunc(shrinkage_possible*vp.value/1048576) shrink_mb,
       trunc((total_blocks-shrinkage_possible)*vp.value/1048576)
resize_mb
  from v$parameter vp, (

       select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible

         from dba_data_files df,
              (select file_id, max(block_id+blocks) hwm
                 from dba_extents
                group by file_id ) ext
        where df.file_id = ext.file_id)

 where vp.name='db_block_size'
 order by shrink_mb
/

-bdbafh Received on Thu Feb 02 2006 - 19:29:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US