I would like to know how usually one would delete data from an Oracle 9i DBMS which actually frees up disc space. Past scenario, we have had cases where clearing up 1-2 million rows of data does not translate to a decrement in disc space usage.
Scenario:
sqlplus > delete from audit_log where date_created between today and the day before;
sqlplus > 2 million records deleted.
bash$: du -sh (after issuing the delete above)
Results in no change to the disc space usage.
Which also brings me to the question, will one need to flush anymore tables in order for oracle to completely delete all the data that is supposedly deleted by the delete DML
In Oracle deleting rows from a table won't automatically release any disk space. It is possible to release disk space but to accomplish this you have to find out how the tables are physically placed in the datafiles. As soon as a datafile has empty blocks on the end, you can resize the datafile to a smaller size. Only after this - successful - operation you get real disk space back. If you have got a lot of empty blocks in a datafile but not on the end, it might be easiest to move the tables from the tablespace to which the datafile belongs into a new tablespace and drop the old tablespace. This won't work for the SYSTEM tablespace, you are not allowed to move SYS objects to an other tablespace.
Sometimes you are lucky when you can move just one - small - table that happened to block releasing space because it was placed at the end of a datafile. In that case a simple
alter table thesmalltable move;will relocate that table and make reclaimable space at the end of the datafile[s]. After that,alter database datafile '/your/df/name.dbf' resize the_new_size;releases disk space.A table is logically placed in a tablespace. A tablespace consists of minimal 1 datafile, in many cases multiple datafiles.
To complete other answers, purging diag logs can help you free some significant disk space (up to several GBs).
Check this : http://www.databasejournal.com/features/oracle/article.php/3875896/Purging-Oracle-Databases-Alert-Log-with-ADRCI---Usage-and-Warning.htm
Basically, you will run the
adrciOracle command line utility, then do:The example above will remove diag traces older than 1 week.
As well, check if you have locally managed tablespaces (LMT) or directory managed tablespaces (DMT). The former is supposed to handle fragmentation better (however far from perfect). Check: http://www.orafaq.com/node/3
Try this command to get some space back:
I think the best way to get hard disk space back is by removing any other no longer used files from your hard disk.
The usual way to delete data from the database is executing the delete command, however that will not necessarily free up disk space.
The full resizea.sql follows:
set termout off drop table alan99; set pages 50 set wrap off col resize_command for a180 col id for 9999 create table alan99 as select max(block_id+blocks) block_id,file_id from dba_extents group by file_id; column tablespace_name format a15 column pct format 999.99 set wrap off set lines 320 set trunc off column file_id format 999 set termout on select b.bytes/1024/1024 CURRENT_meg,b.bytes/1024/1024-trunc(c.block_id*8192/1024/1024+1) savings, b.file_id id,b.tablespace_Name,'alter database datafile '''||b.file_name||''' resize ' ||trunc(c.block_id*8192/1024/1024+1)||'m;' resize_command from dba_data_files b, alan99 c where b.file_id=c.file_id order by 2; set termout off drop table alan99; set termout on