ORA-01652 : unable to extend temp segment by 128 in

유지보수 하다가.. ORA-01652 : unable to extend temp segment by 128 in temp space 오류 메시지가 발견되었다.
답이 없다..ㅜ.ㅜ 해당 오류는 시간을 두면 전체 솔류션에 영향을 시키기 때문에 빨리 해결해야 했다.
우선 사용자에게 테이블 스페이스 용량을 확인 했다.
[쿼리]
SELECT A.tablespace_name,
Round(A.bytes_alloc / 1024 / 1024, 2)
CURRENT_SIZE,
Round(Nvl(B.bytes_free, 0) / 1024 / 1024, 2)
FREE_SIZE,
Round(( A.bytes_alloc - Nvl(B.bytes_free, 0) ) / 1024 / 1024, 2)
USED_SIZE,
Round(( Nvl(B.bytes_free, 0) / A.bytes_alloc ) * 100, 2)
FREE_RATE,
100 - Round(( Nvl(B.bytes_free, 0) / A.bytes_alloc ) * 100, 2)
USED_RATE,
Round(maxbytes / 1048576, 2) MAX_SIZE
FROM (SELECT F.tablespace_name,
SUM(F.bytes) BYTES_ALLOC,
SUM(Decode(F.autoextensible, 'YES', F.maxbytes,
'NO', F.bytes)) MAXBYTES
FROM dba_data_files F
GROUP BY tablespace_name) A,
(SELECT F.tablespace_name,
SUM(F.bytes) BYTES_FREE
FROM dba_free_space F
GROUP BY tablespace_name) B
WHERE A.tablespace_name = B.tablespace_name (+)
UNION
SELECT tablespace_name,
Round(SUM(bytes_used + bytes_free) / 1048576, 2),
Round(SUM(bytes_free) / 1048576, 2),
Round(SUM(bytes_used) / 1048576, 2),
Round(( SUM(bytes_free) / SUM(bytes_used + bytes_free) ) * 100, 2)
FREE_RATE,
100 - Round(( SUM(bytes_free) / SUM(bytes_used + bytes_free) ) * 100, 2)
USED_RATE,
Round(Max(bytes_used + bytes_free) / 1048576, 2)
FROM sys.v_$temp_space_header
GROUP BY tablespace_name
ORDER BY 1;
내용을 확인 해보니.. temp tablespace가 꽉찬 형태이다.

급한대로 아래의 명령어로 테이블 스페이스를 높여 주었다.
alter database tempfile '경로' resize 1G;

뭐.. 위의 쿼리로 안되면.. temp tablespace 경로를 변경해주면 되는데..
근데 중요한 것은 왜.... 500M 정도 되는데 탬플 스페이스 용량이 커져을까?
원인을 파악해야 한다.

확인을 못했지만 아래의 명령어를 통해서 확인 예정이다.

--trace 파일 위치 확인
set linesize 100;
set pagesize 100;
SELECT * FROM V$DIAG_INFO

-- 한달 동안 에러 로그 확인
set linesize 100;
set pagesize 100;
select originating_timestamp, message_text
from X$DBGALERTEXT
where originating_timestamp > sysdate - 31
and ( message_text like '%ORA-%' or message_text like '%Fatal%' );

--문제되는 쿼리 확인 -- 1
select se.username, ts.tablespace_name, su.blocksts.block_size/1024/1024 as mb_used --, sum(su.blocksts.block_size/1024/1024)mb_used
from v$sort_usage su, v$session se, dba_tablespaces ts, v$process pr
where su.session_addr = se.saddr
and se.paddr = pr.addr
and su.tablespace = ts.tablespace_name

--문제되는 쿼리 확인 -- 2
select /+ ordered */
"SID",
s.serial#,
s.module,
s.program,
u.tablespace,
u.contents,
u.blocks,
((u.blocks(select value from v$parameter where name = 'db_block_size'))/(1024*1024)) as "temp size(mb)",
q.sql_text as nowsql,
p.sql_text as beforesql
from v$sort_usage u, v$session s, v$sqltext q, v$sqltext p
where s.saddr = u.session_addr
and s.sql_hash_value = q.hash_value(+)
and q.piece = 0
and s.prev_hash_value = p.hash_value(+)
and p.piece = 0

--문제되는 쿼리 확인 -- 3
select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
from v$session a, v$tempseg_usage b, v$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value
order by b.tablespace, b.blocks;