Re: Looking for hard disk recovery utility for Oracle 8

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 18 Aug 1999 12:14:41 GMT
Message-ID: <37c1a326.177313122_at_newshost.us.oracle.com>

A copy of this was sent to Manfred Mann <manfred37_at_excite.com> (if that email address didn't require changing) On Tue, 17 Aug 1999 15:30:20 -0700, you wrote:

>From what I understand about Oracle 8, that disk space is allocated as
>needed by the DBMS as the database stores more and more data. If the
>database is cleared out (e.g., the user is dropped) and the tables
>rebuilt, the space allocated for data by the DBMS is not reduced.
>
>If this is actually the case and not my misunderstanding, then I figure
>that there must be a utility for recovering some disk space. If you
>know of such a tool, I would greatly appreciated some info about it.
>
>-Manfred

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 Wed Aug 18 1999 - 14:14:41 CEST