Month: September 2010

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;

(more…)