Re: Defrag for datafiles needed

From: Kenneth Koenraadt <plovmand_at_hotmail.com>
Date: Sat, 11 Aug 2001 20:02:52 GMT
Message-ID: <3b758d1a.20687307@news.mobilixnet.dk>

On 11 Aug 2001 21:31:28 +0200, elwood_at_news.agouros.de (Konstantinos Agouros) wrote:

>Hi,
>
>I have the following problem (Oracle 8.1.6.0.3 on Solaris):
>I have an application that once a night import up to 5 million entries
>using sqlldr with option direct=true. I set the file to autoextend=true.
>The data is reduced and afterwards deleted (using delete from where date < ...
>not truncate). The datafile keeps growing as direct=true seems to ignore free
>blocks and appends for performance reasons. Is there a way besides exporting,
>dropping and importing the whole database to keep the datafiles to a minimum?
>
>Konstantin
>--
>Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood_at_agouros.de
>Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
>----------------------------------------------------------------------------
>"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres

Hi Konstantin,

 "delete from" keeps the storage, unlike truncate. Not much to do about that.

There are more than one solution to your problem, e. g.

1. Partition the table (by the date field) could be very good idea. Then cut away the partition with the rows that have aged out.
2. Reorganise the table by
3. create table BACKUP as (select * from ORIGINAL_TABLE);
4. drop table ORIGINAL_TABLE;
5. ALTER TABLE BACKUP RENAME TO ORIGINAL_TABLE;

Regards,
Kenneth Koenraadt
Systems Consultant
Oracle DBA
plovmand@<no-spam>hotmail.com Received on Sat Aug 11 2001 - 15:02:52 CDT