1、如果临时表空间不是默认临时表空间以及默认临时表空间组成员,直接执行如下语句
drop tablespace temp1
including contents
and datafiles
cascade constraint;
-- including contents 删除包含内容 and datafiles 表示物理文件也删除 不再需要我们进入系统进行手工删除金数据文件。
--cascade constraint 表示连参照完整性约束一起删除。如果其他表空间中的约束引用了要被删除的表空间的主键 --和唯一约束,则必须指定这个选项,否则删除表空间,会报告错误
2、查看是否是默认临时表空间
SELECT t.property_name, t.property_value
FROM database_properties t
WHERE t.property_name = 'DEFAULT_TEMP_TABLESPACE';
3、查看是否是默认临时表空间组成员
SELECT t.*
FROM dba_tablespace_groups t, database_properties db
WHERE t.group_name=db.property_value
and db.property_name='DEFAULT_TEMP_TABLESPACE';
看到默认临时表空间组为TEMPGRP,组内成员为TEMP,TEMP2,TEMP3,TEMP4个临时表空间。
4、如果需要删除临时表空间是默认临时表空间
需要创建或者设置另外一个表空间为默认临时表空间。
创建默认临时表空间
--先创建临时表空间
create temporary tablespace temp02 tempfile' /data/dmis/tmpdata/temp101.dbf' size 1024M autoextend on;
--修改默认表空间为新创建的临时表空间
alter database default temporary tablespace temp02;
然后再删除原来的临时表空间
drop tablespace temp1
including contents
and datafiles
cascade constraint;
5、删除临时表空间是默认临时表空间组
此时需要将该临时表空间从临时表空间组中移除
alter tablespace temp1 tablespace group '';
然后再执行删除
执行删除后,发现删除语句长时间执行没有结果,估计是有其他进程在使用该临时表空间
通过如下语句进行判断
--获取 相应saddr
select USERNAME,SESSION_ADDR,SQL_ID,TABLESPACE,CONTENTS,BLOCKS from v$sort_usage;
--根据saddr 获取session SID SERIAL#
SELECT INST_ID,
sid,
serial#,
USERNAME,
STATUS,
MACHINE,
SQL_ID,
EVENT,
(SYSDATE - LOGON_TIME) * 86400 AS "s",
LAST_CALL_ET
FROM gv$session
WHERE saddr = #SESSION_ADDR;
--杀掉使用临时表空间进程
alter system kill session '#sid,#SERIAL' immediate;
再执行删除。
然后可以通过如下语句查看,该临时表空间以及表空间文件是否删除
select t.file_name,t.tablespace_name from dba_temp_files t;