Oracle Database Discussions

Hi ,

I have a 11.2.0.1 system with 2 node RAC and ASM . We are hitting ORA-01652 very frequently
and I can see space is available in TEMP .

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
SIZE 21474836480,
SIZE 137437904896
AUTOEXTEND ON NEXT 655360 MAXSIZE 131071M ( Will the next size ignored as the extent size is 1MB ?)
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

And during the failure

SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*32768/1024/1024 as total_MB,
used_blocks*32768/1024/1024 as used_MB,
free_blocks*32768/1024/1024 as free_MB
FROM gv$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------------------------------ ------------------------------ ------------------- --------------------- ---------------- ------------------- -------------
TEMP 4849568 2385728 2463840 151549 74554 76995
TEMP 0 0 0 0 0 0


Also when I checked from v$sort_usage I see only two session consuming space

SQL> SELECT b.tablespace tablespace,
2 -- b.segfile#,
3 -- b.segblk#,
4 b.blocks,
5 b.blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size')/1024/1024 bytes,
6 a.sid,
7 a.username username,
8 a.osuser osuser,
9 a.inst_id,a.sid,a.serial#,a.module
10 FROM gv$session a, gv$sort_usage b
11 WHERE a.saddr = b.session_addr and
12 b.blocks != 16 and
13 a.inst_id = b.inst_id
14 ORDER BY b.tablespace, b.blocks, bytes desc;

BLOCKS BYTES SID USERNAME OSUSER INST_ID SID SERIAL# MODULE
---------------------------- ---------- -------- ------------- -------------- ------------- --------- -------------- ----------------------------
1,192,864 37277 381 CN obiqb 2 381 860 nqsserver@icmqapp01cdp (TNS V1
1,192,864 37277 3952 CN obiqb 2 3952 1374 nqsserver@icmqapp01cdp (TNS V1
-------------------------- -----------------
2,385,728 74554 (mb)


Wat is causing the 1652 ?