Re: Query for shrinking datafile
- From: Jared Still <jkstill@xxxxxxxxx>
- To: s.cislaghi@xxxxxxxxx
- Date: Mon, 3 May 2010 08:36:01 -0700
On Mon, May 3, 2010 at 12:49 AM, Stefano Cislaghi <s.cislaghi@xxxxxxxxx>wrote:
> Hi all,
>
> do you use any query or method to discover how much space you can save on a
> datafile? I want to say...suppose to shrink your datafile, which is the
> minimum size, according to the highest HWM position, you can reach for your
> datafile?
>
> Which is the easiest way to achieve this?
>
>
HWM for a data file cannot be reliably found by querying DBA_SEGMENTS
as at least one poster has suggested.
That method will work if the objects in the tablespace have never been
dropped,
rebuilt, or otherwise relocated within the data files.
Attached is a script with one way to do it.
There are other methods to for doing this as well.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
-- hwm_df.sql
-- jkstill@xxxxxxxxx
-- 11/06/2008 - complete rewrite
-- for tablespaces that are completely devoid of objects
-- (aka empty) this report will assume 128k is the minimum file size
-- this may or not be correct for your implementation
-- and probably is not terribly important to worry about
@clears
@title 'Datafile High Water Marks' 150
col file_name format a50 head 'FILE NAME'
col file_size format 999,999,999,999 head 'FILE SIZE|BYTES'
col shrink_size format 999,999,999,999 head 'SHRINK TO|SIZE BYTES'
col tablespace_name format a15 head 'TABLESPACE'
col bytes_saved format 999,999,999,999 head 'BYTES SAVED'
col max_block_id format 999999999 head 'MAX BLOCK ID'
break on tablespace_name skip 1
break on report
compute sum of bytes_saved on tablespace_name
compute sum of bytes_saved on report
compute sum of file_size on tablespace_name
compute sum of file_size on report
compute sum of shrink_size on tablespace_name
compute sum of shrink_size on report
with maxext as (
select file_id, max(block_id) max_block_id
from dba_free_space
group by file_id
),
shrink as (
select
t.tablespace_name
, f.file_id
, f.file_name
, m.max_block_id
, f.blocks * t.block_size file_size
, case
when ((nvl(m.max_block_id,f.blocks+1)-1) * t.block_size) >=
(128*1024)
then(nvl(m.max_block_id,f.blocks+1)-1) * t.block_size
else
128*1024
end shrink_size
from maxext m
, dba_data_files f
, dba_tablespaces t
where m.file_id(+) = f.file_id
and t.tablespace_name = f.tablespace_name
and t.contents = 'PERMANENT'
)
select
s.tablespace_name
, s.file_id
, s.max_block_id
, s.file_name
, s.file_size
, s.shrink_size
, (s.file_size - s.shrink_size) bytes_saved
from shrink s
order by tablespace_name, file_id
/
Other related posts:
