Re: ORA-1652 Error during data migration

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 20 Jun 2012 07:40:15 -0700 (PDT)
Message-ID: <1d3555ec-7136-488c-ad08-4477539abb99_at_googlegroups.com>



On Tuesday, June 19, 2012 9:47:55 PM UTC-4, John Hurley wrote:
> Mick:
>
> # I have a team of external consultants who are migrating data accross
> to our database, (Oracle Database 11g Release 11.2.0.2.0)
>
> ... Occasionally their migration crashes with :ORA-1652: unable to
> extend temp segment by 8192 in tablespace                 USERS
>
> You should not have temp segments in the USERS tablespace. The TEMP
> tablespace is a special kind of structure that should be used for
> sorting/ordering/hash joins/etc that require extra space.
>
> Something seems wrong with the setup of your database TEMPORARY
> tablespace ...
>
> Try looking at this perhaps? http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
>
> All of this is pretty well documented in the Oracle free doco ... so
> reading the manual is recommended.

John, remember that Oracle also uses temp segments when it creates and extends objects. The segment extent is a temp segment until such time as the rdbms dictionary is updated to make the extent permanent. So the error can have nothing to do with true temporary segments.

Excessive initial extent parameter values in create table/index statements can lead to this error. Running traditional imports made with the default compress=y setting can eat a lot of unneeded space resulting in the need to expand the target tablespaces.

Also make sure the autoextend file size is at least as large as the largest extent size that will be requested. With locally managed auto-allocate I would suggest a 64M minimum.

HTH -- Mark D Powell -- Received on Wed Jun 20 2012 - 09:40:15 CDT

Original text of this message