Home » RDBMS Server » Server Administration » Decreasing Tablespace size (Oracle 10.2 - Windows environment)
Decreasing Tablespace size [message #318127] Mon, 05 May 2008 17:49 Go to next message
azaza
Messages: 2
Registered: April 2008
Location: Columbia, Maryland - USA
Junior Member
Why can't I decrease the datafile of a tablespace? A friend of mind tried to decrease 13G datafile size, but got an error. Please see the error below as he mentioned:

"Tablespace has data of about 3G only and size is 13 g
When i run
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BBLPRD02\BBL01.DBF' RESIZE 10G
I get the following error message..
Failed to commit: ORA-03297: file contains used data beyond requested RESIZE value
any help would be grtly appreciated"

Report message to a moderator

Re: Decreasing Tablespace size [message #318128 is a reply to message #318127] Mon, 05 May 2008 17:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Why can't I decrease the datafile of a tablespace?
Failed to commit: ORA-03297: file contains used data beyond requested RESIZE value

What part of the message above do you NOT understand?
Realize that it is NOT the amount of data within the tablespace, but the location of the extents within the tablespace that controls the amount of space that can be reclaimed.

If you want to shrink the tablespace as much as possible,
move all the objects within the tablespace to a different tablespace.
Then you will be able to resize to any size you deem appropriate.

Report message to a moderator

Re: Decreasing Tablespace size [message #318129 is a reply to message #318128] Mon, 05 May 2008 18:04 Go to previous messageGo to next message
azaza
Messages: 2
Registered: April 2008
Location: Columbia, Maryland - USA
Junior Member
Anacedent, thank you it makes lot of sense.

Meanwhile, if I export or move all objects, will that defraqment the locations of the extents after the resize?

Albert

Report message to a moderator

Re: Decreasing Tablespace size [message #318130 is a reply to message #318127] Mon, 05 May 2008 18:11 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>if I export
doing an export all by itself won't have any effect on the tablespace

>or move all objects
(re)moving all objects from tablespace will allow the tablespace to be sized to any value without producing an error.

>will that defraqment the locations of the extents after the resize?
yes, but likely not have any noticeable impact afterwards.

Report message to a moderator

Previous Topic: 10.2.0.4 patch-set released
Next Topic: Redo Log
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 06 16:37:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.