Reclaim Space in a Tablespace After Purging

[kwil38]

I have purged some old data out of a couple of tables, all of which belong to the same tablespace. After doing this, I expected my tablespace size to decrease, however it remained exactly the same.

Is there another step or a compression utility that I need to run to reclaim this space?

Thanks, I'll hang up and listen...

 

[carp]

Yes - rebuild the table(s) that you purged the data from. Also, after rebuilding a table, you'll need to rebuild any indexes built upon it.

 

[SantaMufasa]

Kwil,

There are a couple of interpretations of "reclaim...space":

1) Make the freed tablespace space available to Oracle for use by other objects in the same tablespace, and,

2) Make the freed tablespace disk space available to the file system for use by the operating system.

If you have DELETEd data from one or more tables, the "deleted" space is available for reuse only by that table for new or expanded rows. To make the space available to other tables/objects in the same tablespace, you can simply say:

ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;

This causes a very fast reorganisation of the table, freeing up intra-table "Swiss Cheese" holes of freespace to the tablespace. If you empty out a table by using the "TRUNCATE TABLE <tablename>;" command, then the freed space returns automatically to the tablespace for re-use by the same, or other, tables.

If you wish to follow Option 2, above (to return freespace to the file system for re-use/re-allocation by the o/s to other Oracle or non-Oracle files), then you must "empty out" the entire tablespace into another tablespace, then drop the Oracle datafiles that formerly provided storage space to the newly emptied tablespace. To do this, you can

1) export to a dump file all of the Oracle users/schemas that have objects in the tablespace that you have targetted for dropping. Then,

2)

DROP TABLESPACE <tablespace_name>;

CREATE TABLESPACE <same tablespace_name>
DATAFILE '<same filename as before>' 10M [B]REUSE[/B]
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE; -- (my preference)

3) import the dump file that you created in step 1.

The above strategy returns to the operating system's file system all of the "Swiss Cheese" freespace that may have existed amongst all Oracle objects in the subject tablespace.

If any questions remain, please follow up.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[kwil38]

Thanks for the info guys.

Santa - #2 is what I'd like to do.

I've also been researching the ALTER TABLESPACE tablespacename COALESCE; command. However, if I'm understanding correctly, it won't actually decrease the size of my tablespace, it will just group free extents together.....sort of like defragmenting a hard drive.

 

[SantaMufasa]

...it will just group free extents together...

Correct...and (rather strangely) only if the free extents are contiguous...Gee, thanks Oracle, for the big help. All that buys you is bigger free extents, which is good, but not as helpful as we would hope for, huh.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Gents,

thanks for the Q&A session. I thought coalesce did something other than it did.

So much hassle to plug the cheese holes!

I've just furthered my education.

Regards

Tharg.

Grinding away at things Oracular

 

[kwil38]

Santa - After thinking on it, I may have changed my mind.

The 'ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;' sounds like it would be the easiest approach and would free the space for other tables in the tablespace to use. At the rate our database grows, that space would be utilized fairly quickly.

Do you know of any problems associated with using this command?

 

[kwil38]

Just a follow-up....

I used the 'ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;' to give the space back to the tablespace and all worked fine. No adverse effects whatsoever.

Thanks a lot guys!

 

[carp]

Kwil -
Please be sure to check the status of your indexes - they should need to be rebuilt after moving a table.

 

[kwil38]

Carp - Yes, I did have to rebuild the indexes associated with each table.