Home » RDBMS Server » Server Administration » Reclaim undused space
Reclaim undused space [message #209877] Mon, 18 December 2006 04:28 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

Is there any way to re-claim space for dropped tables? I believe that if we either use truncate tables or through exp/imp utilities we would be able to reclaim space.

But, I wish to know if there is any other alternate for re-claiming space if we dropped tables from the schema.

Your suggestion would be appreciated
Re: Reclaim undused space [message #209891 is a reply to message #209877] Mon, 18 December 2006 05:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Drop is almost same as truncate (truncate and delete are different. Delete will not reset HWM. Drop/truncate will reset HWM. Truncate removes the data. Drop remove the table itself).
If you want to reclaim the space in datafile level, you have shrink/resize the datafile.

Re: Reclaim undused space [message #209904 is a reply to message #209891] Mon, 18 December 2006 06:17 Go to previous messageGo to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I alredy removed few tables using drop table tablename. Now, I want to re-set HWM.

I wanted to know that using DROP clause, it would make a room at database level like truncate.

How to shrink the datafile by resizing the data file? Is there any mechanism where I can know for shrinking or re-sizing files at the os level.
Re: Reclaim undused space [message #209910 is a reply to message #209904] Mon, 18 December 2006 07:13 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you read the previous response?
>> I alredy removed few tables using drop table tablename. Now, I want to re-set HWM.
As i said early, drop table will reset the HWM
>>How to shrink the datafile by resizing the data file? Is there any mechanism where I can know for shrinking or re-sizing files at the os level.
Depends on the Oracle version.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96521/dfiles.htm#7459

in 10g you can just shrink the table ( You are dropping the table.so shrinking is of nouse)
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2092
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#CBBBIADA
or you can manually resize
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1365
Previous Topic: big Trouble; USER Deleted
Next Topic: Table Size
Goto Forum:
  


Current Time: Wed Dec 07 14:42:51 CST 2016

Total time taken to generate the page: 0.10984 seconds