TIP #2 : TEMP Tablespace can not extend.

TEMP tablespace growth issue is known issue for DBAs.
I know that each of us experienced this issue so many time.
In this post, I `d like to outline this issue and explain options to fix.
Basically, this error reports in Alert log with the following statement:

ORA-01652: unable to extend temp segment by %s in tablespace %s).

A "temp segment" is not necessarily a SORT segment in a temporary tablespace. Actually it can be used in following conditions:

- In SORT statement
- In creating Index
- In enabling constraint
- In creating primary key/unique key constraint.
.
.
.

Conseuqently, it is not true to say TEMP segment is only used for SORT operations.
On the other hand the most probable usage of TEMP segment is sort usage in practical situation.

I suggest to follow below guideline to resolve the issue :

- Find out whether or not PGA size
(pga_aggregate target in >=9i or sort_area_size <9i ) has proper size.
Having bigger PGA will decrease TEMP usage.

- Make sure that TEMP datafile is autoextensible and maxbytes/maxblocks are not very low.

select * from dba_temp_files;

- Find queries which allocate big chunk of temp tablespace and try to tune these queries.
The following is shell script which can be scheduled via crontab.
The result has SQL statements which allocate TEMP tablespace



In database :

CREATE TABLE "AVAIL"."MONITOR_SORTSEG"
( "TABLESPACE_NAME" VARCHAR2(31),
"USED_EXTENTS" NUMBER,
"FREE_EXTENTS" NUMBER,
"ISSUE_TIME" DATE);

In OS : (monitor_temp.sh)

#!/bin/ksh
SCRIPT=1; export SCRIPT
. ~orfnprd/.profile
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<\EOF
set serveroutput on
begin
insert into monitor_sortseg select tablespace_name,USED_EXTENTS,FREE_EXTENTS,sysdate
from v\$sort_segment where upper(tablespace_name)='TEMP' and free_extents<=500;
if(SQL%ROWCOUNT<>0) then
dbms_output.put_line('Start -------- Start Date:'||sysdate);
dbms_output.put_line('TEMP free extent is lower than 50 .... Finding SQL statement');
insert into monitor_sortuse select USER,SESSION_NUM,sortu.TABLESPACE,
sysdate,sql_text,sorts from v\$sort_usage sortu,v\$sqlarea sqla where upper
(tablespace)='TEMP' and sortu.SQLHASH=sqla.HASH_VALUE ;
commit;
dbms_output.put_line('End ----------End Date:'||sysdate);
end if;
end;
/
exit;
EOF

Sample crontab :

0,5,10,15,20,25,30,40,45,50,55 * * * * sample_temp.sh > sample_temp.log 2>&1