You won't be able to relocate the file with an open database. Mounted
is the best you can get.
You can either make sure the current datafile can't extend any further,
and a second one,
or you can create a new temporary tablespace, move all the users to the
new temporary tablespace, and drop the old one. You would need to make
sure the new tempfile has a max size of say 2 G, so you are forced to
add another file, if growth continues.
Changing the users can be done with one little block of plsql
begin
for i in (select username from dba_users) loop
execute immediate 'alter user '||i.username||' temporary tablespace
<new tablespace>';
end loop;
end;
/
And that should be all
--
Sybrand Bakker
Senior Oracle DBA
Received on Mon Feb 20 2006 - 09:40:59 CST
