Joerg.Sobottka

You wrote: "…Don't forget to purge the tablespace to remove dropped objects…."

PURGE TABLESPACE tablespace_name [ USER user_name ];

 This command removes all objects residing in the specified tablespace that are currently in the recycle bin.?? true??

Good idea. I had forgotten the recycle bin (so easy to forget).

Thank you everyone!!!! Excellent assistance. We are on our way to data removal and cleanup of database structure… simplifying how data is logically and physically stored.

This can be closed.

Hello bobcalvin,

and keep in mind that from the database point of view, the free space inside existing datafiles is as useful as free space outside datafiles… So if you free space by truncating / dropping segments, you will have datafiles with a lot of free space and they won't need to grow in size to allocate new extents, and OS free space will remain stable.

(but yes, you wrote «Customer needs at least 1 TB of OS disk space free» so maybe my point "free here or free there, what difference?" might be irrelevant)

Best regards,

Bruno Vroman.

Hi

Just to add some things:

Why you want to truncate the old partitions (if they are really unused for write and read) and don't drop them?

Don't forget to purge the tablespace to remove dropped objects.

And don't forget to create new optimizer statistics after the process.

BR

Jörg

There's no recycle mechanism in a way of making sure the database doesn't grown, by means of removing partitions on any regular basis, as the "rest" of the database will make sure the database grows. Unless you now have a massive chunk of your database stored in partitions you never ever use.

To me it looks like IF the partitions are created incorrectly, technically there's only one solution: remove these partitions and create new type of partitions. This is huge, obviously, but I don't see how else you can improve from that situation. After that, you can build a recycle mechanism on those partitions and depending on the size of data in there, it may improve things.

But, as you state: there's no new data inserted into the old partitions, yes, probably. But, is there data READ from the old partitions ? If so, that'll be the reason you can't delete, and that may the reason why you can't remove any partition. You then have to switch to remove records, in "old" partitions, but these becomes even more difficult, chance on error very high, and the gain ? … I don't know.

It all doesn't matter if the actual size of the file on the file system is reduced, that way freeing up space in the disk, no matter if that is an ASM disk or a regular file system disk. We may not know all details of how files are spread across disks on your site, but free space = free space.

If that free space is in disks you don't want to use anymore … that's an issue indeed.