Re: Error 1659

Andrew Clark wrote:

> Hello,
>
> I am importing a large database to Oracle 9i. I have a simple script to
> create tablespaces and users and everything I need. When I look at the log
> at the end of the process, I find that a bunch of stuff did not import, and
> error 1659 (unable to allocate MINEXTENTS beyond X in tablespace Y) was
> returned. I thought this might be a space issue and I will try and increase
> the allocated space for each tablespace, but first I thought I would post
> here since I am relatively new to Oracle. Should I go through with my plan,
> or is there something I am missing altogether?
>
> Thanks,
> Andrew

Your original hunch seems valid on its face.

Try the following query:

clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a40
col tablespace_kb heading 'TABLESPACE|TOTAL KB' col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd WHERE dd.tablespace_name = fs.tablespace_name GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024 ORDER BY dd.tablespace_name, dd.file_name;

and/or

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

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)