| reclaim free space [message #254228] |
Thu, 26 July 2007 03:54  |
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 #254335 is a reply to message #254228] |
Thu, 26 July 2007 08:38  |
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 #254435 is a reply to message #254402] |
Thu, 26 July 2007 13:59 |
 |
Littlefoot
Messages: 21823
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:
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 #254503 is a reply to message #254455] |
Fri, 27 July 2007 00:00 |
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 #254511 is a reply to message #254505] |
Fri, 27 July 2007 00:26 |
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 #254517 is a reply to message #254514] |
Fri, 27 July 2007 00:41 |
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 #254528 is a reply to message #254518] |
Fri, 27 July 2007 01:08 |
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 |
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 ?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
