[ORACLE] 임시테이블(GLOBAL TABLE) 사용여부 SQL 쿼리 및 KILL SESSION

- 임시 테이블의 컬럼 변경(추가/수정/삭제) 시 발생

 

ALTER TABLE 임시테이블 ADD (컬럼 VARCHAR2(7 CHAR) NULL); // 오류발생 ALTER TABLE 임시테이블 ADD (컬럼 VARCHAR2(7 CHAR) NULL); 오류 보고 - ORA-14450: attempt to access a transactional temp table already in use 14450. 00000 - "attempt to access a transactional temp table already in use" *Cause: An attempt was made to access a transactional temporary table that has been already populated by a concurrent transaction of the same session. *Action: do not attempt to access the temporary table until the concurrent transaction has committed or aborted.

 

 

이유 > 트랜젝션이 COMMIT 또는 ROLLBACK 될 떄까지 임시테이블 엑세스 금지

 

[검증 쿼리]

 

SELECT A.OBJECT_NAME, C.SID, C.SERIAL#, C.USERNAME, C.OSUSER, C.MACHINE, 'ALTER SYSTEM KILL SESSION ''' || TO_CHAR(C.SID) || ',' || TO_CHAR(C.SERIAL#) || ''';' KILL_CMD FROM USER_OBJECTS A INNER JOIN V$LOCK B ON B.ID1 = A.OBJECT_ID AND B.TYPE = 'TO' INNER JOIN V$SESSION C ON C.SID = B.SID WHERE OBJECT_NAME = '임시테이블';

 

<결과>