| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help with resizing datafiles.....
I would add one caveat to this - something that has worked for me in the past.
I have a script which maps out a tablespace/datafile(s). What I have found is that sometimes I find that my ability to shrink a tablespace/datafile is hindered by just a few extents of an object that are stored near the end of the tablespace and there is a big gap of unused space between those extents and the next highest extents. If I don't have a lot of constraints to worry about sometimes what I will do is simply move that object to another tablespace temporarily (I've even been known to create a tablespace just for this purpose), shrink the tablespace and then move the object back. Another alternative is simply to export the object, drop it and then do the shink followed by an import.
Thomas Kyte wrote:
> 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.
>
 |
 |