Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!router1.news.adelphia.net!router2.news.adelphia.net!nntpserver.com.MISMATCH!newsfeed-east.nntpserver.com!nntpserver.com!manticore.nntpserver.com.POSTED!teranews!not-for-mail Newsgroups: comp.databases.oracle.misc Subject: Re: Error 1659 From: Andrew Clark References: <1109866233.4cfd394fee9c00bc8964f2b0bba7f798@teranews> <1109866942.93326@yasure> <1109870438.4141bbf198aed59e043d49550687b7a8@teranews> User-Agent: Xnews/5.04.25 Lines: 66 NNTP-Posting-Date: Thu, 03 Mar 2005 15:32:48 EST Message-ID: <1109881969.68e44d7f60ff1306ff7b2effcc4de7fb@teranews> X-Abuse-Report: http://www.usenetabuse.com X-Abuse-Notes: Abuse reports must be submited via the usenetabuse.com portal listed above. X-Abuse-Notes2: Reports sent via any other method will not be processed. X-Abuse-Notes3: Any other abuse reporting headers in this article are fraudulent. X-Orginal-Message-ID: Date: Thu, 03 Mar 2005 20:32:48 GMT Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:116978 Frank van Bortel wrote in news:d07nhv$62f$1@news1.zwoll1.ov.home.nl: > Andrew Clark wrote: >> DA Morgan wrote in >> news:1109866942.93326@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