2

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

3
  • 1
    DELETE only removes records from the table; it never deallocates the segments from the data files, and never coalesces empty space within the data files. Apr 29, 2011 at 3:49
  • 1
    What do you mean "supposedly"? In what way do you think that DELETE doesn't delete the data? (i.e. DELETE+COMMIT removes the records from the table so that subsequent (non-flashbacked) queries will not return those records) Apr 29, 2011 at 3:52
  • Hi Jeffrey, the delete DML does delete the data, however not the disk space that one would suppose it freed, hence "supposedly", my bad.
    – user66296
    Apr 29, 2011 at 8:07

4 Answers 4

5

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.

2
  • Hi ik_zelf, thank you for your thoughts. One question, does the whole operation you described introduce any downtime?
    – user66296
    Apr 29, 2011 at 8:08
  • There will be downtime[s] mainly caused by the rebuild of indexes that became unusable during table move[s]. A lot of downtime can be prevented using dbms_redefinition but not all, there will at least be short outages.
    – user69489
    May 1, 2011 at 9:04
1

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 adrci Oracle command line utility, then do:

adrci> set homepath diag/rdbms/yourinstance/yourinstance
adrci> purge -age 10080

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

0

Try this command to get some space back:

DUMP TRAN <database name> WITH NO_LOG

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.

1
  • 7
    I don't think DUMP TRAN is a valid command in Oracle. Apr 29, 2011 at 3:42
0
EPADMRT > @resizea

CURRENT_MEG    SAVINGS    ID TABLESPACE_NAME RESIZE_COMMAND
----------- ---------- ----- --------------- ---------------------------------------------------------------------------------------
      30000         -1     3 UNDO_TS         alter database datafile '/var/oradata03/PADMRT/PADMRT_UNDO_01.dbf' resize 30001m;
      30000         -1    19 UNDO_TS         alter database datafile '/var/oradata04/PADMRT/PADMRT_UNDO_02.dbf' resize 30001m;
      20000         -1    11 OPLA_DATA       alter database datafile '/var/oradata01/PADMRT/OPLA_DATA01.dbf' resize 20001m;
      15360         -1     8 AGILEODM_INDX   alter database datafile '/var/oradata01/PADMRT/AGILEODM_INDX_DATA02.dbf' resize 15361m;
      20480         -1    13 AGILEODM        alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA04.dbf' resize 20481m;
       5120         -1     7 AGILEODM_INDX   alter database datafile '/var/oradata01/PADMRT/AGILEODM_INDX_DATA01.dbf' resize 5121m;
      20480         -1    14 AGILEODM        alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA05.dbf' resize 20481m;
 32767.1875     -.8125     6 AGILEODM        alter database datafile '/var/oradata01/PADMRT/AGILEODM_DATA01.dbf' resize 32768m;
      13236          0     5 AGILEODI        alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA01.dbf' resize 13236m;
      22784          6     9 AGILEODI        alter database datafile '/var/oradata02/PADMRT/AGILEODI_DATA02.dbf' resize 22778m;
      32767         30    10 AGILEODM        alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA02.dbf' resize 32737m;
      10240         42    20 AGILEODI        alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA03.dbf' resize 10198m;
       3000         51    16 VQA_DATA        alter database datafile '/var/oradata03/PADMRT/VQA_DATA02.dbf' resize 2949m;
       3000         72    15 VQA_DATA        alter database datafile '/var/oradata01/PADMRT/VQA_DATA01.dbf' resize 2928m;
       3000         79    18 VQA_DATA        alter database datafile '/var/oradata04/PADMRT/VQA_DATA04.dbf' resize 2921m;
       1335        151     4 USERS           alter database datafile '/var/oradata01/PADMRT/users01.dbf' resize 1184m;
      10240        202    21 AGILEODI        alter database datafile '/var/oradata01/PADMRT/AGILEODI_DATA04.dbf' resize 10038m;
       6144        938     2 SYSAUX          alter database datafile '/var/oradata02/PADMRT/PLMDM_SYSAUX_DATA1_01.dbf' resize 5206m;
       4096       1058     1 SYSTEM          alter database datafile '/var/oradata02/PADMRT/PLMDM_SYS_DATA1_01.dbf' resize 3038m;
      32767       1391    22 OPLA_DATA       alter database datafile '/var/oradata01/PADMRT/OPLA_DATA02.dbf' resize 31376m;
      10000       7053    17 VQA_DATA        alter database datafile '/var/oradata03/PADMRT/VQA_DATA03.dbf' resize 2947m;
      32767       7492    12 AGILEODM        alter database datafile '/var/oradata02/PADMRT/AGILEODM_DATA03.dbf' resize 25275m;
      32767       8749    23 AGILEODM        alter database datafile '/var/oradata03/PADMRT/AGILEODM_DATA06.dbf' resize 24018m;

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

You must log in to answer this question.