Re: exp owner=sys, system compress=y?

"Frank" <frankbo_at_interaccess.nl> wrote in message news:8qusob$4qb$1_at_porthos.nl.uu.net...
> Maybe it's unlimited for the latest releases, but not so for 7.3.4 and
 8.0.6
> (I think).
> On these versions it is the default max, depending on block size (121 for
> 2k,
> 249 for 4k, 505 for 8k, etc).
> I do tweak the initial and next clause for system TS, and some individual
> tables
> occasionally, but leave pctincrease to 50%.
> BTW - this is standard practice in 8iR2 - check the scripts!
>

The maximum theoretical number of extents per segment hasn't been related to block size since version 7.1. In other words, it's theoretically unlimited from that version onwards. What you see in the System tablespace as the maxextents setting is a bad case of Oracle scripts not keeping up with Oracle versions, and a daft decision to *set* maxextents for the various system objects. Alter those maxextent settings, and you won't have a problem.

I was rather hoping you were going to tell me you'd encountered the *physical* limit on the number of extents (as opposed to the theoretical one) -because I never have.

And it remains true that I've never encountered any problems from setting pctincrease to the otherwise generally recommnded figure of zero. Whatever the scripts say. (Remember, these are the same scripts that give you, for example, users SYSTEM and SYS with SYSTEM tablespace as their temp tablespace!!)

Regards
HJR
> What will happen is:
> You'll receive the maxextents message
> Oracle will crash (and produce a wonderful trace file)
> You can try to recover, but it will crash again.
> Basically - you're stuck. Prepare backup media for a restore, or recreate!
> --
> Frank
> Howard J. Rogers <howardjr_at_www.com> schreef in berichtnieuws
> 39d25014_at_news.iprimus.com.au...
> > Can't say I ever have reached maxextents. What is it set to? I know if
 you
> > *don't* set maxextents (for non-system tablespaces), it defaults to
> > something a little over 2 billion -which must be the theoretical, if not
 the
> > practical, limit. Never encountered a practical limitation.
> >
> > Have you?
> >
> > Regards
> > HJR
> > --
>
> --------------------------------------------------------------------------
> > Opinions expressed are my own, and not those of Oracle Corporation
> > Oracle DBA Resources:

 http://www.geocities.com/howardjr2000
>
> --------------------------------------------------------------------------
> >
> >
> >
> > "Frank" <frankbo_at_interaccess.nl> wrote in message
> > news:8qss3i$5af$1_at_porthos.nl.uu.net...
> > > Did you ever run into a "maxextents reached" on your system TS?
> > > One of the reasons Oracle Corp recommends not to set the system TS to
> > > pctincrease 0.
> > > For all others - agreed.
> > > --
> > > Frank
> > > Howard J. Rogers <howardjr_at_www.com> schreef in berichtnieuws
> > > 39d1a924_at_news.iprimus.com.au...
> > > > Oracle's default when it creates *any* tablespace is 50%. Which has
 to
 be
> > > > one of the worst defaults going around. I always change it for my
> > > > databases, and can't say I've noticed any horrible effects.
> > > >
> > > > Regards
> > > > HJR
> > > > --
> > >
> >
>
> --------------------------------------------------------------------------
> > > > Opinions expressed are my own, and not those of Oracle Corporation
> > > > Oracle DBA Resources:

 http://www.geocities.com/howardjr2000
> > >
> >
>
> --------------------------------------------------------------------------
> > > >
> > > >
> > > >
> > > > "Van Messner" <vmessner_at_bestweb.net> wrote in message
> > > > news:BM9A5.1301$np1.175347_at_newshog.newsread.com...
> > > > > I may be wrong but I think if you set the pctincrease on the
 system
> > > > > tablespace to zero, you will shortly have objects which have
 thrown
 an
 awful
> > > > > lot of extents. Oracle's default when it creates a system
 tablespace
 is
 to
> > > > > set pctincrease to 50%.
> > > > >
> > > > > Van
> > > > >
> > > > >
> > > > > "Brian Peasland" <peasland_at_edcmail.cr.usgs.gov> wrote in message
> > > > > news:39D0A034.A206CC51_at_edcmail.cr.usgs.gov...
> > > > > > The only way to remove fragmentation is to export the data,
 delete
 the
> > > > > > database, recreate the database with a modified sql.bsq file so
 as
 to
> > > > > > reduce fragmentation, and then import the data. You can not
 export
 just
> > > > > > the SYS tables.
> > > > > >
> > > > > > But the bigger question is why are you worried about this? Are
 you
 sure
> > > > > > that fragmentation is slowing your database performance?
 Fragmented
> > > > > > tables are not the death knell for high performing databases.
 Many
> > > > > > databases run just fine with fragmentation, especially OLTP
 systems.
> > > > > >
> > > > > > HTH,
> > > > > > Brian
> > > > > >
> > > > > >
> > > > > > NetComrade wrote:
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > My system tablespace is getting fairly fragmented.
> > > > > > > I was wondering if anybody has been able to successfully
 rebuild
 it
> > > > > > > w/o any probs via imp/exp.
> > > > > > >
> > > > > > > Thanx.
> > > > > >
> > > > > > --
> > > > > > ========================================
> > > > > > Brian Peasland
> > > > > > Raytheons Systems at
> > > > > > USGS EROS Data Center
> > > > > > These opinions are my own and do not
> > > > > > necessarily reflect the opinions of my
> > > > > > company!
> > > > > > ========================================
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Fri Sep 29 2000 - 18:24:37 CDT