删除临时表空间或者默认临时表空间

旧城等待， 2023-05-31 07:47 10阅读 0赞

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';

![在这里插入图片描述][20191024091848796.png]  
看到默认临时表空间组为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;

[20191024091848796.png]: /images/20230531/1cb04f951720495684528eafce4b95ca.png