8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Reclaiming Unused Space in 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.

Tim... said...

Hi.

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.

Cheers

Tim...

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

Tim... said...

Hi.

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...

Cheers

Tim...

Tim... said...

Hi.

I've added a note to the article about your point.

Cheers

Tim...

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
Manfred

Tim... said...

Hi.

No worries. Your warning was useful. Adding the note to the article will hopefully help prevent others from suffering the same fate. :)

Cheers

Tim...

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.

Tim... said...

Hi.

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.

Cheers

Tim...

Mugo said...

Thanks for the transform=segment_attributes:n tip, it helped me overcome 'ORA-12953: The request exceeds the maximum allowed database size of 11 GB' on oracle XE.

Ren said...

I am afraid the most important one is missing, and that's SYSAUX. But I understand that's almost impossible to shrink....

Tim... said...

Hi.

The most important? I guess that depends on your view point. I've certainly never been in a position where I've needed to shrink it, so I'm guessing you've stored stupid stuff in there, like APEX and audit trails etc.

Never put stuff into SYSAUX that doesn't need to be there. Move unnecessary, or large, occupants out into their own tablespaces.

Cheers

Tim...

Ren said...

Hi Tim. I come to your blog for years now and never said thanks for doing one of the best Oracle blogs, really, thanks for the good job. Regarding SYSAUX issue. I see more and more often people moving AWR to AWR warehouse and because you can't move SM data and also because those who run AWR dwh usually do so to keep history as long as possible...well, you got the picture, it's not uncommon to see

Ren said...

...SYSAUX having 32+GB with 99% free after moving SM/AWR out.

Tim... said...

Hi.

Checked some MOS notes. Shrinks and reorgs of the SYSAUX tablespace using moves are supported, so you can clean it up like any other tablespace. I added a note about it to the article.

Cheers

Tim...

JT said...

Hi Tim,
Great article, thanks.Comment re: your links:
•Online Move of a Table in Oracle Database 12c Release 2 (12.2)
... The link is invalid.


Tim... said...

Hi.

Thanks for the heads-up. I've corrected the link now. :)

Cheers

Tim...

melle said...

ora-12953 the request exceeds the maximum allowed database size of 11 gb

Tim... said...

Hi.

As discussed in the article, many of the methods involve making new copies of the objects, which take extra space. If you are working on Oracle XE, which has a size restriction, these may not be suitable for you.

This appears to be your problem, so use a method that doesn't suffer from this, like the export/import method.

Cheers

Tim...

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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.