Re: table_space problem

In article <1993Mar20.195803.1352_at_hermes.dlogics.com>, dlm_at_hermes.dlogics.com writes:
> In article <1993Mar19.211419.26420_at_netnews.whoi.edu>,
> dhiltz_at_whsun1.wh.whoi.edu (David Hiltz) writes:
>> In article <Mar18.173015.71143_at_yuma.ACNS.ColoState.EDU>  ciuffoli_at_lamar.ColoState.EDU (Carlos Ciuffoli) writes:

>>>ERROR: 
>>>ORA-01547: failed to allocate extent of size 956 in tablespace 'TEMPUSERS' 
>>>
>>    ERROR:
>>    ORA-01547: failed to allocate extent of size 3596 in tablespace 'SYSTEM'

Agreed!! All hail the prophet!!

>
> Now then: default storage parameters have the above side-effect when tables
> are extended. oracle out-of-the-box defaults to 50% growth factor. that is,
> each new extent will be 150% of the latest extent size. if you add just one
> row to a table whose last extent is full, you may run out of space in the
> tablespace for the gigundo new extent (that's ORA-1547).

We have a separate tablespace to hold Oracle created temporary structures such as its sort/merge area, point all users except SYS and SYSTEM to this temporary tablespace using ALTER USER xyz TEMPORARY TABLESPACE abc;.

We have default storage in this temporary tablespace (SYS_TS3) set at an initial of 1Meg and next of 1Meg with percent increase of ZERO. Tablespace SYS_TS2 is where our rollback segments reside and SYSTEM is just the Oracle Data Dictionary with users and applications going into other specific tablespaces. SYSTEM is 10-20Meg, SYS_TS2 is 50-100 Meg, and SYS_TS3 is 50-100Meg. We tend to have less than 200 active users per database and we run 5 production databases which contain 4 major applications and a bunch of small to medium applications lumped in the 5th database.

>
> one solution is to preallocate your tables using the strorage(initial x)
> clause, so that it's about the right size from the beginning, and doesn't
> extend during normal operations.

We also do this, its tedious but worth trying.

>
> another solution is to change the default storage(pctincrease x) clause in your
> userdata and temporary tablespaces. perhaps 50% is too much. you might try
> stunts like larger next extents with 0% growth (all extents same size),
> if you are low on total disk space for oracle.

We only ever use 0% growth except on Data Dictionary or Oracle Corp. created tables. A managed database should (usually) never need percentage growth.

>
> another solution is to add more data files to the tablespace in question.
> and you do have separate tablespaces for SYSTEM, user data, indexes, and
> temporary tables, don't you?

If the problem is occurring because of sorting grabbing lots of temporary storage structures then you could try increasing the INIT.ORA parameter SORT_AREA_SIZE but be aware that each user will start using more RAM. I have found this very useful when rebuilding indexes and such. I normally run it at 128K but for index rebuilds I push it up to 10Meg.

-- 

Bruce...        pihlab_at_hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************