Home » RDBMS Server » Server Administration » reclaim free space
reclaim free space [message #254228] Thu, 26 July 2007 03:54 Go to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Hi friends,

Mine is Oracle 10.2.0 on Windows Server 2003. I checked the free space using the dba_free_space, one tablespace showed around 13GB of free space. I have already used

alter table <table name> shrink space cascade  


to compact the table and indexes. I have even resized the datafile associated with this tablespace.

I want to release the free space(13GB) to OS.

Any suggestions ???


-Nand
Re: reclaim free space [message #254258 is a reply to message #254228] Thu, 26 July 2007 05:44 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
>>I have even resized the datafile associated with this tablespace

Did that help you ?
Re: reclaim free space [message #254266 is a reply to message #254258] Thu, 26 July 2007 06:06 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
After incrementally trying I resized it to 16G, its showing free space as 13G.

Nand
Re: reclaim free space [message #254335 is a reply to message #254228] Thu, 26 July 2007 08:38 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
>>I resized it to 16G, its showing free space as 13G

Sorry i dont understand what you say.

Since you see 13GB free space in tablespace level,you may Check the freespace in each datafile and shrink it accordingly.
Someone, please correct me if i am wrong.
Re: reclaim free space [message #254340 is a reply to message #254335] Thu, 26 July 2007 09:16 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
See...first i shrunk the segments, then i resized the datafile.
Re: reclaim free space [message #254402 is a reply to message #254340] Thu, 26 July 2007 12:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
try this on test SERVER before.
Re: reclaim free space [message #254435 is a reply to message #254402] Thu, 26 July 2007 13:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once upon a time, in Dreamland far far away, prince charming said
DreamzZ
Thanks for posting document;)

The King himself replied:
joy division
I think it was meant with a hint of sarcasm. You will have to search long and hard to find someone who is going to open up a .DOC file from someone in the internet who they don't know (or even that they do know).

Dazzled by the sun, our prince made another step to the right direction:

./fa/2785/0/

Then I woke up, wondering who wrote this very post here, instead of in the Community Hangout Pub.


[Updated on: Thu, 26 July 2007 14:01]

Report message to a moderator

Re: reclaim free space [message #254455 is a reply to message #254435] Thu, 26 July 2007 16:03 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
./fa/2786/0/
  • Attachment: d.gif
    (Size: 36.47KB, Downloaded 687 times)
Re: reclaim free space [message #254503 is a reply to message #254455] Fri, 27 July 2007 00:00 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Thankyou dreamzz for that script...

When I ran it, it showed for (a datafile)

16,075 as the smallest possible size
16,384 as the current size
309 as the savings..

and when I queried dba_free_space for that tablespace( it has a single datafile) it showed

13594.9375 i.e. around 13GB of free space

My question however is ..is it possible to reclaim this free space back to the OS...or reduce further the size of the datafile...or i suppose the best way is to move the contents to a new tablespace whose size is initially set to a low value and auto extended as more segments are moved into it. drop the previous tablespace and rename the new one to the dropped one.
Re: reclaim free space [message #254505 is a reply to message #254228] Fri, 27 July 2007 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>auto extended as more segments are moved into it. drop the previous tablespace and rename the new one to the dropped one
YES, but with disk space so cheap why are you forced to such action for so little in disk space and actual cost savings?
Re: reclaim free space [message #254511 is a reply to message #254505] Fri, 27 July 2007 00:26 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Actually its not the problem of disk space...my requiremnt is to speed up the process of transporting that database over the network to a different geographical location...(to create a standby database). And since my network bandwidth is less it will take too much time...thats the resaon i decided to atleast reduce the size of the datafile.

[Updated on: Fri, 27 July 2007 00:27]

Report message to a moderator

Re: reclaim free space [message #254514 is a reply to message #254228] Fri, 27 July 2007 00:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Actually its not the problem of disk space..
So you mis-state your real problem & waste folks time.
You're Own Your Own (YOYO)!
Re: reclaim free space [message #254517 is a reply to message #254514] Fri, 27 July 2007 00:41 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
I didn't mis-state my problem....and sorry if i have wasted the precious time of the forum users... but the problem still persists...reduce disk space ...standby is not a problem for me. actually iam looking at various options to simplify the task.

[Updated on: Fri, 27 July 2007 00:42]

Report message to a moderator

Re: reclaim free space [message #254518 is a reply to message #254228] Fri, 27 July 2007 00:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I want to release the free space(13GB) to OS.

>Any suggestions ?

You NEVER mentioned network transfer!

You're On Your Own (YOYO)!
Re: reclaim free space [message #254528 is a reply to message #254518] Fri, 27 July 2007 01:08 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
See...

If you are releasing free space....it means you are reducing the database size. Now database size if reduced can help in a lot of other tasks as well....say taking a backup whether its cold or hot. So the end result is to reduce the time it takes...thats it.

[Updated on: Fri, 27 July 2007 01:09]

Report message to a moderator

Re: reclaim free space [message #254591 is a reply to message #254228] Fri, 27 July 2007 04:18 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
To shrink a datafile,there must be space avilable at the end of datafile.
Did you try a alter tablespace coalesce; ? then alter datafile ?
Re: reclaim free space [message #254616 is a reply to message #254591] Fri, 27 July 2007 04:58 Go to previous message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
No. I resized directly. I didn't use alter tablespace coalesce
Previous Topic: 7.3.3 client <-> 9.2.x or 10.2.x server?
Next Topic: Database hasn't coming up after rebooting.
Goto Forum:
  


Current Time: Fri Dec 09 23:32:33 CST 2016

Total time taken to generate the page: 0.10248 seconds