asiminator
asked on
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Hi.
I have Oracle 9.2.0.1 running on Compaq Tru64 UNIX V5.1
I have run into a problem with the temp tablespace.
I first noticed it when some of my webpages that are served off the database stopped working giving the following error message:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
I opened up the folder where the file lives and it appeared as though the drive was full. The TEMP01.dbf file was around 11GB!!. I made some room by removing some other files but it still didnt work. I then went into enterprise manager and the temp tablespace is showing up as 0 MB.
This is an online system so I cant test a solution... I need a solution that works and please dont hesitate to ask any questions.
Asim
P.S. I suspect the TEMP file blew out because of a new procedure that was being tested. I just need to get it back up and running.
I have Oracle 9.2.0.1 running on Compaq Tru64 UNIX V5.1
I have run into a problem with the temp tablespace.
I first noticed it when some of my webpages that are served off the database stopped working giving the following error message:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
I opened up the folder where the file lives and it appeared as though the drive was full. The TEMP01.dbf file was around 11GB!!. I made some room by removing some other files but it still didnt work. I then went into enterprise manager and the temp tablespace is showing up as 0 MB.
This is an online system so I cant test a solution... I need a solution that works and please dont hesitate to ask any questions.
Asim
P.S. I suspect the TEMP file blew out because of a new procedure that was being tested. I just need to get it back up and running.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just log in enterprise manager or sqlplus as sysdba
execute the following to add free space to temp tablespace:
SQL> alter tablespace temp add tempfile '<Path to a filename like temp01.dbf>' size 300M;
then you are good to go for now.
execute the following to add free space to temp tablespace:
SQL> alter tablespace temp add tempfile '<Path to a filename like temp01.dbf>' size 300M;
then you are good to go for now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that's exactly what I proposed,
but anyway, your problem is solved.
but anyway, your problem is solved.
what the heck? I thought you are gonna to take back your points,
now that you assigned, at least you can do that right.
I am the only one in this post to give you at least the right track to do things.
and I don't get any points????
now that you assigned, at least you can do that right.
I am the only one in this post to give you at least the right track to do things.
and I don't get any points????
ASKER
I did not use your comment at all. Hence, no points. It seemed like a rather simplistic approach to be taking, so it was basically ignored. The answer was actually provided by Sergio Leunissen from Oracle so the points should theoretically go to him. ramaswamymorla provided something close... thats why he got the accepted answer and the other dude put a lot of effort into his response.
ASKER
Thanks for that... but my main problem at the moment is fixing my temp tablespace problem. I will use this once I get the tablespace fixed.
The file being 11GB and enterprise manager saying that it is 0MB is a bigger concern.
Thanks
Asim