oracle 10G shrink datafiles

Question

how do i shrink datafiles in oracle 10G?

Answer 1

Caveat: I am not an Oracle system administrator, other than for personal installs. Take everything I say with a large grain of salt.

I'm assuming that you created the datafiles with auto-extend, and they've been extended past what you feel they should contain. There is a clause to ALTER DATABASE that will resize a file, here's the example from the Oracle SQL reference:

ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;

However, I really don't think you want to do this blindly.A better approach IMO would be to use the export command to dump the tables that are in that datafile, then recreate the tablespace.

Answer 2

Here is a way, courtesy of Tom Kyte to get the block size of your db, list how much space you can possibly reclaim, and to build the alter... commands to actually perform the database shrinks. Hope this helps,

http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html

Craig Glendenning

Answer 3

The ALTER TABLESPACE....RESIZE is only allowed beyond the HWM. So you might have many unused segmnets below it. Before this operation, issue an:

ALTER TABLE .. .SHRINK SPACE on some tables of that tablespace/datafile in order to reorganize the contents of the datafiles.

Might be a long task, but you can generate the commands with SQL.

Answer 4

@Dave's answer about exporting and importing is correct and the best choice to free up the space. Also in Oracle there is no shrink command, @Dave is right but there exists resize command which as @David said "Data files can be resized down to the last used block" BUT there is one mentionable thing, that free blocks in datafile may be allocated like that

0101000001111000000000000000000001110000000000
                                    |---------

where: 0-is free block 1-is used block

by resizing datafile till last used block it will become like that:

010100000111100000000000000000000111
                                    |---------     

But what about other free blocks in datafile?? They are not available for other datafiles or system itself.

If the datafile were like that:

1111111111111100000000000000000

then resizing would be usefull but not in previous variant.

Anyway to identify till what size you are able resize datafile, here is the script:

    select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id 
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id

Don't worry this script will not cut any used blocks from datafile.

Answer 5

For standard datafiles in Oracle, you can't shrink them. You would have to do something like:

1. Move the segments to another tablespace, or export and drop them
2. Drop the datafile
3. Create a new smaller datafile
4. Move the segments back to the first tables, or import them from the dump file

For a "bigfile" tablespace -- meaning that CREATE BIGFILE TABLESPACE was used to create it -- you can use ALTER TABLESPACE .. RESIZE ...