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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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) 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
gatorvipCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.