Re: logical storage philosophy?

Sybrand Bakker wrote:

> "Fred A G" <nospam_at_allowed.localhost> wrote in message
> news:BwuL6.4782$vR1.16338_at_nntpserver.swip.net...
> > Bon jour, tout le monde!
> >
> > I am searching through Metalink and other sites for a source on storage
> > parameters policies, but nothing much comes up. Aren't there any around?
> > I am also interested to hear about the philosophies used by the database
> > management people here, when it comes to Oracle logical storage
> > parameters (like initial, next, max extent, pct increase, and so on).
> >
> > For instance, what behaviour do you expect for initial and additional
> > extents. Aim for one big (enough) inital and only a few additional per
> > year or at most per month? Is the max extent limit (e.g. 121) a
> > safeguard and for normal operation it should be near unreachable, or is
> > unlimited the way to go?
> >
> > Regards
> > /Fad
> >
> >
>
> If you are running 8i: use Locally Managed Tablespaces (LMTs) which will
> force you to use equally size extents and for the rest : don't bother
> If you are not running 8i:
> Pct_increase was one of the smartest ideas Oracle ever had, and is the main
> source for tablespace fragmentation.
> Use equally sized extents and pct_increase 0, and maxextents unlimited in
> Oracle 7.3 and 8.0
>
> Hth,
>
> Sybrand Bakker, Oracle DBA

A bit of extension if I might.

If you are building tablespaces in 7.3 or 8.0 make the pct_increase on the tablespaces 1%. But be absolutely sure that you specify pct_increase of 0% in every single table and index create statement as Sybrand suggests. The first parameter will make sure that SMON coalesces freespace. The second will make sure that you don't fragment the tablespace. Under no conditions build a table or index without specifying a storage clause.

Daniel A. Morgan Received on Mon May 14 2001 - 01:29:44 CDT