Re: Defrag for datafiles needed

Unfortunately direct load always appends data after your Table's High Water Mark. The high water mark is not reset on Deletes. Only a truncate or a recreation will reset the high watermark.

Your going to have to look for some sort of work around. The quickest method I can think of is to create a copy of the original data using "create table as select * from original" to moving a copy of the data into a temporary table, add any indexes, triggers etc to the copy and then drop the original and rename the copy to the original.

Regards

Fraser McCallum
MVP Oracle
www.brainbench.com

"Konstantinos Agouros" <elwood_at_news.agouros.de> wrote in message news:elwood.997558108_at_news.agouros.de...
> 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 Received on Sat Aug 11 2001 - 15:52:13 CDT