Re: ORA-1652 Error during data migration

From: ddf <oratune_at_msn.com>
Date: Wed, 20 Jun 2012 08:13:28 -0700 (PDT)
Message-ID: <bde5fe5c-2cc9-4d77-9604-c621a377f5c7_at_qz1g2000pbc.googlegroups.com>

On Jun 19, 7:47 pm, John Hurley <johnthehur..._at_gmail.com> 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.s...
>
> All of this is pretty well documented in the Oracle free doco ... so
> reading the manual is recommended.

He doesn't have temp segments in USERS; Oracle declares any segment that is being created or dropped as a temp segment until the operation creating/dropping it completes. If Oracle can't extend the tablespace to create it during object creation or extent addition then this error is thrown. This has been reported on Metalink some time back; the relevant document is 19047.1.

"What I am asking is why do I get these errors when I am using the autoextend feature above?
If Oracle is extending the size of the datafile when required, why am I getting this error? "

First setting autoextend without setting NEXT can cause Oracle to use exceptionally small increments and it may be falling behind on the extensions, throwing the error. A better way to set this would be:

alter tablespace USERS add datafile '/export/u01/oradata/mydb/ users03.dbf' size 1000M autoextend on next 1M;

which would provide 1M extensions to the file size rather than some ridiculously small values such as shown below

SQL> create tablespace smorf datafile 'c:\oradb\oradata\smedley \smorf01.dbf' size 100m autoextend on;

Tablespace created.

SQL> select tablespace_name, increment_by from dba_data_files;

TABLESPACE_NAME                INCREMENT_BY
------------------------------ ------------
SYSTEM                                 1280
SYSAUX                                 1280
UNDOTBS1                                640
USERS                                   160
INDX                                      0
SMORF                                     1

6 rows selected.

SQL> Notice that SMORF, created with a command similar to what you are using that does not set NEXT, has an increment value of 1 block, a pretty small increment. (What can be confusing is that NEXT is specified in bytes in the create/alter command but displayed in blocks in DBA_DATA_FILES.) Since you're incrementing the datafile by 1 block at a time for each autoextend request it can take quite a while to allocate 16,000 blocks, for example.

Since Oracle 9.2 (I believe) if MAXSIZE is not specified it defaults to 32G so you should be fine there.

It's not the autoextend, per se, that's causing your problem it's that you're not setting a large enough increment for each autoextend request.

David Fitzjarrell Received on Wed Jun 20 2012 - 10:13:28 CDT