系統(tǒng)：Windows 11

數(shù)據(jù)庫：Oracle 19.3.0.0

問題描述：drop臨時表空間時報錯ORA-60100，如下所示：

SQL> drop tablespace temp includingcontents and datafiles;

drop tablespace temp including contents anddatafiles

*

第 1 行出現(xiàn)錯誤:

ORA-60100: 由于排序段, 已阻止刪除表空間 ID 號 (tsn) 為 3 的臨時表空間

異常原因：

temp表空間還有會話占用，可通過v$sort_usage查詢，殺掉占用的會話或等會話執(zhí)行完畢釋放后再刪除.

--查詢語句

set line 200

col sql_text for a50

col tablespace for a15

col username for a20

col username for a10

Select se.username,

se.sid,

se.serial#,

su.extents,

su.blocks *to_number(rtrim(p.value)) as Space,

tablespace,

segtype,

sql_text

from v$sort_usagesu, v$parameter p, v$session se, v$sql s

where p.name = 'db_block_size'

andsu.session_addr = se.saddr

and s.hash_value= su.sqlhash

and s.address =su.sqladdr

order by se.username, se.sid;

USERNAME SID SERIAL# EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT

---------- ---------- ---------- -------------------- --------------- --------------------------------------------------------------------

63 9766 1 1048576 TEMP DATA select count(*) from ilmobj$ whererownum = 1

SQL> alter system kill session '63,9766'immediate;

系統(tǒng)已更改.

SQL> drop tablespace temp including contents anddatafiles;

表空間已已刪除