OraFAQ Forum: General » Oracle Temp Tablespace is too BIG

Home » Other » General » Oracle Temp Tablespace is too BIG (Oracle 11g - Sun Solaris 64 Bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Temp Tablespace is too BIG [message #591563]

Mon, 29 July 2013 14:30

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

Michel Cadot
Messages: 68757
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

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

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

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

Michel Cadot
Messages: 68757
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

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

cookiemonster
Messages: 13967
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

Michel Cadot
Messages: 68757
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 20 12:35:07 CDT 2025