smon exclusive lock on i_obj# for offline temp tbs

did a regular temp tbs exchange lately, you know:

-- create an intermediate tbs
create temporary tablespace tempo
  tempfile 'm:\tempo_01.dbf'  size 512m autoextend on next 128m maxsize 2048m;
-- make it the default for the exchange time
alter database default temporary tablespace tempo;
-- offline and drop the old stuff
alter database tempfile 'm:\temp01.dbf' offline;
drop tablespace temp including contents and datafiles cascade constraints;
-- create the new tbs with the original name
create temporary tablespace temp
  tempfile 'm:\temp_01.dbf' size 512m autoextend on next 128m maxsize 2048m;
-- make this the default again
alter database default temporary tablespace temp;
-- offline and drop the intermediate stuff
alter database tempfile 'm:\tempo_01.dbf' offline;
drop tablespace tempo including contents and datafiles cascade constraints;

however the old tbs drop was hanging somehow and inspecting locks and sessions:

select l.sid, l.type || ' (' || l.id1 || '/' || l.id2 || ')' as lock_type,
    decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'Row-SELECT (SS)', 3, 'Row-X (SX)',
      4, 'SHARE', 5 ,'SELECT/Row-X (SSX)', 6,'EXCLUSIVE') as lock_hold_mode,
    decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'Row-SELECT (SS)', 3, 'Row-X (SX)',
      4, 'SHARE', 5 ,'SELECT/Row-X (SSX)', 6,'EXCLUSIVE') as lock_request_mode,
    decode(l.block, 0, 'F', 1, 'T') as is_blocking, s.blocking_session as blocking_sid,
    o.owner as object_owner, o.object_name, s.type as session_type, s.username, s.terminal,
    s.module, s.program, s.event, s.logon_time, s.sql_exec_start, s.seconds_in_wait
from dba_objects o, v$session s, v$lock l
  where l.id1 = o.object_id and l.sid = s.sid
order by l.block desc;

showed that the smon process locked this action because of an exclusive lock on sys.i_obj# ???!

was looking up sys.v$sort_segment for remaining segments with the old tbs but only the intermediate tbs was listed. stuck!

ok, v$tempseg_usage did better. it indeed still listed the olb tbs along with a possibility to examine the allocating sessions:

select A.*, B.sid from v$tempseg_usage A, v$session B
  where A.session_addr = B.saddr;

that way i found myself being inattentive in forgetting to check for permanent sessions against the database and temp space before doing the tbs exchange operation. great learning curve from smon / sys.i_obj# to v$tempseg_usage / v$session though.

have fun!