ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

I think the title explains it all...

Running Oracle 10 on Linux.

tried to autoextend but maybe I got the wrong file?  It's the only one called tempxx.dbf yet it won't do it.

SQL> alter database tempfile '/entixd/oracle/u02/oradata/jet/temp01.dbf' autoextend on;

ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/entixd/oracle/u02/oradata/jet/temp01.dbf"

SQL> select username, default_tablespace, temporary_tablespace from dba_users where username = 'AMT';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
AMT                            USERS
TEMP

Not sure where to go with this.  Been looking around, but some other environments have different variables that may not apply to me.
LVL 37
Seth SimmonsSr. Systems AdministratorAsked:
Who is Participating?
 
gatorvipConnect With a Mentor Commented:
so if you combine the first two answers (http://#35317482 and http://#35317486) you could do

alter database tempfile 1 autoextend on maxsize unlimited;
(or "maxsize 25G" to change the size to 25G) and see if you're still getting an error

resize 5G will probably fail, since it will attempt to resize it "to" 5G (not "by" 5G)
0
 
slightwv (䄆 Netminder) Commented:
Just for grins see if it already is:
select file_id,file_name, autoextensible from dba_temp_files;

The try with file id:
alter database tempfile 1 autoextend on;

0
 
gatorvipCommented:
Run

select *
from dba_temp_files

to ensure you have the right file. Then you probably want to run "autoextend on" with "maxsize unlimited" (or some other size higher than what you have now)

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Seth SimmonsSr. Systems AdministratorAuthor Commented:
I guess it's already on?

SQL> select file_id, file_name, autoextensible from dba_temp_files;

   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
AUT
---
         1
/u02/oradata/jet/temp01.dbf
YES
0
 
slightwv (䄆 Netminder) Commented:
>>I guess it's already on?

Yes.  It is likely already at max size.  Up the max size.
0
 
gatorvipCommented:
'/entixd/oracle/u02/oradata/jet/temp01.dbf'
'/u02/oradata/jet/temp01.dbf'

that's not (necessarily) the same path
0
 
slightwv (䄆 Netminder) Commented:
>>that's not (necessarily) the same path

That's why I always use the file number.  You don't have to worry about path names.
0
 
Seth SimmonsSr. Systems AdministratorAuthor Commented:
the /u0x folders are syminks to /entixd/u0x

technically it's the same location
0
 
slightwv (䄆 Netminder) Commented:
>>technically it's the same location

Not as far as Oracle is concerned.  The PATH needs to match what is in the database.
0
 
Seth SimmonsSr. Systems AdministratorAuthor Commented:
The file size of temp01.dbf is 20gb.  I'm guessing this will increase it by another 5gb?

alter database tempfile '/u02/oradata/jet/temp01.dbf' resize 5G;
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>The PATH needs to match what is in the database.

Need to correct: Control file not database per say.

>> I'm guessing this will increase it by another 5gb?

You have a query that fills up 20G of TEMP space?  Before you just extend the datafile, check the query.

>>alter database tempfile '/u02/oradata/jet/temp01.dbf' resize 5G;

That SHRINKS it to 5G.  You can either set a new MAX size for autoextend or RESIZE to 25g.
0
 
Seth SimmonsSr. Systems AdministratorAuthor Commented:
SQL> alter database tempfile 1 autoextend on maxsize 25G;

Database altered.

That worked.
Ran the same report with no errors.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.