Re: Oracle Myths

The system tablespace has some special characteristics. For example, there are a bunch of segments that start at explicit hard coded block locations. Why do I believe that to be true? If you look in sql.bsq you will see some unusual create table statements. They have some undocumented parameters. (which I suggest NO ONE use) Also when we had an Oracle consultant come out to use the DUL (Data Unloader Tool - and I was involved in the aftermath of this database reconstruction, not one I knew about until someone from another department asked me to recover their unbacked up, nonarchivelog mode database) on a database of ours. It can read the actual data files with out the Oracle binaries right from the raw files. It does not even have to run on the same OS that the files were created on. (eg Oracle NT data files can be DUL extracted on a Sun box without Oracle present) It needs some configuration files to tell it which file is the system tablespace and the other database files, but it reads the schema etc right out of the system datafile. So I suspect that the system tablespace has not been changed due to this and other reasons.

I could be wrong.

Jim
"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CE52C02.39AFD7B_at_exesolutions.com...
> Ed Stevens wrote:
>
> > On 17 May 2002 03:23:23 -0700, p_byrne76_at_hotmail.com (Pascal Byrne)
wrote:
> >
> > >"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
message news:<3ce21b71$0$8510$ed9e5944_at_reading.news.pipex.net>...
> > >> PCTIncrease should be as small as possible but non-zero to minimize
> > >> tablespace fragmentation.1% is a good value (from my OCP course notes
though
> > >> not necessarily given by the tutor!)
> > >
> > >I've seen this quoted in a recent paper by Michael R. Ault (author of
> > >"Oracle8i Administration and Management"). The reason given was that
> > >SMON will not coalesce free space if PCTINCREASE is zero. Is this
> > >definitly a myth?
> > >
> > >-Pascal
> >
> > I can't say for sure, but my bet is that this falls into the catagory of
"not a
> > myth, but so what?" It's my understanding that with proper tablespace
> > definition and management, tablespace fragmentation is itself a
non-issue.
> > Whether my understanding of that "fact" is correct or not, the real
point I'm
> > tryng to make is that in some cases, the basic "statement of truth" ( in
this
> > case it is "SMON will not coalesce free space if PCTINCREASE is zero.")
may
> > still be true, but other features/functions make it a non-issue. Kind
of like
> > the old story of the housewife who always cut an inch off the end of a
ham
> > before putting it in the baking pan.
> > --
> > Ed Stevens
> > (Opinions expressed do not necessarily represent those of my employer.)
>
> My best guess would be that it was valid back before 8i and before LMT.
>
> Then I always created my tablespaces with PCTINCREASE 1 and my tables with
PCTINCREASE 0.
>
> But why oh why oh why does Oracle, itself, continue to create the system
tablespace with the defaults it does? Is it due to optimization
> or just a reluctance to change it?
>
> Daniel Morgan
>
Received on Fri May 17 2002 - 22:45:22 CDT