Re: ORA-01654

"Richard" <qaz1521_at_hotmail.com> wrote in message news:bjmo6h$9g8$1$8302bc10_at_news.demon.co.uk...
>
> "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
> news:3F5DD0F2.EEFB087E_at_remove_spam.peasland.com...
> > > Does anybody know if there is an on line defragmentation tool
available
> for
> > > Oracle, like the tools that are used to defragment disk drives without
> > > reformatting?
> >
> > Why would you want such a tool? By defragmenting, what do you hope to
> > accomplish?
>
> Brian,
>
> I would hope this could reduce free space fragmentation and avoid the type
> of extension failure Jeff describes in his original post. I believe
> defragmenting the data doesn't do much for performance but an on line
> defragmentation tool could avoid the need to manipulate tablespaces.
>
> Regards,
>
> Richard

You can avoid this issue entirely at the design stage in one of two ways

1. As Sybrand describes set initial=next=<a constant value across all objects in a tablespace> and pctincrease=0 so that all extents are the same size and thus you can always allocate another one unless you are actually out of space.
2. Use locally managed tablespaces with uniform extent sizes, my preference as regulars will know, which enforces this.

Rather than get a tool to solve the problem, design the database so the problem doesn't happen.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK