Oracle技术分享 ORA-60100 异常处理

系统：Windows 11

数据库：Oracle 19.3.0.0

问题描述：drop临时表空间时报错ORA-60100，如下所示：

SQL> drop tablespace temp includingcontents and datafiles;

drop tablespace temp including contents anddatafiles

*

第 1 行出现错误:

ORA-60100: 由于排序段, 已阻止删除表空间 ID 号 (tsn) 为 3 的临时表空间

异常原因：

temp表空间还有会话占用，可通过v$sort_usage查询，杀掉占用的会话或等会话执行完毕释放后再删除.

--查询语句

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;

系统已更改.

SQL> drop tablespace temp including contents anddatafiles;

表空间已已删除