"템프테이블"의 두 판 사이의 차이
DB CAFE
(→TEMP 테이블 삭제시 락 있을때 삭제 불가) |
(→템프 테이블(TEMPORARY) 생성) |
||
(같은 사용자의 중간 판 17개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | == | + | == 템프러리 테이블(TEMPORARY TABLE) == |
+ | # SESSION(또는 TRANSACTION) 레벨의 임시 데이터를 저장하는 용도 | ||
+ | #: - (오라클의 실행 계획을 저장하는 Plan 테이블이 전역 임시 테이블 임) | ||
+ | # SESSION(또는 TRANSACTION) 레벨의 임시 데이타 저장하기 때문에 RAC 시스템에서 글로벌 동기화 불필요 | ||
+ | # DML LOCK 필요없음 (세션 DATA 간 경합이 발생하지 않음) | ||
+ | # REDO LOG 발생 안함 (임시 DATA 이므로 DML문이라도 redo log 발생 하지 않음) | ||
---- | ---- | ||
− | === | + | === 템프러리 테이블(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> | ||
== 템프 테이블스페이스 사이즈 증가 == | == 템프 테이블스페이스 사이즈 증가 == | ||
18번째 줄: | 80번째 줄: | ||
</SOURCE> | </SOURCE> | ||
− | --- | + | ---- |
=== 서브 쿼리를 이용한 임시 테이블 생성 === | === 서브 쿼리를 이용한 임시 테이블 생성 === | ||
<source lang=sql> | <source lang=sql> | ||
35번째 줄: | 97번째 줄: | ||
WHERE TEMPORARY = 'Y' | WHERE TEMPORARY = 'Y' | ||
</source> | </source> | ||
− | + | ||
− | |||
− | |||
---- | ---- | ||
==== TEMP 테이블 삭제시 락 있을때 삭제 불가 ==== | ==== TEMP 테이블 삭제시 락 있을때 삭제 불가 ==== | ||
48번째 줄: | 108번째 줄: | ||
AND o.object_name = 'TEMP_TEST' | AND o.object_name = 'TEMP_TEST' | ||
</source> | </source> | ||
+ | ---- | ||
=== 템프 테이블 모니터링 === | === 템프 테이블 모니터링 === |
2023년 7월 6일 (목) 21:19 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 템프러리 테이블(TEMPORARY TABLE)[편집]
- SESSION(또는 TRANSACTION) 레벨의 임시 데이터를 저장하는 용도
- - (오라클의 실행 계획을 저장하는 Plan 테이블이 전역 임시 테이블 임)
- SESSION(또는 TRANSACTION) 레벨의 임시 데이타 저장하기 때문에 RAC 시스템에서 글로벌 동기화 불필요
- DML LOCK 필요없음 (세션 DATA 간 경합이 발생하지 않음)
- 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];
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
;