Re: datafile's high water mark...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/24
Message-ID: <6pjndso4rm9kb89f7pefmvlbq6m18r1vqr@4ax.com>#1/1

A copy of this was sent to andreyNSPAM_at_bookexchange.net (NetComrade) (if that email address didn't require changing) On Thu, 23 Mar 2000 19:23:17 GMT, you wrote:

>I am planning to backup the db (it's no longer in use) and would like
>to decrease the files to the minimum, tar and compress, does anybody
>know where I can find datafile's high water mark (so I can run a SQL
>script making something like
>slect ALTER DATABASE DATAFILE RESIZE||' <hwm>||';' from some sys_view.
>
>Thanx.
>---------------
>In case I forgot to mention:
>We use Oracle 7.3.4 and 8.1.6 on Solaris 2.6, 2.7 boxes
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email

Here is a script to show you the size a file could be and then generates the alter statement for all files that can be shrunk.

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 /

-- 
http://osi.oracle.com/~tkyte/
 
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 Fri Mar 24 2000 - 00:00:00 CST