1.新建一个TEMP02作为默认表空间,然后删除之前的表空间
create temporary tablespace temp02 tempfile '/u01/app/oracle/oradata/sfmtest/temp02.dbf' size 1g;
alter user bosswg temporary tablespace temp02;
alter database default temporary tablespace temp02;
select username,default_tablespace,temporary_tablespace from dba_users;
ps:如果TEMP表空间还有会话在使用,删除TEMP表空间会失败,一下语句是查下TEMP表空间是否还有会话在使用
select m.USERNAME,
m.SID,
m.SERIAL#,
m.SQL_ADDRESS,
m.MACHINE,
m.PROGRAM,
n.TABLESPACE,
n.SEGTYPE,
n.CONTENTS
from v$session m, v$sort_usage n
where m.SADDR = n.SESSION_ADDR;
删除掉使用TEMP空间的会话
alter system kill session 'SID,SERIAL#';
然后再执行
drop tablespace temp including contents and datafiles;
转载于:https://blog.51cto.com/springseaside/1679491