Re: tablespace extents, i think

In article <TIM.94Mar25131416_at_sun1.claremont.com>, Tim Maggio <tim_at_sun1.claremont.com> wrote:
>
>One table is about 50 MB, which I drop and recreate every week or so
>(during testing). Along with the recreation process, I build several
>indexes. The first one is usually about 13 MB or so. This time, Oracle
>complains with:
> ORA-01547: failed to allocate extent of size 5120 in tablespace 'DEANDATA'

after many rounds of create/drop of tables and indexes, the free space in the tablespace is now fragmented in such a way that, altho you have sufficient free space in total, you do not have enough in one contiguous extent. that's what the error message says.

to cure the problem, export the schema, drop and cre-create the tablespace, then import the schema. your free space will be "coagulated" into one large extent.

to avoid the problem in the future, i recommend:

1. keep tables and indexes in separate tablespaces, if possible on different physical drives;
2. tablespaces should have default storage parameters with (pctincrease 0) or (pctincrease 100) BUT NEVER (pctincrease 50). these measures make index and table references from conflicting with each other (for performance) and avoid creating odd-size free extents which encourage fragmentation. -- -- Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450 dlm_at_dlogics.com Motto: Just show me the code