오라클 temp 할당량 및 실시간 사용량 확인 쿼리

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 오라클 temp 할당량 및 사용량 확인 쿼리

할당된 TEMP 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

SQL> set pages 40 set line 132 col tbs_name format a15 col Used_mega format a15 col Used_PCT format 999.99 col Cache_PCT format 999.99 SELECT d.tablespace_name tbs_name, d.status Status, d.CONTENTS Type, d.extent_management Ext_manage, NVL(a.BYTES / 1024 / 1024, 0) Total_mega, NVL(t.BYTES, 1)/1024/1024 ||' / '|| NVL(a.BYTES / 1024 / 1024, 1) Used_mega, NVL(t.BYTES / a.BYTES * 100, 1) Used_PCT, NVL(t.curnt_byte/1024/1024, 1) Cache_mega, (NVL(t.curnt_byte/1024/1024, 1)/NVL(a.BYTES / 1024 / 1024, 0)*100) Cache_PCT FROM SYS.dba_tablespaces d, (SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_temp_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM (BYTES_USED) curnt_byte, sum(BYTES_CACHED) BYTES FROM v$temp_extent_pool GROUP BY tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY' /   TBS_NAME    STATUS      TYPE        EXT_MANAGE TOTAL_MEGA USED_MEGA           USED_PCT CACHE_MEGA CACHE_PCT --------------- --------- --------- ---------- ---------- -------------------- -------- ---------- --------- TEMP        ONLINE      TEMPORARY LOCAL           20 6 / 20          30.00      0     .00

사용된 TEMP 확인(사용중이 아니어도 used가 한번 늘어난 temp는 줄지 않음(재활용하기위해 used로 표시됨))

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> set lines 200 set pages 1000 col total for 999,999,999,999 col used for 999,999,999,999 col free for 999,999,999,999 col "used(%)" for a10 select s.tablespace_name, sum(s.BYTES_USED+bytes_free) total,         sum(s.BYTES_USED) used,         sum(bytes_free) free,        round(sum(s.BYTES_USED)/sum(s.BYTES_USED+bytes_free)*100,2)||' %' "used(%)" from v$temp_space_header s group by s.tablespace_name /   TABLESPACE_NAME               TOTAL         USED         FREE used(%) ------------------------------ ---------------- ---------------- ---------------- ---------- TEMP                     20,971,520        7,340,032       13,631,488 35 %

현재 사용중인(실시간) temp 사용중인 유저 확인

SQL>
set line 150
set concat "+"
col username format a10
col osuser format a10
col tablespace format a15
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
c.spid,
a.username,
a.osuser,
a.status,
a.sql_hash_value
FROM v$session a,
v$sort_usage b,
v$process c
WHERE a.saddr = b.session_addr and a.paddr=c.addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks
/
 
TABLESPACE      SEGFILE#    SEGBLK#      BLOCKS    SID    SERIAL# SPID             USERNAME   OSUSER      STATUS   SQL_HASH_VALUE
--------------- ---------- ---------- ---------- ------ ---------- ------------------------ ---------- ---------- -------- --------------
TEMP               201      128       63104     45        707 3959             IMSI       oracle      INACTIVE     4193336422

참조 : 예전 쿼리 https://positivemh.tistory.com/260