OraFAQ Forum: Server Administration » How can I make free space after "delete" operation ??

Home » RDBMS Server » Server Administration » How can I make free space after "delete" operation ??

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I make free space after "delete" operation ?? [message #127302]

Mon, 11 July 2005 03:04

babita
Messages: 3
Registered: July 2005
Location: India

Junior Member

Hi,
we have performed some delete operations on the schema. It shud freed some space from datafile, but as we used "delete" space is still allocated to the object.
How can I freed this unused space? (analyze/COALESCE...??)
Can I make free space on HD also?
Thanks in advance.

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127307 is a reply to message #127302]

Mon, 11 July 2005 03:24

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

HI

Delete command doesn't deallocate any storage space already allocated to the segment concerned as it doesn't move the HWM for the segment.

Regds
Girish

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127316 is a reply to message #127307]

Mon, 11 July 2005 04:28

babita
Messages: 3
Registered: July 2005
Location: India

Junior Member

ya that's true.
I want to know is there any way to freed up the space after deletion.
Thanks.

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127357 is a reply to message #127316]

Mon, 11 July 2005 06:47

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

try moving table to new tablespace & then check.

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127400 is a reply to message #127357]

Mon, 11 July 2005 09:34

macdba
Messages: 27
Registered: May 2005
Location: US

Junior Member

alter table table_name move;
This will release the space and set the high water mark to the new value.
After this make sure to rebuild indexes for this table since indexes become invalid.

rgds
--Mak

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127407 is a reply to message #127302]

Mon, 11 July 2005 09:51

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

And just to add, your datafiles will stay the same size, even with the segments getting smaller via a move or other operation. Datafiles are meant to be allocated once and be fixed (unless autoallocate) so that oracle isn't constantly getting and freeing space from and to the operating system.

Check out the concepts guide for more details.

Report message to a moderator

Re: How can I make free space after "delete" operation ?? [message #127520 is a reply to message #127407]

Tue, 12 July 2005 01:25

babita
Messages: 3
Registered: July 2005
Location: India

Junior Member

Thanks to all 4 u'r replies.

Report message to a moderator

Previous Topic:	Transportable tablespace error
Next Topic:	physical layout

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 10 20:58:38 CDT 2025