Re: Error 1659

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Thu, 03 Mar 2005 20:14:22 +0100
Message-ID: <d07nhv$62f$1@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...

-- 
Regards,
Frank van Bortel

Received on Thu Mar 03 2005 - 13:14:22 CST