Re: Error 1659

From: Andrew Clark <nospam_at_nospam.com>
Date: Thu, 03 Mar 2005 20:32:48 GMT
Message-ID: <1109881969.68e44d7f60ff1306ff7b2effcc4de7fb@teranews>

Frank van Bortel <fvanbortel_at_netscape.net> wrote in news:d07nhv$62f$1_at_news1.zwoll1.ov.home.nl:

> Andrew Clark wrote:

>> DA Morgan <damorgan_at_x.washington.edu> wrote in
>> news:1109866942.93326_at_yasure: 
>> 
>> 
>>>Your original hunch seems valid on its face.
>>>
>>>Try the following query:
>>>
>>>SELECT df.tablespace_name, SUM(df.bytes) TOTAL_SPACE,
>>>SUM(fs.bytes) FREE_SPACE, 
>>>ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) PCT_FREE
>>>FROM dba_free_space fs, dba_data_files df
>>>WHERE df.tablespace_name = fs.tablespace_name (+)
>>>GROUP BY df.tablespace_name
>>>ORDER BY df.tablespace_name;
>>>
>>>These and other queries are available at http://www.psoug.org
>>>click on Morgan's Library
>> 
>> 
>> Thanks, that looks like some helpful stuff. On the tablespaces that I
>> got an error 1659 on, here is the output of your second script:
>> 
>> TABLESPACE_NAME TOTAL_SPACE FREE_SPACE   PCT_FREE
>> --------------- ----------- ---------- ----------
>> ARDAT             209715200   16646144       7.94
>> SDDAT             104857600   27459584      26.19
>> SADAT              83886080   25034752      29.84
>> SFIDX             131072000                     0
>> TIMEIDX            20971520    5177344      24.69
>> 
>> So, obviously, SFIDX needs more space allocated to it. But why are
>> the others failing when it looks like there is ample space?
>> 
>> Andrew

>
> The largest block free is 27 MB - not that much.
> TimeIDX has just 5MB.
> Hopefully, these are Locally Managed Tablespaces, if
> not (you had some basic scripts....) check if there's
> a pct_increase.
> A pct_increase of even 1% will cause the 200th extent to
> be over 7 times as large as the first one; 10% will
> increase that figure to almost 190 Million!
>
> If this is a monitored system on OS level, I would
> change the tablespaces to autoextending, with a defined
> maximum:
> alter database datafile '.....' autoextend on next 1M
> maxsize 200M;
>
> If you do database monitoring the old-fashioned way
> (checking free space, adding space manually), just
> resize the datafile:
> alter database datafile '.....' resize 200M;
> Resize usually is to a larger size...

Yes, there were extents. And they were all 50! Well, I got rid of them and I am going to try an autoextend with the maxsize being 2x as big as the size. Maybe that will do the trick.

Andrew Received on Thu Mar 03 2005 - 14:32:48 CST