The easiest way to avoid defrag is to size the tablespace with equi
extents and create table which inherits these storage attributes.
elwood_at_news.agouros.de (Konstantinos Agouros) wrote in message news:<elwood.998247901_at_news.agouros.de>...
> In <3b7fa683.2160498_at_news.mobilixnet.dk> plovmand_at_hotmail.com (Kenneth Koenraadt) writes:
>
> >Hi Konstantin,
>
> >You face a very common problem with data reorganisation. Deleting rows
> >from a table keeps ALL the storage, and loading with direct = true
> >does not use the existing blocks (even if the blocks are empty),
> >instead it loads above the high-water mark. Result - your table
> >becomes more and more fragmented over time.
>
> >2 solutions :
>
> >1)Reorganise the table by
>
> > a) create table BACKUP as (select * from ORIGINAL_TABLE);
> > b) drop table ORIGINAL_TABLE;
> > c) ALTER TABLE BACKUP RENAME TO ORIGINAL_TABLE;
>
>
> >2) PARTITIONING the table (by the date field) could be very good idea.
> >Then cut away the partition with the rows that have aged out.
>
>
>
> >Solution 1) is simple, but expensive regarding I/O.
> I do this more or less now once a week.
>
> >Solution 2) is more elegant, more flexible and less costly.
>
> >IMHO, partitioning is a *very* powerful DBA feature. So don't hesitate
> >to learn AND use it.
> That sounds so to me, too. Could You give me a hint, if entering 'partitioning'
> into technets search engine would be enough?
>
> Konstantin
>
>
>
>
> >Regards,
> >Kenneth Koenraadt
> >Systems Consultant
> >Oracle DBA
> >plovmand@<no-spam>hotmail.com
>
>
> >On 18 Aug 2001 10:23:47 +0200, elwood_at_news.agouros.de (Konstantinos
> >Agouros) wrote:
>
> >>Hi,
> >>
> >>I have the following problem (8.1.6.0.3 on Solaris7):
> >>Once in the night I import roughly 2-5mio entries using sqlldr direct=true.
> >>After some work with the data I delete all of this data that's older than
> >>two days. So this one big table stays at about 10mio lines. I set my datafile
> >>to autoextend and it keeps growing nevertheless. I guess the directpath method
> >>simply appends to the end. So is there a way besides exporting/dropping/import-
> >>ing all tables to keep the datafile tight?
> >>
> >>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 Mon Aug 20 2001 - 04:15:53 CDT
