Oracle-L: RE: Shrink datafiles

From: Ganesh Raja <ganesh_at_gtfs-gulf.com>
Date: Wed, 19 Jun 2002 07:23:31 -0800
Message-ID: <F001.00481355.20020619072331@fatcity.com>

Courtesy AskTom.oracle.com

Hope this helps....

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 /

Best Regards,
Ganesh R

Tel  : +971 (4)  397 3337  Ext 420
Fax  : +971 (4)  397 6262
HP   : +971 (50) 745 6019
============================================

Live to learn... forget... and learn again.

-----Original Message-----
Sent: Wednesday, June 19, 2002 6:28 PM
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: Ganesh Raja
INET: ganesh_at_gtfs-gulf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------