Where have you been all my life?

September 10, 2010 cruepprich

Sometimes a solution comes along and you find yourself saying: “Where have you been all my life?” So it was when I did some googling on resizing datafiles. As it so often goes, one click goes to another. Pretty soon you find yourself deep in the jungle of Oracle related forums, and you’re afraid you may never find your way out.

Often you you’ll find what you’re looking for. But sometimes, you just come across something that just completely changes your (DBA) life.

So it was when I came accross Tom Kyte’s thread on resizing datafiles and his invaluable script max_shrink.sql.

This script looks at all your datafiles and determines to which size you can shrink them to. It also provides the necessary alter database datafile commands for your convenience.

I used this script to clean up a development database. It went from 19G down to 4G in a few minutes.

I wish I had known about this little gem years ago. I hope you’ll find it equally useful.

Here is Tom’s script:

[sourcecode language=”sql”]
–Script source: http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:153612348067
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
/
[/sourcecode]

Now you may not be able to shrink the datafile by any significant amount because a one or more segments may be hanging out in the blocks at the end of the data file. So in order to find out which segments are keeping you from shrinking the file you can run the following script:

[sourcecode language=”sql”]
–Script source: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065432640718
column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||’.’||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper(‘&1’)
UNION
select file_id, block_id, blocks,
‘Free’
from sys.dba_free_space
where tablespace_name = upper(‘&1’)
order by 1,2,3
/

[/sourcecode]

If you look at the end of the result set where the largest block numbers are, you will see the offending segments.