luyan
asked on
How to really clean the tablespace?
Why cleaned up some big tables, but it still showed the tablespace space was used up?
If you move or drop a table, then *ALL* of the space formerly used by the table gets returned to the tablespace as "free" space.
If you truncate a table, then *SOME* of the space formerly used by the table gets returned to the tablespace as "free" space, *IF* the table had grown to multiple extents. But the "initial" extent remains allocated to the table, even though it is now empty.
If you just delete some (or even all) records then *NO* space gets returned to the tablespace as "free" space.
If you truncate a table, then *SOME* of the space formerly used by the table gets returned to the tablespace as "free" space, *IF* the table had grown to multiple extents. But the "initial" extent remains allocated to the table, even though it is now empty.
If you just delete some (or even all) records then *NO* space gets returned to the tablespace as "free" space.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in which case, the blocks assigned to the tables do NOT get returned as free space to the tablespace, but only as free space inside the table.
in order to reclaim space in such a case is to recreate the table, or use TRUNCATE instead of DELETE (assuming you delete all the rows).
in case you do NOT delete all the rows from a table, you need to recreate the table with an intermediate step of a temp table holding the rows to be kept.