Re: Oracle Myths

Jim Kennedy wrote:

> 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
> >

Thanks.

I am enjoying a good thread without a flame war. Without doubt many will benefit. Hopefully my students among them.

Daniel Morgan Received on Mon May 20 2002 - 10:31:16 CDT