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

Now I understand that I can alleviate this problem by adding more
datafiles to the tablespace as
I have done in the following example :

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

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?

I am interested in any reasons for this.
Disk capacity is not really a problem on our server but by the same
token I would prefer not to
create an abitrarily large tablespace to avoid the ORA-1652 error.

Thank you in advance for any feedback on this.

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/Or...es/TBS_3.shtml

All of this is pretty well documented in the Oracle free doco ... so
reading the manual is recommended.

On Tue, 19 Jun 2012 17:56:39 -0700, Mick wrote:

Mick, several things:

1) What is the max size your files can auto extend on to? Is it reached?
2) Why do you have temporary segments in users tablespace?
3) Did you find the culprit? You can look into V$ACTIVE_SESSION_HISTORY to
find the session consuming the most temp space at the time.
4) 1000M is less than 1G. It's not really big file.



--
http://mgogala.byethost5.com

On Tuesday, June 19, 2012 9:47:55 PM UTC-4, John Hurley wrote:
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 largestextent size that will be requested. With locally managed auto-allocate I would suggest a 64M minimum.

HTH -- Mark D Powell --

On Jun 19, 7:47*pm, John Hurley <johnthehur... (AT) gmail (DOT) com> wrote:
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

Mark:

# 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.

Thanks Mark ... probably been 10 years or more since I saw a
tablespace actually fill up ... not a believer in taking 1652's on the
chin.

Some of us are kind of overly pro active on space management ...
easier to do for some than others depending on how many database
instances one is supporting and the mix of what kind of database
instances they are.