행위

템프테이블

DB CAFE

1 템프러리 테이블(TEMPORARY TABLE)

  1. SESSION(또는 TRANSACTION) 레벨의 임시 데이터를 저장하는 용도
    - (오라클의 실행 계획을 저장하는 Plan 테이블이 전역 임시 테이블 임)
  2. SESSION(또는 TRANSACTION) 레벨의 임시 데이타 저장하기 때문에 RAC 시스템에서 글로벌 동기화 불필요
  3. DML LOCK 필요없음 (세션 DATA 간 경합이 발생하지 않음)
  4. REDO LOG 발생 안함 (임시 DATA 이므로 DML문이라도 redo log 발생 하지 않음)

1.1 템프 테이블(TEMPORARY) 생성

CREATE GLOBAL TEMPORARY TABLE AAAA_TEMP(
    A VARCHAR2(15) NOT NULL,
    B NUMBER NOT NULL
) ON COMMIT [PRESERVE ROWS | DELETE ROWS];


  • ON COMMIT PRESERVE ROWS : 트랜잭션의 종료 (COMMIT)후에도 데이터는 남아있음(PRESERVE:보존하다). 세션종료시 데이터가 삭제됨
  • ON COMMIT DELETE ROWS : 트랜잭션의 종료(COMMIT) 와 함께 삭제됨
  • [SUB QUERY CLAUSE] SELECT된 테이블의 구조와 같이 테이블 생성



1.1.1 예시 1) TRANSACTION 단위 DATA 유지

1.1.1.1 ON COMMIT DELETE ROWS
CREATE GLOBAL TEMPORARY TABLE TEST_TEMPORARY (
    TEST_NO NUMBER(20) 
) 
ON COMMIT DELETE ROWS; -- 기본옵션, commit시 데이터 삭제(종료시에도 삭제)

INSERT INTO TEST_TEMPORARY VALUES (1);
INSERT INTO TEST_TEMPORARY VALUES (2);
  • 데이터 조회
SELECT * FROM TEST_TEMPORARY;
  • Transaction 단위 데이터 유지되는 옵션을 사용 한 테이블 이기 때문에 COMMIT;을 입력하면 데이터 삭제됨.
COMMIT;
  • 데이터 조회
SELECT * FROM TEST_TEMPORARY;

1.1.2 예시 2) SESSION 단위 DATA 유지

1.1.2.1 ON COMMIT PRESERVE ROWS
CREATE GLOBAL TEMPORARY TABLE TEST_TEMPORARY2 (
    TEST_NO NUMBER(20) 
) ON COMMIT PRESERVE ROWS;

INSERT INTO TEST_TEMPORARY2 VALUES (1);
INSERT INTO TEST_TEMPORARY2 VALUES (2);
  • 데이터 조회
SELECT * FROM TEST_TEMPORARY2;
  • Session 단위 데이터 유지되는 옵션을 사용 했기 때문에 COMMIT;을 입력해도 데이터 유지 됨.
COMMIT;
SELECT * FROM TEST_TEMPORARY2;
  • 하지만 다른 세션(신규 접속)에서 해당 데이터를 조회 하면 데이터가 없다.
    • - 테이블은 모든 세션에서 볼 수 있고 데이터는 세션별로 독립적이기 때문에 다른 세션 에서 접근할 수 없음.
SELECT * FROM TEST_TEMPORARY2;

2 템프 테이블스페이스 사이즈 증가

ALTER TABLESPACE TEMP 
             ADD TEMPFILE '+DATA' 
            SIZE 10G AUTOEXTEND ON NEXT 100M 
         MAXSIZE 32767M;

2.1 서브 쿼리를 이용한 임시 테이블 생성

CREATE GLOBAL TEMPORARY TABLE [TABLE NAME](
)
ON COMMIT [PRESERVE ROWS | DELETE ROWS];
AS ([SUB QUERY CLAUSE])

2.2 TEMP 테이블 조회

SELECT TABLE_NAME, TEMPORARY, DURATION
  FROM USER_TABLES
 WHERE TEMPORARY = 'Y'

2.2.1 TEMP 테이블 삭제시 락 있을때 삭제 불가

SELECT 'alter system kill session '''||s.sid||','||s.serial#||''';'
  FROM v$lock l  ,dba_objects o   ,v$session s
 WHERE l.id1 = o.object_id
   AND s.sid = l.sid
   AND o.owner = 'SCOTT'  
   AND o.object_name = 'TEMP_TEST'

2.3 템프 테이블 모니터링

SELECT /*+ ordered */ "SID",
             s.serial# "Serial",
             s.MODULE "Module",
             s.program "Program",
             u.TABLESPACE "TS 명", 
             u.CONTENTS "Cont.",
             u.blocks "Temp Blocks",
             ((u.blocks*v.value)/(1024*1024)) "Temp Size(MB)"
             , q.sql_id     
                      
             
--             substr(q.sql_text,1,50) "SQL"
--             , substr(p.sql_text,1,50) "이전 SQL" 
FROM    v$sort_usage u,
             v$session s,
             v$sqltext q,
             v$sqltext p, 
             (SELECT value 
              FROM   v$parameter
              WHERE name = 'db_block_size') v 
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
;