ORA-1652

ORA-1652: unable to extend temp segment by 512 in tablespace TEMP01

And tablespace TEMP01 is a locally managed one ...

Its coming very often;

I would like to figure out whats happeening , would simebody suggets any Vdollors , to look at ???

ANyway I cant coalesce , since its a locally managed tablespace...

Suggestions plz
Madhu

Datafile for ur TEMP01 tablespace has fixed size and hasn't AUTOEXTEND option.
All disk space in datafile done and oracle can't allocate extent # 512 in this file.
U should:

1) set autoextent option for datafile
or
2) add new datafile to TEMP01 tablespace.

Size of the tablespace is already 2Gb and i believe its sufficient .. And one more thing I absorved is 99% of my sorts are taking place in memory and not in disk.

So what i am thinking is when ever oracle needs the disk space for sorting and the sort is so huge ( not necessarily bigger than the tablespace ), oracle is throwing ORA-1652 error.

Usually i check v$sort_usage and v$sort_segment tables , but couldn't trace anything from it. coz i always see used_blocks are ZERO.

Is there anyway to check the BIGGEST sort of the day or something like that ????

Just by increasing the size of the tablespace may solve the problem for the time being , but i suspect something else..

Any other idea ???
PS: I am talking about a locally managed temporary tablespace

--Madhu

Something is using up all that space. I'd bet that it is being used by sorts or index creation.

Maybe you are using Global Temporary Tables that have a lot of activity?

Look at v$sort_usage for the piggies of TEMP usage.

All users' temporary tablespace must be assigned to temporary tablespace (TEMP). The default value is SYSTEM tablespace.

Change the type of the TEMP temporary to "TEMPORARY" and NOLOGGING.

INITIAL and NEXT extent size should be equal to ((N*SORT_AREA_SIZE)+BLOCK_SIZE)

Jeff,
I did check the v$sort_usage , but I couldnt see any user except SYSTEM user doing sorts now.

And I also queried v$sort_segment, here the values i found

Total extents : 499
Total blocks :255488
Used extents : 58
used blocks : 29696
extent hits : 8689
max sort blocks : 46592

And to remember you again , 99% of my sorts are on memeory ... May be u may have some picture now with the above details.

I think the TEMP tablespace is having enough space left over , but still i am getting the error , not on every day but atleast once in a week.

I would like to arrest this problem.. Is there anyway to check to see the biggest sort in the past ???

Still I am not clear , how to solve this problem???

PS: I am not having any Global Temporary tables and just to rememeber this is a locally managed tablespace with nologging option and unform , 4MB

thanks for your help,
Madhu

v$sort_usage will only show large segments in TEMP when the process that does alot of sorts is running.

Is there any way to dig the past sorts ???
Pl let me know how to diagnose this problem ???

thanks in advance,
Madhu

May be this query little bit help u solve sort activity problems:

COLUMN txt FORMAT A50 word
heading 'SQL text' justify left
COLUMN username FORMAT A20 heading 'User name' justify left
COLUMN sid FORMAT 9999 heading 'SID' justify right
COLUMN s# FORMAT 99999 heading 'Serial' justify right
COLUMN cur_cnt FORMAT 99999 heading '#|curs' justify right
COLUMN sort FORMAT 99999 heading 'Disk|sorts' justify right
COLUMN mem FORMAT 999G999G999 heading 'Total|memory' justify right
COLUMN exe FORMAT 999G999G999 heading 'Total|exec' justify right
COLUMN gets FORMAT 99999999 heading 'Blocks|get' justify right
COLUMN reads FORMAT 99999999 heading 'Blocks|read' justify right
COLUMN prows FORMAT 99999999 heading '#|rows' justify right
COLUMN hit FORMAT A24
heading 'Hit ratio' justify left

select
s.sid sid,
s.serial# s#,
s.username username,
a.sql_text txt,
count(c.sid) cur_cnt,
sum(sharable_mem)+sum(runtime_mem) mem,
sum(executions) exe ,
decode(sum(buffer_gets),0,'0',
ltrim(to_char(100-(sum(disk_reads)/(sum(disk_reads)+sum(buffer_gets))*100),'990D99'))||'% ('||
ltrim(to_char(sum(disk_reads),'99999999999'))||'/'||
ltrim(to_char(sum(buffer_gets),'999999999999'))||')') hit,
sum(sorts) sort,
sum(rows_processed) prows
from v$session s,
v$open_cursor c,
v$sqlarea a
where s.SQL_ADDRESS=c.ADDRESS(+) and
s.username is not null and
s.SQL_ADDRESS=a.ADDRESS and
s.username not in ('SYSTEM','SYS')
group by s.sid,
s.serial#,
s.username,
s.SQL_ADDRESS,
c.ADDRESS,
a.sql_text
order by 3,1;

COLUMN txt clear
COLUMN username clear
COLUMN sid clear
COLUMN s# clear
COLUMN cur_cnt clear
COLUMN sort clear
COLUMN mem clear
COLUMN exe clear
COLUMN gets clear
COLUMN reads clear
COLUMN prows clear
COLUMN hit clear

add event in your init.ora so there will be trace file to be diagnoticed

event = "1652 trace name errorstack level 12"

Stestakov ,
Your query shows me the present sorts , i ran it now and did see few rows , would you pl explain the columns

SORT,PROWS...

I could see some good hit ratios and negative values in sort column and sometimes 0, the other times a positive value ex: 100 ... what does that indicate ???

Pando,

do i need to bounce the database after adding the event in the init.ora , i have never added any events to init.ora file.

Thanks,
Madhu