Home » RDBMS Server » Server Administration » How to reduce size of TEMP01.DBF?
How to reduce size of TEMP01.DBF? [message #503140] Wed, 13 April 2011 03:23 Go to next message
ibanpaf
Messages: 11
Registered: September 2008
Location: peshawar pakistan
Junior Member
hi i am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size can you help me.

Report message to a moderator

Re: temp01.dbf' [message #503147 is a reply to message #503140] Wed, 13 April 2011 04:09 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
Ok, then you should follow the below process.

1. Create new temporary tablespace ( like temp01 )

2. Assign new temp tablespace to all users [ who's using old temp tablespace ]

3. Then you can drop old temp tablespace.

- Babu

Report message to a moderator

Re: temp01.dbf' [message #503177 is a reply to message #503147] Wed, 13 April 2011 06:43 Go to previous messageGo to next message
ibanpaf
Messages: 11
Registered: September 2008
Location: peshawar pakistan
Junior Member
i have the following problem how to solve it.
ora-01652 unable to extend temp

Report message to a moderator

Re: temp01.dbf' [message #503181 is a reply to message #503177] Wed, 13 April 2011 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase the size of the file or add a new file to the tablespace.

Also you MUST post the complete error message and statement you executed when the error came.

Regards
Michel

[Updated on: Wed, 13 April 2011 06:57]

Report message to a moderator

Re: temp01.dbf' [message #503192 is a reply to message #503181] Wed, 13 April 2011 07:51 Go to previous messageGo to next message
ibanpaf
Messages: 11
Registered: September 2008
Location: peshawar pakistan
Junior Member
i am using toad for oracle and where ever i access the temp01.dbf this problem occures the error is
ora-01652 unable to extend temp segment by 3596 in tablespace system.

Report message to a moderator

Re: temp01.dbf' [message #503194 is a reply to message #503192] Wed, 13 April 2011 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Through away TOAD
2/ Use SQL*Plus

Regards
Michel

Report message to a moderator

Re: temp01.dbf' [message #503196 is a reply to message #503192] Wed, 13 April 2011 07:57 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You see, the complete error message changes the answer? The problem is with TEMP space in SYSTEM tablespace, not TEMP tablespace.

Are you running a giant query void of proper joins that is giving a Cartesian product?

How about answering the question of what query you were running when this occurred?

Report message to a moderator

Re: temp01.dbf' [message #503197 is a reply to message #503192] Wed, 13 April 2011 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ibanpaf wrote on Wed, 13 April 2011 08:51
i am using toad for oracle and where ever i access the temp01.dbf


What does this mean, whenever you access temp1.dbf? This makes no sense to me.

Report message to a moderator

Re: temp01.dbf' [message #503202 is a reply to message #503197] Wed, 13 April 2011 08:12 Go to previous messageGo to next message
ibanpaf
Messages: 11
Registered: September 2008
Location: peshawar pakistan
Junior Member
okay sir through away TOAD now how to handle this in the sql-plus?

ora-01652 unable to extend temp segment by 3596 in tablespace system

Report message to a moderator

Re: temp01.dbf' [message #503204 is a reply to message #503202] Wed, 13 April 2011 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to handle what?
You gave an error but you didn't give what you did to get the error.

Regards
Michel

[Updated on: Wed, 13 April 2011 08:22]

Report message to a moderator

Re: temp01.dbf' [message #503330 is a reply to message #503204] Thu, 14 April 2011 10:25 Go to previous messageGo to next message
shaneone1
Messages: 8
Registered: August 2009
Junior Member
In Sqlplus try this


Connect / as sysdba

SQL> alter tablespace temp add tempfile
'e:\Oracle\Ora10g\DbfSID\temp02.dbf' size 2000m reuse autoextend on next 100m;

SQL> alter database tempfile 'e:\ORACLE\ORA10g\DBFSID\TEMP01.DBF' drop including datafiles;

SQL> alter tablespace temp add tempfile
'e:\Oracle\Ora10g\DbfSID\temp01.dbf' size 2000m reuse autoextend on next 100m;

SQL> alter database tempfile 'e:\ORACLE\ORA10g\DBFSID\TEMP02.DBF' drop including datafiles;

Look at your alert_log afterwards and determine if the temporary tablespace is having problems extending.

Just bounce database and try again.
Hope that helps.

Report message to a moderator

Re: temp01.dbf' [message #503333 is a reply to message #503330] Thu, 14 April 2011 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try what you suggest?

Regards
Michel

Report message to a moderator

Re: temp01.dbf' [message #504340 is a reply to message #503147] Sat, 23 April 2011 18:14 Go to previous messageGo to next message
cparisienreveregroupcom
Messages: 3
Registered: April 2011
Junior Member
this is the correct solution for your Server version. Create a new temp tablespace,at the size you want, make that the default, then drop the older one.

[Updated on: Sat, 21 January 2012 01:07] by Moderator

Report message to a moderator

Re: temp01.dbf' [message #504353 is a reply to message #504340] Sun, 24 April 2011 01:17 Go to previous message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this is not the correct solution for the posted problem.

Regards
Michel

Report message to a moderator

Previous Topic: How validate OLAPSYS schema objects after transferring Database from windows 2003 32 bit to 2008 64
Next Topic: Domain change -- easy?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 09 04:03:05 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.