Home » RDBMS Server » Server Administration » TEMP tablespace full in AIX oracle9i
TEMP tablespace full in AIX oracle9i [message #256187] Fri, 03 August 2007 06:23 Go to next message
reyazan
Messages: 53
Registered: February 2005
Member
i am using oracle 9i on aix5.2. i have a 17 gb temp tablespace with single datafile.
now my temp tablespace almost is full now .
how i clear temp tablespace.

[mod-edit] removed illiterate spelling attempts and the word urgent.

[Updated on: Fri, 03 August 2007 08:21] by Moderator

Report message to a moderator

Re: TEMP tablespace full in AIX oracle9i urgent [message #256190 is a reply to message #256187] Fri, 03 August 2007 06:33 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi reyazan,

I assume you use a temporary tablespace as opposed to a regular tablespace for your temp tablespace.

It's not nessesarily an indication of a problem having a nearly full temp tablespace as Oracle will reuse temp space when needed.
The temporary tablespace is used for sorting/temp tables and the like. As I remember it's the smon proces that from time,time will clear space in temp.
If you run into problem you must have program(s) that together need s this much space. Could be a very large sort or several smaller sorts.
You could use the following sql to find out who (if any) is using the temp space.
If you don't get a result from the select then nobody is using the temp space and it will be reused when needed.
/* shows you what sql_statements and user is doing something on the temporary segments. */
select  a.sql_text,
        b.username,
        b.tablespace,
        b.extents,
        b.blocks from v$sqlarea a, v$sort_usage b
where a.address = b.sqladdr ;
SQL_TEXT
--------------------------------------------------------------------------------
USERNAME                       TABLESPACE                         EXTENTS
------------------------------ ------------------------------- ----------
    BLOCKS
----------
insert into bbb values ('anders')
CRB                            TEMP                                     1
       128


best regards
Carl Bruhn
Re: TEMP tablespace full in AIX oracle9i urgent [message #256214 is a reply to message #256187] Fri, 03 August 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In short: it is normal that the temporary tablespace SEEMS full.

Regards
Michel

[Updated on: Fri, 03 August 2007 07:44]

Report message to a moderator

Re: TEMP tablespace full in AIX oracle9i [message #256232 is a reply to message #256187] Fri, 03 August 2007 08:29 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And enough already with your use of the word urgent. Almost every single post of your is urgent. Go hire somebody then in your company to attend to these problems. You get what you paid for.
Re: TEMP tablespace full in AIX oracle9i [message #256349 is a reply to message #256187] Sat, 04 August 2007 00:21 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

Don't worry about temp tablespace...oracle server take care of it.


Regards
Taj

[Updated on: Sat, 04 August 2007 00:21]

Report message to a moderator

Previous Topic: Database upgrade using dbua
Next Topic: how can exceeded Tablespace quota in oracle10g
Goto Forum:
  


Current Time: Sat Dec 10 09:23:52 CST 2016

Total time taken to generate the page: 0.22014 seconds