After surfing google for days i have found the most simple and clear example to reclaim the free space in the tablespace after delete. I hope this helps
Link: http://www.dbforums.com/oracle/976248-how-reduce-tablespaces-used-space-after-delete-records-2.html
solution:
ALTER TABLE MOVE demo
Let's create a table with 9999 rows in it, each sized around 1k:
SQL> create table t (x char(1000) default 'x' primary key);
Table created.
SQL> insert /*+ append nologging */ into t(x) select rownum from all_objects where rownum < 10000;
9999 rows created.
SQL> commit;
Commit complete.
The table has 29 extents allocated to it, for a total of 14.6M:
SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 14680064
Let's delete ALL of the rows:
SQL> delete from t;
9999 rows deleted.
SQL> commit;
Commit complete.
Now- "surprise" - the table still uses the same extents:
SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 14680064
Why ? Because even if you delete all the rows of the table, the High Water Mark is not decreased - it is never decreased, to allow for maximum concurrency (Oracle is dead serious about maximizing concurrency i.e. performance and scalability; it's the main reason behind its success in Enterprise applications).
Deallocating the unused space (=space above the HWM) doesn't help much (since there is not a lot of unused space above the HWM):
SQL> alter table t deallocate unused;
Table altered.
SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
29 13959168
Now, let's MOVE the table, which in essence means to clone the table (including triggers, constraints and so on), transfer the rows, drop the "old" table and rename the new - all made by the kernel, so super-safe even in case of machine/server failure:
SQL> alter table t move;
Table altered.
Now, we have now only the initial extent allocated:
SQL> select count(*), sum(bytes) from user_extents where segment_name='T';
COUNT(*) SUM(BYTES)
---------- ----------
1 65536
Caveat: it normally happens that many/all of the indexes on the table are UNUSABLE after the move (not in this case but i'm running 9.2.0.4, the latest release, which has probably optimized the process in case of totally empty tables):
SQL> col table_name form a30
SQL> col index_name form a30
SQL> set lines 123
SQL> select table_name, index_name, status from user_indexes where table_name='T';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ ------------------------
T SYS_C002573 VALID
If STATUS were not VALID, you could simply rebuild manually the index(es):
SQL> alter index SYS_C002573 rebuild;
Index altered.
Or you could automate the whole process:
set serveroutput on size 100000
begin
for n in (select index_name from user_indexes where status <> 'VALID') loop
dbms_output.put_line ('rebuilding ' || n.index_name);
execute immediate 'alter index ' || n.index_name || ' rebuild';
end loop;
end;
/
As an example, let's manually set the index to UNUSABLE:
SQL> alter index SYS_C002573 unusable;
Index altered.
SQL> set serveroutput on size 100000
SQL> begin
2 for n in (select index_name from user_indexes where status <> 'VALID') loop
3 dbms_output.put_line ('rebuilding ' || n.index_name);
4 execute immediate 'alter index ' || n.index_name || ' rebuild';
5 end loop;
6 end;
7 /
rebuilding SYS_C002573
PL/SQL procedure successfully completed.
HTH
Alberto
