Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Odd ora 1652- help

Re: Odd ora 1652- help

From: Chuan Zhang <chuan_at_asiaonline.net>
Date: Mon, 4 Dec 2000 14:52:57 +1100
Message-Id: <10699.123500@fatcity.com>


Dear Suhen,

  Thanks for your help.

  But my problem is that the PIN00 tablespace is locally management tablespace with uniform extent size 64k. There should not be any fragmentation. I have run your script, the max(bytes) is 379584512.

I still got ora 1652. I totally lost.

Thanks

Chuan

> 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:
>
> 1. Add a datafile to the tablespace
> 2. 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.
> 3. 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.
> 4. 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-----
> Sent: Monday, December 04, 2000 11:55 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Suhen Pather
> INET: Suhen.Pather_at_strandbags.com.au
>
> 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 - 21:52:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US