ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is blocked due to sort segments

OS 환경 : Oracle Linux 7.9 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

에러 : ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is blocked due to sort segments

temp 테이블스페이스 삭제 시 발생하는 메세지

1 2 3 4 5

SQL> drop tablespace temp including contents and datafiles; drop tablespace temp including contents and datafiles * ERROR at line 1: ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is blocked due to sort segments

 

 

해결 방법 : temp 사용중인 세션 확인 후 기다리거나, kill 해도 상관 없는 경우 kill 진행 또는 db 재기동

 

temp 사용중인 세션 확인 후 기다리거나, kill 해도 상관 없는 경우 kill 진행 또는 db 재기동

 

 

현재 temp 사용중인 세션 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

SQL> set line 150 set concat "+" col username format a10 col osuser format a10 col tablespace format a15 SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, c.spid, a.username, a.osuser, a.status, a.sql_hash_value, c.pname FROM v$session a, v$sort_usage b, v$process c WHERE a.saddr = b.session_addr and a.paddr=c.addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;   TABLESPACE        SEGFILE#    SEGBLK#     BLOCKS        SID    SERIAL# SPID                     USERNAME   OSUSER     STATUS   SQL_HASH_VALUE PNAME --------------- ---------- ---------- ---------- ---------- ---------- ------------------------ ---------- ---------- -------- -------------- ----- TEMP                   201      30848        128         25      56098 60757                               oracle     ACTIVE                0 M002 TEMP                   201      31104        128         25      56098 60757                               oracle     ACTIVE                0 M002 TEMP                   201     584448        128         25      56098 60757                               oracle     ACTIVE                0 M002

나의 경우 M002 프로세스 세션이 temp 를 사용중임(Shared MMON Slave Process)
시스템 백그라운드 프로세스 이므로 내가 kill 할수는 없는 상황임

 

 

db 재기동 진행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.   SQL> startup ORACLE instance started.   Total System Global Area 2147481656 bytes Fixed Size                  8898616 bytes Variable Size            1962934272 bytes Database Buffers          167772160 bytes Redo Buffers                7876608 bytes Database mounted. Database opened.

 

 

temp 테이블스페이스 삭제

1 2 3

SQL> drop tablespace temp including contents and datafiles;   Tablespace dropped.

정상적으로 삭제됨

 

 

만약 위 경우에서 세션이 시스템 세션이 아닌 일반 유저 세션인 경우 temp 사용이 종료될때 까지 기다리거나
AP 팀에 kill 가능여부 확인 후 해당 세션 kill

1 2 3

SQL> alter system kill session '123, 45678' immediate;   System altered.

 

 

원인 : 현재 temp를 사용중인 세션이 존재해서 발생한 문제

현재 temp를 사용중인 세션이 존재해서 발생한 문제

해당 세션 정리 후 정상적으로 제거 가능함

 

 

참조 : 

2696984.1
https://positivemh.tistory.com/481
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/background-processes.html

https://positivemh.tistory.com/1028