Re: Can I "Compress" a tablespace?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Sep 1999 16:02:02 -0400
Message-ID: <Kr=WN7GbO1xK2C+VqmqB36ZYJV8E@4ax.com>

A copy of this was sent to Connor McDonald <connor_mcdonald_at_yahoo.com> (if that email address didn't require changing) On Wed, 08 Sep 1999 18:46:55 +0800, you wrote:

>Bob wrote:
>>
>> In article <7r3spf$3k8$1_at_nnrp1.deja.com>,
>> Ben Ryan <benryan_at_my-deja.com> wrote:
>> > ALTER DATABASE DATAFILE '/directory/filename' RESIZE number M;
>>
>> Thank you very much, Ben.
>>
>> Bob
>>
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.
>
>Note that you can only resize a tablespace downwards if the space you
>are trying to reclaim has NEVER been used...
>
>HTH
Thats not exactly true.

You can never resize a tablespace downwards.

You can resize a datafile downwards though -- EVEN if the space had at one time been used.

You can have a case where you have a 100meg datafile that is full, drop a 50meg table that was contained in that datafile and then shrink the file down to 50meg.

You can also have a case with that 100meg datafile that is full and you drop that same table and you cannot reclaim ANY space.

It all depends on how the space is allocated in the datafile. We cannot shrink a datafile smaller then where the last *currently* allocated extent is. I have a script (below) that shows you the max you can shrink any one of you datafiles by -- the datafiles could have had allocated space in them that will be "shrunk" away though.

set verify off
column file_name format a50 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"

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 a75 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 /

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 08 1999 - 15:02:02 CDT