Glenn,
Use following query to check usage of temp(temporary) tablespace and put
into your log table:
select user,segtype,extents from v$sort_usage
Are you having any table/indexes with degree > 1 (check
dba_tables/dba_indexes)
Some of your job using such tables in parallel because of degree > 1 and
resulting in segtype as hash instead of sort and in this case it uses a lot
of temp tablespace execeeding its total limit. There is also a possiblity
that there may be some cartisian product/joining which also consumes a lot
of temp tablespace and 5G is nothing when it happens...
Once that job is terminated the usage goes to Zero so you cannot track it.
Besides user also don't complaint because of fear of running wrong code. If
nobody complaints then don't bother...
HTH,
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Mon, 25 Mar 2002 13:30:26 -0800
We are periodically getting the;
"ORA-1652: unable to extend temp segment by 128 in tablespace TEMP"
errors.
I could not identify the problem, so I set up a monitor script which would
insert into a log table space usage records every 30 seconds, so I could see
the space usage at the time of the failure.
After we got another ORA-1652, I looked up the time and queried my log
table, which showed hardly any usage in the TEMP tablespace.
My question is;
Why do I keep getting this error when I have plenty of free space in TEMP???
Why is it trying to extend a 128 extent when I have uniform extents (locally
managed temporary tablespace and the extent sizes are 1M)?
Here are my supporting settings;
Temporary tablespace settings:
create temporary tablespace TEMP
tempfile '/RPT/oradata04/prddata/temp01.dbf'
size 5000M
REUSE
extent management LOCAL
UNIFORM
size 1048576;
Query at the time of the failure:
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;
Output:
Tablespace Tablespace Allocated Allocated Allocated
Name Total MB Total MB Used MB Free MB
-------------------- ---------- --------- --------- ---------
TEMP 5,000 568 6 562
Users using temp space query;
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
a.sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2);
Output:
Temporary
Mbytes
Session ID User Name TS Name SQL
Used
---------- ---------- ----------
------------------------------------------------------------ ---------------
152,6214 APPS TEMP select parameter, value from
nls_session_parameters 1.00
32,11293 APPS TEMP select parameter, value from
nls_session_parameters 1.00
(a couple of others totalling 6MB)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
MOHAMMAD RAFIQ
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
