Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!samsung!emory!mephisto!prism!scott
From: scott@prism.gatech.EDU (Scott Holt)
Newsgroups: comp.databases
Subject: Re: Oracle Application Foundation on Suns
Keywords: Oracle, Version-6, Sun, NFS, Application Foundation
Message-ID: <13154@hydra.gatech.EDU>
Date: 29 Aug 90 22:40:28 GMT
References: <5559@thebes.Thalatta.COM> <19876@crg5.UUCP>
Distribution: na
Organization: Georgia Institute of Technology
Lines: 50

In article <19876@crg5.UUCP> larrys@crg5.UUCP (Larry Scheurich) writes:
>In article <5559@thebes.Thalatta.COM> depner@Thalatta.COM (August Depner) writes:
>>
>>The barebones DBMS runs fine, but when we try to install Application Foundation,
>>the install script bombs after a while with an ORACLE error of 01547 - failed to
>>allocate extent of size 'xxx' in tablespace 'SYSTEM'.  We've pared down what we're installing and
>>increased the size of tablespace 'SYSTEM'  several times with no luck. At present, we are allocating
>>over 100 Mb of diskspace to ORACLE, and cannot complete the install.
>
>If your database only contains a system tablespace, that may be your problem.
>One of the very first steps of the install is to do a very large import of
>a user known as APPLSYS.  This user contains all of the database information
>about Application Foundation.  If you have only one tablespace, you're 
>probably getting "nailed" by rollback segments that are trying to grow
>in the same tablespace that the data's going into.  In order to verify this,
>you may want to run sqldba/monitor while the import's runninng to see
>if that's the problem.  I suspect that this is the problem.  If it is,
>you have three possible solutions:
>

It could also be a problem related to fragmentation in the system tablespace.
I haven't looked, but I would assume the import/export file was created with
compressed extents. If this is the case, then as each of APPLSYS's tables
is created, import attempts to allocate an initial extent large enough to hold
the whole table. If it cannot find a single extent large enough, you get
the above error. There could be plenty of total space in the system table
space, but no contiguous block large enough.

One thing that could cause the fragmentation is temporary tables. As tables
are created and destroyed, the tablespace containing them becomes more
fragmented. Since temporary tables are created and destroyed frequently,
there will be a great deal of fragmentation in whatever tablespace they are
created in. By default, the system tablespace is used as the temporary
tablespace.

The database view  freespace  (in release 6) should show you if there is
fragmentation. Freespace lists all free extents in the database, their sizes
and the files they reside in. A large number of small extents may be a
indication of fragmentation.

The tablespace to use for temp tables is a user parameter which you can
control with ALTER USER. If fragmentation is a problem, create a separate
tablespace and make it the tempspace for user APPLSYS. 

- Scott
-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt, Systems Analyst		Internet: scott@prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services