Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

"Joe Salmeri" <JoeSalmeri_at_comcast.net> wrote in message news:rdNS8.432120$%y.31164463_at_bin4.nnrp.aus1.giganews.com...
> It was my understanding that it would reduce the fragmentation in each of
> the datafiles but that doesn't address the issue where you have 3 data
files
> in a tablespace each with 200mb free and I need to allocate a 600mb
extent.
>
> Are you saying that locally managed tablespaces will allow Oracle to
> allocate part of the extent from one data file and the rest from a
different
> datafile?

No, extents still can't cross the physical file boundary. But you are mistaken in believing you need a 600Mb extent. What's wrong with 3 200Mb extents? Contiguity of extents is a complete and utter mirage anyway, and there is precisely zero performance improvement to be gained or noticed between a single-extent 600Mb segment, and a 3-200Mb extent segment, or even a 30-20Mb extent segment.

In dictionary managed tablespace, a 3000-0.2Mb segment would pose problems, true. Much, much less so in locally managed tablespace. I myself would not be comfortable with *quite* that many extents, but 100-60Mb extents would not be a issue.

It's a myth of the biggest proportions that segments should be comprised of one extent. A single Oracle block is not even contiguous on disk, so there's no way a single extent can be, either (raw partitions excepted). But that is not a performance problem.

If you can accept that, then it is follows that it is daft to want enormous datafiles to accomodate large extents. You are depriving yourself of the management flexibilty (in backup and recovery scenarios, principally) that smaller datafiles bring. And you are also missing out on a definite performance advantage that ensues from multi-file tablespaces -namely, Oracle's propensity to round-robin the various extents of a segment so that they are stored on separate files (and hence, potentially, on separate disks).

HJR
>If so I will investigate using locally managed tablespaces,
> currently we are using dictionary based, we just recently upgraded to
Oracle
> 8i from 8.0.5. From what I have read so far it stil appears that an
extent
> must be contiguious within a single datafile.
>
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:gfvmhusfooj1enmi8r96k7k3drcfpfd5im_at_4ax.com...
> > On Thu, 27 Jun 2002 17:28:04 GMT, "Joe Salmeri"
> > <JoeSalmeri_at_comcast.net> wrote:
> >
> > >I want option 1, files that are larger than 2 gb. I have tables that
are
> > >much larger than 2 gb and I don't want tons of extents. I don't care
> about
> > >the things that don't understand large file support because I KNOW that
> > >Oracle does. I don't understand why this is such a difficult task to
> > >accomplish on Linux, I have done this on numerous Windows NT and
Windows
> > >2000 servers that I have built over the years and it worked flawlessly.
> > >
> > >When you create a bunch of datafiles for a tablespace and that
tablespace
> > >contains many large tables it is very easy to get into the situation
> where
> > >you are not be able to create objects because the free space is
> fragmented
> > >between all of the datafiles. This is not an easy thing to clean up
and
> > >requires many hours of effort that could have EASILY been avoided.
> > >
> > >Extents (initial allocations or next allocations) MUST be contiguous.
> >
> > Hear, hear.
> > That is why Oracle invented Locally Managed Tablespaces.
> > This reduces fragmentation to exactly 0, that is when you allow for an
> > extra 64k per file, holding the allocation bitmap.
> > Objection overruled.
> >
> > Regards
> >
> >
> >
> >
> > >cannot find free space in one datafile and combine that with free space
> in a
> > >second datafile to make up a SINGLE extent. If the extent size is not
> > >available in a contiguous block then the allocation fails.
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>
Received on Fri Jun 28 2002 - 05:07:30 CDT