oracle 10G shrink datafiles

Question

how do i shrink datafiles in oracle 10G?

Answer 1

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 ...

Answer 2

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 3

What is advantages and disadvantage for using BIGFILE TABLESPACE?

Answer 4

Hi Roman,

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 5

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.