Where have you been all my life?September 10, 2010 at 3:04 pm | Posted in Oracle Developement, Oracle Performance | Leave a comment
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:
--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 /
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:
--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 /
If you look at the end of the result set where the largest block numbers are, you will see the offending segments.
More on this can be read on AskTom.