Re: Need help with resizing datafiles.....

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 19 Aug 1999 19:10:27 GMT
Message-ID: <37bc5699.288788766@newshost.us.oracle.com>

A copy of this was sent to paways_at_hotmail.com (Paul Ways) (if that email address didn't require changing) On Thu, 19 Aug 1999 10:38:26 -0800, you wrote:

>I need to resize some datafiles. What I am having trouble with is
>determining how much space each datafile is using and how much is left free.
>The only views I could find that reference datafiles only give you the total
>size of the datafile. There are no indicators as to how much of that space
>is used or free. Any suggestions as to how I can determine this info would
>be very much appreciated.
>
>
>
> -**** Posted from RemarQ, http://www.remarq.com/?b ****-
> Real Discussions for Real People

In 7.2 and up, you can resize a datafile. a datafile cannot be shrunk beyond the point at which data exists (consider a datafile to be a heap -- you might have 100meg allocated to it and 1meg of data actually in it. There is a good chance that if the 1meg of data is as the 'end' of the datafile, you will not be able to shrink that file very much).

This script shows you all of your datafiles and the maximum shrinkage possible for each datafile. for every datafile that can be shrunk, the alter statement is generated:

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 /

---

You run this in sqlplus. It doesn't DO the shrink (for that you must spool the output and run the commands) so it is harmless as is -- it will not modify the database.

--
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 Thu Aug 19 1999 - 14:10:27 CDT