Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.stanford.edu!sn-xit-03!sn-xit-12!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.misc Subject: Re: Error 1659 Date: Thu, 03 Mar 2005 12:20:16 -0800 Organization: Ye 'Ol Disorganized NNTPCache groupie Message-ID: <1109881027.376789@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: <1109866233.4cfd394fee9c00bc8964f2b0bba7f798@teranews> <1109866942.93326@yasure> <1109870438.4141bbf198aed59e043d49550687b7a8@teranews> In-Reply-To: <1109870438.4141bbf198aed59e043d49550687b7a8@teranews> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 52 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:116976 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 Free space does necessarily equate to usable. Any chance the extent size be requested is larger than the free space available? And is that free space fragmented such that no continguous space is as large as what is being requested? My guess is that if you solve the problem with SFIDX you will be fine. But why the separation of tablespaces for data and index? Hopefully not following some mythological nonsense published a decade ago. Also ... is this LMT or DMT? -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)