| TEMP tablespace full in AIX oracle9i [message #256187] |
Fri, 03 August 2007 06:23  |
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  |
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
