'temporary tablespace'에 해당되는 글 1건

  1. 2011/09/09 boyo temporary tablespace 용량 변경

오라클 9i 이전과 이후에 temporary tablespace 설정이 다르며, 아래 사항은 9i 이후 설정방법 입니다.

temp tablespace 관련 테이블 : V$TEMPFILE, dba_temp_files, V$TEMP_EXTENT_POOL


1. temp tablespace 설정 상태 확인
SQL> select * from dba_temp_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/data/ORCL/datafile/o1_mf_temp_3vsro6sw_.tmp
         1 TEMP                           1066401792     130176 AVAILABLE            1 YES 3.4360E+10    4194302           80 1065353216      130048


2. temp tablespace 사용량 및 용량 확인
SED,(B.BYTES-A.BYTES_USED)/1024/1024 || ' M' "FREE", ROUND(A.BYTES_USED/B.BYTES SQL> SELECT B.NAME ,B.BYTES/1024/1024 || ' M' TOTAL SELECT B.NAME ,B.BYTES/1024/1024 || ' M' TOTAL ,A.BYTES_USED/1024/1024 || ' M' USED,(B.BYTES-A.BYTES_USED)/1024/1024 || ' M' "FREE", ROUND(A.BYTES_USED/B.BYTES * 100) || '%' "USAGE" FROM
(SELECT BYTES_USED,FILE_ID FROM V$TEMP_EXTENT_POOL)A, (SELECT BYTES,FILE#,NAME FROM V$TEMPFILE)B WHERE A.FILE_ID = B.FILE#;


3. temp tablespace 사용중인 세션 확인
set line 150
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
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;



[참고 사이트]
http://blog.naver.com/PostView.nhn?blogId=dangtong76&logNo=140042613114
http://dbrang.tistory.com/564
http://blog.daum.net/liberalis/12917306
https://forums.oracle.com/forums/thread.jspa?threadID=427050

2011/09/09 16:41 2011/09/09 16:41