Home » Other » General » Oracle Temp Tablespace is too BIG (Oracle 11g - Sun Solaris 64 Bit)
Oracle Temp Tablespace is too BIG [message #591563] Mon, 29 July 2013 14:30 Go to next message
akinur
Messages: 12
Registered: July 2013
Location: Canada
Junior Member
Oracle TEMP tablespace is too big 36 GB
I deleted, created and shrinked the table but again it come back, any idea?


~~ 1)
SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%'
ALTER database tempfile '/oracle/product/oradata/riotest/temp01.dbf' resize 1024M;
ALTER TABLESPACE temp SHRINK SPACE KEEP 1024M;

~~ 2)
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/oracle/product/oradata/riotest/temp02.dbf' SIZE 50M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

~~3)

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/data/u02/app/oracle/oradata/riotest/temp01.dbf' SIZE 1024M REUSE
AUTOEXTEND ON NEXT 200M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591570 is a reply to message #591563] Mon, 29 July 2013 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Oracle TEMP tablespace is too big 36 GB
I deleted, created and shrinked the table but again it come back, any idea?


No table reside in TEMP tablespace.

What do 1, 2, 3 is for?

If "it come back" then this means you need it. It does not come back for the pleasure, only because you ask it to do so.

Regards
Michel

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591572 is a reply to message #591570] Mon, 29 July 2013 14:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Oracle TEMP tablespace is too big 36 GB
Then why did you make size TEMP tablespace 36GB?
Only you control the size of the tablespace.
Oracle will use all the space that is available.
If you want TEMP to be smaller, then you need to make it smaller.

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591580 is a reply to message #591572] Mon, 29 July 2013 15:16 Go to previous messageGo to next message
akinur
Messages: 12
Registered: July 2013
Location: Canada
Junior Member
If I off the "Auto Extend" is there will be a problem to the running database?

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591582 is a reply to message #591580] Mon, 29 July 2013 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If I off the "Auto Extend" is there will be a problem to the running database?
If you do not turn off AUTO EXTEND, then TEMP tablespace will continue to grow.

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591590 is a reply to message #591580] Mon, 29 July 2013 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
akinur wrote on Mon, 29 July 2013 22:16
If I off the "Auto Extend" is there will be a problem to the running database?


Michel Cadot wrote on Mon, 29 July 2013 21:46
...
If "it come back" then this means you need it. It does not come back for the pleasure, only because you ask it to do so.


So the conclusion is... if you have not enough space you will have an error.

Regards
Michel

[Updated on: Mon, 29 July 2013 23:59]

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591638 is a reply to message #591582] Tue, 30 July 2013 07:38 Go to previous messageGo to next message
akinur
Messages: 12
Registered: July 2013
Location: Canada
Junior Member
> If you do not turn off AUTO EXTEND, then TEMP tablespace will continue to grow.
- I know it will continue to grow, but my question is diffrent "If I off the "Auto Extend" is there will be a problem for the running database?"

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591643 is a reply to message #591638] Tue, 30 July 2013 07:53 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the DB needs to use more temp space than is available then you will get errors.

Report message to a moderator

Re: Oracle Temp Tablespace is too BIG [message #591651 is a reply to message #591638] Tue, 30 July 2013 08:25 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 30 July 2013 06:57
akinur wrote on Mon, 29 July 2013 22:16
If I off the "Auto Extend" is there will be a problem to the running database?


Michel Cadot wrote on Mon, 29 July 2013 21:46
...
If "it come back" then this means you need it. It does not come back for the pleasure, only because you ask it to do so.


So the conclusion is... if you have not enough space you will have an error.

Regards
Michel

[Updated on: Tue, 30 July 2013 08:25]

Report message to a moderator

Previous Topic: Clean Database
Next Topic: Adding new application at existing oracle 11gr2 database.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 27 05:41:34 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.