행위

"템프테이블"의 두 판 사이의 차이

DB CAFE
(→‎템프 테이블(TEMPORARY) 생성)
 
(같은 사용자의 중간 판 33개는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
= 템프테이블(TEMPORARY TABLE) =
+
== 템프러리 테이블(TEMPORARY TABLE) ==
 +
# SESSION(또는 TRANSACTION) 레벨의 임시 데이터를 저장하는 용도
 +
#: - (오라클의 실행 계획을 저장하는 Plan 테이블이 전역 임시 테이블 임)
 +
# SESSION(또는 TRANSACTION) 레벨의 임시 데이타 저장하기 때문에 RAC 시스템에서 글로벌 동기화 불필요
 +
# DML LOCK 필요없음 (세션 DATA 간 경합이 발생하지 않음)
 +
# REDO LOG 발생 안함 (임시 DATA 이므로 DML문이라도 redo log 발생 하지 않음)
 +
----
 +
=== 템프러리 테이블(TEMPORARY) 생성 ===
  
−
-- 템프테이블(TEMPORARY) 생성
 
 
<source lang=sql>
 
<source lang=sql>
 
CREATE GLOBAL TEMPORARY TABLE AAAA_TEMP(
 
CREATE GLOBAL TEMPORARY TABLE AAAA_TEMP(
7번째 줄: 13번째 줄:
 
     B NUMBER NOT NULL
 
     B NUMBER NOT NULL
 
) ON COMMIT [PRESERVE ROWS | DELETE ROWS];
 
) ON COMMIT [PRESERVE ROWS | DELETE ROWS];
−
</source>  
+
</source>
−
  -- 서브 쿼리를 이용한 임시 테이블 생성
+
{{틀:고지상자
 +
|제목 = 템프러리 테이블 옵션 (PRESERVE / DELETE) ROWS 
 +
|내용 =
 +
* ON COMMIT '''PRESERVE ROWS''' : 트랜잭션의 종료 (COMMIT)후에도 데이터는 남아있음(PRESERVE:보존하다). 세션종료시 데이터가 삭제됨
 +
* ON COMMIT '''DELETE ROWS''' : 트랜잭션의 종료(COMMIT) 와 함께 삭제됨
 +
* [SUB QUERY CLAUSE] SELECT된 테이블의 구조와 같이 테이블 생성
 +
}}
 +
----
 +
==== 예시 1) TRANSACTION 단위 DATA 유지 ====
 +
===== ON COMMIT DELETE ROWS =====
 +
<source lang=sql>
 +
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);
 +
</source>
 +
* 데이터 조회
 +
<source lang=sql>
 +
SELECT * FROM TEST_TEMPORARY;
 +
</source>
 +
* Transaction 단위 데이터 유지되는 옵션을 사용 한 테이블 이기 때문에 COMMIT;을 입력하면 데이터 삭제됨.
 +
<source lang=sql>
 +
COMMIT;
 +
</source>
 +
* 데이터 조회  
 +
<source lang=sql>
 +
SELECT * FROM TEST_TEMPORARY;
 +
</source>
 +
 
 +
==== 예시 2) SESSION 단위 DATA 유지====
 +
===== ON COMMIT PRESERVE ROWS =====
 +
<source lang=sql>
 +
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);
 +
</source>
 +
* 데이터 조회
 +
<source lang=sql>
 +
SELECT * FROM TEST_TEMPORARY2;
 +
</source>
 +
* Session 단위 데이터 유지되는 옵션을 사용 했기 때문에 COMMIT;을 입력해도 데이터 유지 됨.
 +
<source lang=sql>
 +
COMMIT;
 +
SELECT * FROM TEST_TEMPORARY2;
 +
</source>
 +
* 하지만 다른 세션(신규 접속)에서 해당 데이터를 조회 하면 데이터가 없다.
 +
** - 테이블은 모든 세션에서 볼 수 있고 데이터는 세션별로 독립적이기 때문에 다른 세션 에서 접근할 수 없음.
 +
<source lang=sql>
 +
SELECT * FROM TEST_TEMPORARY2;
 +
</source>
 +
 
 +
== 템프 테이블스페이스 사이즈 증가 ==
 +
 
 +
<source lang=sql>
 +
ALTER TABLESPACE TEMP
 +
            ADD TEMPFILE '+DATA'
 +
            SIZE 10G AUTOEXTEND ON NEXT 100M
 +
        MAXSIZE 32767M;
 +
</SOURCE>
 +
 
 +
----  
 +
=== 서브 쿼리를 이용한 임시 테이블 생성 ===
 
<source lang=sql>
 
<source lang=sql>
 
CREATE GLOBAL TEMPORARY TABLE [TABLE NAME](
 
CREATE GLOBAL TEMPORARY TABLE [TABLE NAME](
15번째 줄: 88번째 줄:
 
AS ([SUB QUERY CLAUSE])
 
AS ([SUB QUERY CLAUSE])
 
</source>  
 
</source>  
−
-- 임시 테이블 조회
+
 
 +
----
 +
 
 +
=== TEMP 테이블 조회 ===
 
<source lang=sql>
 
<source lang=sql>
 
SELECT TABLE_NAME, TEMPORARY, DURATION
 
SELECT TABLE_NAME, TEMPORARY, DURATION
21번째 줄: 97번째 줄:
 
  WHERE TEMPORARY = 'Y'
 
  WHERE TEMPORARY = 'Y'
 
</source>   
 
</source>   
−
--[PRESERVE ROWS] 트랜잭션의 종료 (commit)후에도 남아있음 세션종료시 데이터가 삭제됨
+
 
−
--[DELETE ROWS] 트랜잭션의 종료와 함께 삭제됨
+
----
−
--[SUB QUERY CLAUSE] SELECT된 테이블의 구조와 같이 테이블 생성
+
==== TEMP 테이블 삭제시 락 있을때 삭제 불가 ====
 +
<source lang=sql>
 +
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'
 +
</source>
 +
----
 +
 
 +
=== 템프 테이블 모니터링 ===
 +
 
 +
<source lang=sql>
 +
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
 +
;
 +
</source>
 +
[[Category:oracle]]

2023년 7월 6일 (목) 21:19 기준 최신판

thumb_up 추천메뉴 바로가기

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


assignment 템프러리 테이블 옵션 (PRESERVE / 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
;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=템프테이블&oldid=9053"