Error 1659

[Andrew Clark]

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

[DA Morgan]

Andrew Clark wrote:

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
damo...@x.washington.edu
(replace 'x' with 'u' to respond)

[Andrew Clark]

DA Morgan <damo...@x.washington.edu> 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

[Frank van Bortel]

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

[DA Morgan]

Andrew Clark wrote:

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?

[Andrew Clark]

Frank van Bortel <fvanb...@netscape.net> wrote in
news:d07nhv$62f$1...@news1.zwoll1.ov.home.nl:

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

[Frank van Bortel]

DMT, as I understand it, pct_increase=50...

[DA Morgan]

Create a new tablespace, EXTENT MANAGEMENT LOCAL UNIFORM SIZE,
and PCT_INCREASE=0 (it should ALWAYS be 0). Then calculate an
appropriate PCTFREE and PCTUSED for each table, set PCTFREE to
0 for primary key indexes.

Then recreate the objects in the new tablespace and finally drop
the old.

Likely you have tons of wasted space and you will also see an
improvement in performance too: Possibly quite substantial.