|Reclaiming Unused Space in Datafiles - This article shows several methods for reclaiming unused space from datafiles.|
Martin said...A nice feature (I think starting with 10g) is that empty datafiles can be dropped from tablespaces now as well (e.g. if accidentially a wrong file was added).
John said...Thanks for puting this together. Very useful, but in my testing I found lots of issues. I think you should point out:
John said...oops. hit enter before adding the issues.
1. reorganize in oem requires host based access from oem to the target db host.
2. if the tablespace contains any objects which contain timestamp or lob you cannot use the reorganize option.
3. you did not mention coalesce (which may be because it's so useless).
4. if oem can generate a script to do the reorganization online, it would be nice to see.
Diego said...Hi there, thanks for the wrap up. I like how you show the manual steps before jumping into graphical tools.
If you go for an export/import approach, I have found that you can use "transform=segment_attributes:n" in the impdp command prevent Oracle from remembering how much space (extents) it should give to each table.
Yes. Nice point. I've added a reference to that at the end of that section. It doesn't affect this example as I used a truncate, but if I had performed lots of deletes on the tables, it could well have resulted in more space savings.
eMarcel said...SuperB! Clear and useful article. Thanks!
Wouter said...Great post
I reorganized a tblsp using EM and a scratch tblsp . This crashed because of a ctxsys index that could not be moved. The tables that were already moved where left in the SCRATCH tblsp.
Solved this by dropping the ctxsys indexes, restarting the reorg. Then used EM to do a reorg of the SCRATCH tblsp and copied the first half of the proposed SQL to move the objects back
Manfred Milhofer said...Generally great site, thanks, but this time you have spoiled my day I'm afraid..
I went for the new tablespace/move objects/rename tablespace back to original name approach
I hit this:
ORA-959 Tablespace '_$deleted$0$0' Does Not Exist Error Executing Some Code (Doc ID 604648.1)
Now database is dead. Ho hum
Bad luck. There is a fix for 10.2, 11.2 and it is fixed in 12.1. Im sure if you make a fuss it will get ported to 11.2.
Of course, you take a full backup before any structural changes right? That's what DBAs do...
I've added a note to the article about your point.
Manfred said...Hi Tim, sorry I came across a bit shirty, was a long day with a classic Oracle ending, not your fault! I saw the fix, think I will just avoid the problem for now
And yes I had a backup
No worries. Your warning was useful. Adding the note to the article will hopefully help prevent others from suffering the same fate. :)
Nigel Dams said...Hi and thanks for the very informative article. I have a question (maybe a bit of a newbie question, but ...) how come after performing the first example of exporting, dropping, recreating and importing the reclaim_user tablespace, the query
"select table_name, num_rows, blocks from dba_tables where owner = 'RECLAIM_USER';"
says t1 AND t2 both contain 10000 rows? Confused.
Check the logs of both the export and import. You will see that the statistics were exported and imported. Since the statistics weren't re-gathered after the truncate, they still say 10000.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!