Re: TableSpace Storage Parameters?

Hi Jonathan,

There is a big difference between dropping objects and deleting and inserting records in an object.

Dropping objects does not happen all that frequently on the production machine but deleting and inserting records happens all the time and thats what causes fragmentation and requires COALESCE of free contiguous space.

I have used pctincrease = 1 on tablespaces for about a year or so on two 1.5 Gigabytes databases.
We insert and delete 100,000 records every day in several different tables.

It has simplified my job as a DBA. I hardly reorg, defrag or rebuild indexes anymore, where as prior to
it, I used to either increase the datafile size or reorg every quarter.

Thanks

Suresh Bhat  

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in article <918148245.17337.0.nnrp-04.9e984b29_at_news.demon.co.uk>...
> This is NOT a sensible thing to do.
> It would be far better to adopt a sensible storage
> strategy that made is unnecessary for SMON to
> do any coalescing of free space.
>
> i.e.
> all objects in a tablespace to use the default storage
> default storage to be ' initial = next, pctincrease = 0'.
>
> Although there are some (version dependant) details that
> have to be addressed with Parallel CTAS and multiple free
> list groups this policy ensures that any dropped object
> leaves behind holes which are exactly the right size for
> the next object you want to create, so space management
> ceases to be a problem.
>
> See also my document on smon
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
> Oracleguru, Suresh Bhat wrote in message
> <01be5068$c1638be0$a504fa80_at_mndnet>...
> >Hi!
> >
> >I would also set the pctincrease to 1 for all tablspaces ( not the
tables
> >) except Oracle created tablespaces such as SYSTEM, RBS, TOOLS etc.
> >
> >If the tablespace is already created use:
> >
> >alter tablespace tablespace_name default storage ( pctincrease 1 );
> >
>
>
>
>
Received on Fri Feb 05 1999 - 15:57:19 CST