Oracle 12c 临时表空间清理

1, 需求

1, Oracle 数据库，临时表空间 FASTTEMP 满，需要清理。

2, 解决方法思考

1, 创建新的临时表空间 FASTTEMP2 及文件
2, 用 FASTTEMP2 替换 FASTTEMP
3, 删除 FASTTEMP 临时表空间及其文件
4, 创建新的临时表空间 FASTTEMP 及文件
5, 用 FASTTEMP 替换 FASTTEMP2
6, 删除 FASTTEMP2 临时表空间及其文件

3, 实施过程

1, 看一下临时表空间的使用情况

select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from  (select tablespace_name,sum(bytes) bytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;

2, 看一下谁在使用 FASTTEMP

-- 注意 sid 和 serial#，清除占用 fasttemp 临时表空间的 session 时会用到
SELECT SE.USERNAME,
SID,
SERIAL#,
SQL_ADDRESS,
MACHINE,
PROGRAM,
TABLESPACE,
SEGTYPE,
CONTENTS
FROM V$SESSION SE,
V$SORT_USAGE SU
WHERE SE.SADDR = SU.SESSION_ADDR;

KILL 使用的 session

-- 语法
Alter system kill session 'sid,serial#';
-- 示例
Alter system kill session '3391,37858';

3, 查看当前的数据库默认表空间：

-- 确认当前的临时表空间为 FASTTEMP
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

4, 查看目前临时表空间的大小

-- 确认数据文件路径，注意文件路径，单个文件大小及总数量和总大小， 构造新表空间和重构当前表空间时会用到

select file_name,tablespace_name,bytes/1024/1024/1024 "GB",autoextensible from dba_temp_files;

5, 创建另一个临时表空间

create temporary tablespace FASTTEMP2 tempfile '/oradata/orcl/fast/fasttemp2_01.dbf' size 30G autoextend on MAXSIZE unlimited;

6, 切换至新的临时表空间

-- 看一下哪些用户使用了 fasttemp 临时表空间，排除一下系统自创建的用户
select username, default_tablespace, temporary_tablespace, local_temp_tablespace from dba_users;

-- 改变缺省临时表空间为刚刚创建的新临时表空间 fasttemp2
alter database default temporary tablespace FASTTEMP2; 
alter user fast LOCAL TEMPORARY TABLESPACE FASTTEMP2;
alter user IWP LOCAL TEMPORARY TABLESPACE FASTTEMP2;
alter user USERROLE LOCAL TEMPORARY TABLESPACE FASTTEMP2;
alter user VOLTE LOCAL TEMPORARY TABLESPACE FASTTEMP2;

7, 删除原来临时表空间

drop tablespace FASTTEMP including contents and datafiles;

8, 重新创建临时表空间 fasttemp

create temporary tablespace FASTTEMP tempfile '/oradata/orcl/fast/fasttemp_01.dbf' size 30G autoextend on MAXSIZE unlimited;

alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_02.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_03.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_04.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_05.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_06.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_07.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_08.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_09.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_10.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_11.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_12.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_13.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_14.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_15.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_16.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_17.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_18.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_19.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_20.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_21.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_22.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_23.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_24.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_25.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_26.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_27.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_28.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_29.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_30.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_31.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_32.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_33.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_34.dbf' size 30G autoextend on MAXSIZE unlimited;
alter tablespace FASTTEMP add tempfile '/oradata/orcl/fast/fasttemp_35.dbf' size 30G autoextend on MAXSIZE unlimited;

9, 重置缺省临时表空间为新建的 fasttemp 表空间

alter database default temporary tablespace FASTTEMP; 
     
alter user fast LOCAL TEMPORARY TABLESPACE FASTTEMP;
alter user IWP LOCAL TEMPORARY TABLESPACE FASTTEMP;
alter user USERROLE LOCAL TEMPORARY TABLESPACE FASTTEMP;
alter user VOLTE LOCAL TEMPORARY TABLESPACE FASTTEMP;

10, 删除中转用临时表空间及其文件

drop tablespace FASTTEMP2 including contents and datafiles;