TEMPORARY TABLESPACE 에 대한 각종 내용 정리.

TEMPORARY TABLESPACE 란?

 - 테이블스페이스 파일 용량이 부족할 때를 대비해서 임시 테이블스페이스와 임시 파일을 계정에 연결

 

-- 디폴트 테이블 스페이스 확인

select * from sys.props$ where name = 'DEFAULT_TEMP_TABLESPACE';

 

-- 기본 temp files에 대한 정보 확인

select * from dba_temp_files ;

 

-- 사용량 확인

select * from V$TEMPSEG_USAGE;

 

-- 그룹에서 빼기

alter tablespace "TEMP_DB" tablespace group '';

 

-- 그룹에서 넣기

alter tablespace TEMP_DB tablespace group TEMP_GROUP1;

 

320x100

 

-- 테이블스페이스 그룹 확인

select * from dba_tablespace_groups;

 

-- 유저개정 생성하면 temporary tablespace 지정

 create user bradkim identified by bradkim 
default tablespace zzz temporary tablespace TEMP_DB;

 

-- TEMP 테이블스페이스 확인

select * from dba_tablespaces where contents='TEMPORARY';

 

-- 테이블스페이스 확인

select * from v$tablespace; 

 

-- TEMP SPACE 용량 확인

select * from DBA_TEMP_FREE_SPACE ;

 

-- TABLESPACE 삭제

DROP TABLESPACE "TEMP_DB" ;

-- 추가옵션1 INCLUDING CONTENTS AND DATAFILES;

-- 추가옵션2 CASCADE CONSTRAINTS;

 

-- TEMPORARY TABLESPACE 생성

CREATE TEMPORARY TABLESPACE TEMP_DB tempfile '/data/TEST/system/temp_01.dbf' size 10M;

 

-- 테이블 스페이스 SHRINK SPACE (주의해서 사용)

ALTER TABLESPACE TEMP SHRINK SPACE ;

 

-- 기타 내용 조회

   select * from dba_data_files  ;
   select * from dba_tablespaces ;
   select * from user_users ;

 

-- TEMPORARY TABLESPACE 사용량 확인

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';

 

SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type",        
       d.extent_management "Extent Management",                               
       TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)", 
          TO_CHAR (NVL (u.BYTES, 0) / 1024 / 1024, '99999999.999')            
       || '/'                                                                 
       || TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') "Used (M)",
       TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Cached %"    ,      
       TO_CHAR (NVL (u.BYTES / a.BYTES * 100, 0), '990.00') "Used %"          
  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_cached) BYTES                      
            FROM v$temp_extent_pool                                           
        GROUP BY tablespace_name) t ,
        (SELECT   tablespace_name, SUM (bytes_used) BYTES                      
            FROM v$temp_extent_pool                                           
        GROUP BY tablespace_name) u                                                  
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';

 

 

-- MACHINE 별 현재 세션 사용중인 정보 확인

SELECT   distinct a.sid,  a.serial#,
  a.machine,  a.terminal,  a.program,
  b.address,  b.piece,  b.sql_text
FROM   v$session a,  v$sqltext b
WHERE   a.sql_address = b.address
and MACHINE = 'bradkim-PC'
order by a.sid, a.serial#,b.address,b.piece;

 

temporary tablespace 작업 시 해당 공간에 세션에 붙어있으면 제거하고 작업을 진행해야한다.

 

-- 현재  temp 세션 사용중인 정보 확인

select tu.tablespace, tu.username,s.sid, s.serial#,s.sql_id, s.prev_sql_id,s.status, s.osuser, s.process, s.machine, 
s.port,s.prev_exec_start, s.logon_time,s.last_call_et,
'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' SQL_TEXT 
from v$tempseg_usage tu, v$session s where tu.session_addr = s.saddr;

 

-- 세션 죽이기~

alter system kill session '8,21259';