Re: Shrinking datafiles

Note that there was a bug that would not let you 'create controlfile' for that database after a datafile was shrunk. So you couldn't restore from controlfile failures, and you couldn't copy your database to another location.

I'm pretty sure this was fixed in 7.3.3.

Yosi

Thomas Kyte wrote:

> A copy of this was sent to "as" <joe_at_isp.com>
> (if that email address didn't require changing)
> On Tue, 3 Aug 1999 13:09:00 +0200, you wrote:
>
> >Is it possible to shrink a datafile? The scenario is as such - after a few
> >months we have seen that our live tables are not going to grow anymore as we
> >archive data to an archive database. We initially set the tables to a large
> >initial extent but have dropped and imported them to new smaller tables. We
> >have a lot more free space available in the tablespace but this is not
> >needed.
> >Is it possible to reclaim this space in the physical file system by
> >shrinking the .dbf datafile???
> >
> >Thanking you in advance
> >Andre
> >
>
> If you have 7.2 or up you can resize datafiles and shrink them. They cannot be
> shrunk past the last extent allocate in them -- it is possible therefore to have
> a 100meg datafile with 1meg of data in it and NOT be able to shrink it at all
> (the 1meg of data is at the 'end' of the file).
>
> This script helps you find out what files are candidates for resizing, how small
> they can become and generates the needed DDL to shrink them:
> ---------------------------------------------------------------
> 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
> /
> ---------------------------------------------------------------
>
> it is written for use with sqlplus.
>
> --
> 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 Tue Aug 03 1999 - 15:14:02 CDT