Re: Shrink Many Files

Antoine BRUNEL wrote:
> Hi from Paris
>
> a litlle select on DBA_EXTENTS, grouping by FILE_ID and with max
> (BLOCK_ID+BLOCKS) will give max used space on each db files...
>
> <cdavis10717_at_comcast.net> a écrit dans le message de news:
> 1105558713.668279.29440_at_z14g2000cwz.googlegroups.com...
> >I have a tablespace comprised of 450 files.
> >
> > I recently dropped a 400GB table from this tablespace. The table
has
> > 27000 extents.
> >
> > I'd now like to re-size each of the 450 files to its smallest
possible
> > size.
> >
> > I'd like to have a script that can determine each file's smallest
> > possible size and generate the RESIZE syntax, which I will spool
and
> > run.
> >
> > Does anyone have a query that produces this required syntax?
> > Thanks.
> >
> > C
> >

Thanks for the info. This is the final query I wrote and ran for the 8K blocks of my PSAPBTABD tablespace:

spool x.sql
select
'ALTER DATABASE DATAFILE '''||a.name||' RESIZE '||b.blocks||'K;' from
v$datafile a,
(
select
file_id,
max(blocks) blocks
from
(
select
file_id,
(block_id+blocks)*8 blocks
from
dba_extents
where
tablespace_name = 'PSAPBTABD'
)
group by
file_id
) b
where

        b.file_id = a.file#
order by

        a.name
;
spool off
@x.sql Received on Thu Jan 13 2005 - 12:03:39 CST