Tips and wisdom from 15 years of DBA experience

Thursday, August 27, 2009

How Do I Shrink Datafiles to Reclaim space?

There is probably a lot of available space that could be re-claimed by your database server operating system, but it is locked up in Oracle datafiles. Fortunately it is possible to shrink Oracle datafiles. This works in 10.2 and possibly earlier versions, but I am not sure - if you are on anything earlier than 10.2, you have other issues to worry about : )

Here is what this Tom Kyte script will give you:

  • The block size your db is using
  • The possible savings by shrinking each datafile and a total possible savings
  • The SQL to shrink each datafile as far as it can go

-- This script was written by Tom Kyte and retrieved from asktom.oracle.com

set pages 0
set lin 150
set verify off
column file_name format a60 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"
column sum format 999,999,999
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 a95 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
/


11 comments:

Pavan said...

Dear Craig,

The first two scripts were good. The third one is throwing an error. Can you please suggest how to overcome the error.

SQL> column cmd format a75 word_wrapped
SQL> select
2 'alter database datafile '''/u01/oracle/oradata/VVAP1/VVA_IMA_DEC07_F1'''resize '
3 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) 'm;' cmd
from
4 5 dba_data_files a,
6 ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
7 where
8 a.file_id = b.file_id(+) and
9 ceil( blocks*&&blksize/1024/1024) -
10 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
11 /
'alter database datafile '''/u01/oracle/oradata/XXXX/XXXXXX'''resize '
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

Thanks in advance.
Joe

SK said...

The last section of code needed some tweaking. Here is the tweak:

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
/

Craig Glendenning said...

It's my blog, I can respond a year late if I want to : - )...Tom's script works fine as posted, but only if you remove extraneous return characters. Sorry about that.

Anonymous said...

"It's my blog, I can respond a year late if I want to"

Stupid blog then...

Anonymous said...

worthless comment...

Anonymous said...

Thanks for the info - really useful.

Anonymous said...

Thank you very much!

Anonymous said...

"Stupid blog than"...ignorant people will have ignorant comments.

Thnak you for the sripts ...

Anonymous said...

Thanks for posting...haters gonna hate.

Anonymous said...

great post - ignore the haters - thanks!!! feel free to post things 20 years later if you think it's helpful! (what the @!#&#@!* above doesn't realize is that it's up to end reader to determine the relevance of the post and whether or not to use the knowledge) again - thanks!

Anonymous said...

They see you rollin... They hatin'...

Followers