Re: table_space problem

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'

First of all: <soap box> you should not place user data in the SYSTEM tablespace. it is only for the data dictionary tables. do not ask oracle to compete with users for space. </soap box>

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

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.

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.

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?

--
Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450
dlm_at_dlogics.com                                 Motto: Just show me the code