RE: Odd ora 1652- help

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Mon, 4 Dec 2000 12:57:40 +1100
Message-Id: <10699.123495@fatcity.com>

Hi Chuan

Temporary segments are created when Oracle creates certain objects it creates
temporary segments and later on they become a permanent part of the objects.

In the tablespace where you are creating the object you do not have that amount
of contiguous space left in the tablespace. You may have a lot of free space in separate blocks in your tablespace, but if
it is not contiguous, Oracle cannot use it. Allocating extents requires that

there be a contiguous block of free space.

Check the values of initial and next extent and compare them with the query below.

The query below will give you the maximum bytes that can be allocated to an extent.

select max(bytes) from dba_free_space
where tablespace_name='PINOO'
/

SOLUTION:

Add a datafile to the tablespace
Adjust the storage parameters of the object you are trying to create. Parameters to look at: initial extent, next extent, pct increase. Lower the initial and next if they are larger than the query above.
If you have a lot of free space in that tablespace, but the it is very fragmented, you may want to consider rebuilding the tablespace.
Enable AUTOEXTEND for the datafile

Best Regards

Suhen Pather
Oracle DBA
Strandbags Australia
Land 02 9749 7791
email suhen.pather_at_strandbags.com.au

-----Original Message-----
From: Chuan Zhang [mailto:chuan_at_asiaonline.net] Sent: Monday, December 04, 2000 11:55 AM To: Multiple recipients of list ORACLE-L Subject: Odd ora 1652- help

Hi, All,

I have got the ora-1652 error.
"Sun Dec 3 03:04:42 2000
ORA-1652: unable to extend temp segment by 8192 in tablespace PIN00
Sun Dec 3 03:17:28 2000
ORA-1652: unable to extend temp segment by 1024 in tablespace PIN00" This error happened when we refreshed our DB nightly by creating the tables copied from another DB. I have checked the tablespace "PIN00", there are almost 3GB free space left. The table being created is only 1GB big. I checked temporary tablespace as well, there is 2Gb free space. By the way, the PIN00 tablespace is locally management tablespace.

On both table and tablespace level, specify the maxextents unlimited.

I have no idea what's wrong with this error. Can anybody have this experience before? It is on Solaris 2.7 and Oracle 8.1.5.

Your help is much appreciated.

Thanks in advance,

Chuan

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Chuan Zhang
INET: chuan_at_asiaonline.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Sun Dec 03 2000 - 19:57:40 CST