Unanswered: reclaim table space without disturbing active application
i have a real time application that uses Oracle 8i and the application will keep adding some events in the tables... when i see the tablespace becomes 90%, i started deleting some old data... but it didn't free up the table space... since the application couldn't be stopped/disturbed in any way, what is the best methodology to reclaim the table space....
An Oracle database file is not like a flat file that when you remove lines, it shrinks in size. I have attached a link to the database layout information in the 8I concepts manual. Please check it out.
Using DELETE wouldn't deallocate unused space for the table, it will reduce the number of rows on it, but will allocate space at to its high water mark. You can, however, deallocate this unused space once you delete the rows with an ALTER TABLE table_name DEALLOCATE UNUSED.