RE: Shrink datafiles

From: <John.Hallas_at_vodafone.co.uk>
Date: Wed, 19 Jun 2002 07:48:44 -0800
Message-ID: <F001.00481476.20020619074844@fatcity.com>

This script was posted by Mohammed Rafiq last yea. It should meet your requirements

John

>>>>>>>>>>>>>>>>>>

set verify off
set pages 60
col file_size_mb format 99999
prompt This script provides a report useful for resizing datafiles prompt You should perform a 'ALTER TABLESPACE tsname COALESCE' before running
prompt this script to ensure you are getting all free space at end of the file
accept table_space prompt "Enter a tablespace_name or all: "

select ddf.file_name,
       dfs.file_id,
       ddf.blocks,
       (ddf.blocks*value)/1024/1024 file_size_mb,
       dfs.block_id block_hwm,
       ddf.blocks-dfs.block_id fat_blocks,
       floor(((ddf.blocks-dfs.block_id)*value)/1024/1024)
fat_mb,
       ceil(((ddf.blocks*value)/1024/1024 -
         ((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to
  from dba_free_space dfs,
       dba_data_files ddf,
       v$parameter

where v$parameter.name = 'db_block_size'

   and (ddf.tablespace_name = UPPER('&&table_space')

        or 'ALL' = UPPER('&&table_space'))

   and dfs.tablespace_name = ddf.tablespace_name
   and dfs.file_id = ddf.file_id
   and dfs.block_id = (select max(block_id)
                         from dba_free_space
                        where file_id = dfs.file_id)

  order by fat_blocks desc;

<<<<<<<<<<<<<<<

-----Original Message-----
Sent: 19 June 2002 15:28
To: Multiple recipients of list ORACLE-L

Does anyone have a script that will show how much I can shrink datafiles? I have
some bloated databases that I need to reclaim space from and thought if someone
had one handy I wouldn't have to write it:)

Thanks, Dave

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David Turner
  INET: turner_at_tellme.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 19 2002 - 10:48:44 CDT