Oracle 11g临时表空间在线回收机制

Oracle 11g临时表空间在线回收机制

临时表空间主要使用在：
    - 索引创建或重创建。
    - ORDER BY or GROUP BY （这个是‘罪魁祸首’）
    - DISTINCT 操作。
    - UNION & INTERSECT & MINUS - Sort-Merge joins. - Analyze 操作
    - 有些异常将会引起temp暴涨（这个也很有可能）

--数据库版本
SQL> select * from v$version;

--查看数据库默认临时表空间
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
select * from database_properties where property_name like 'DEFAULT%TABLESPACE';
select * from dba_tablespace_groups;

--检查临时表空间当前空闲情况（收回前查看临时表空间的空间情况）
set lines 200 pages 50000
col TABLESPACE_NAME for a30
col TABLESPACE_SIZE for 99999999999999
col ALLOCATED_SPACE for 99999999999999
col FREE_SPACE for      99999999999999
SELECT TABLESPACE_NAME,
      TABLESPACE_SIZE/1024/1024/1024 as "TABLESPACE_SIZE(G)",
      ALLOCATED_SPACE/1024/1024/1024  as "ALLOCATED_SPACE (G)",
      FREE_SPACE/1024/1024/1024      as "FREE_SPACE(G)"
 from DBA_TEMP_FREE_SPACE;

新创建的数据文件是以“稀疏文件”的方式，虽然创建成功，但是在文件系统中不是写入完全，空间占据是没有分配的状态；
新创建文件情况下：ALLOCATED_SPACE最开始表示元数据信息；
新创建文件情况下：FREE_SPACE表示没有分配的空间；

我们得到结论：当使用Temp空间的时候，需要从文件系统中请求空间使用。ALLOCATED_SPACE表示正在使用的空间对象，而FREE_SPACE表示没有分配给稀疏文件的空间。

经过上面的分析，我们可以清晰地看到dba_free_temp_space的字段含义和Temp情况。

ALLOCATED_SPACE表示文件系统中给临时表空间稀疏文件真实分配的大小，也就是某个时候系统使用这个临时表空间最大的位置（类似于HWM）。从组成上，这个大小三部分组成：元数据信息（1M）、正在使用的临时段空间、当前没有使用但是曾经使用过的临时段空间。
 
FREE_SPACE表示的维度是从实用角度入手，表示当前表空间有多大空间可以使用。包括：当前没有使用但是曾经使用过的临时段空间、稀疏文件中未分配部分。

两个字段体积中有一部分属于共享，是当前没有使用但是曾经使用过的临时段空间部分。

--oracle 11g 使用命令释放不使用的临时表空间（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
--语法如下：
--ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];
--ALTER TABLESPACE tablespace SHRINK SPACE [KEEP size];

alter tablespace temp1 shrink space keep 10G;
alter tablespace temp2 shrink space keep 10G;
alter tablespace temp3 shrink space keep 10G;

set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1;
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1; 

alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp24.dbf' keep 1G;

SQL> alter tablespace temp1 shrink space keep 10G;

Tablespace altered.

SQL> alter tablespace temp2 shrink space keep 10G;

Tablespace altered.

SQL> set line 200
select name,state,type,total_mb/1024,free_mb/1024,usable_file_mb/1024,offline_disks from v$asm_diskgroup;

SQL> col name for a60
SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;

alter tablespace TEMP2 add tempfile '+DATADG/prod/datafile/temp26.dbf' size 10m autoextend on maxsize unlimited;

alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp21.dbf' keep 1G;
alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp22.dbf' keep 1G;
alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp23.dbf' keep 1G;
alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp24.dbf' keep 1G;
alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp25.dbf' keep 1G;
alter tablespace temp2 shrink tempfile '+DATADG/prod/datafile/temp26.dbf' keep 1G;

SQL> alter tablespace temp1 shrink tempfile '+DATA/prod/tempfile/temp1.260.886527071' keep 10G;

Tablespace altered.

SQL> SELECT file#, name, bytes/1024/1024/1024 as "TEMPFILE(G)" FROM v$tempfile;

总结：
Oracle 11g的在线回收默认临时表空间功能很强大（仅locally managed temporary tablespaces可用：Doc ID 452697.1）
Oracle 11g以前回收默认临时表空间：(How to Shrink the Datafile of Temporary Tablespace (Doc ID 273276.1))
      1.建立新的小的默认临时表空间temp1
      2.alter tablespace命令使temp1成为默认临时表空间
      3.删除老的默认临时表空间temp

----------------------------------------------------临时表空间信息查找
--create temporary tablespace temp tempfile '/home/MIG/data/temp02.dbf' size 10m;
alter tablespace temp add tempfile '/home/MIG/data/temp02.dbf' size 10m;

set pages 999
set line 300
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1 ;

set pages 999
select 'alter database tempfile '||file_id||' resize 8G;' from dba_temp_files;

set pages 999
select 'alter database tempfile '||file_id||' autoextend on maxsize 30G;' from dba_temp_files;
----------------------------------------------------删除临时表空间的某个数据文件
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;

alter database default temporary tablespace TEMP2;

SQL> alter database tempfile '+DATA/prod/tempfile/temp1.766.868470553' offline;

Database altered.

SQL> alter database tempfile '+DATA/prod/tempfile/temp1.766.868470553' drop including datafiles;
alter database tempfile '+DATA/prod/tempfile/temp1.766.868470553' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time


SQL> alter database tempfile '+DATA/prod/tempfile/temp1.766.868470553' online;

Database altered.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> 

ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
找出占有的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 and tablespace='TEMP1';

select sql_text  from v$sqltext 
where sql_id in
(
select sql_id from  v$tempseg_usage);
看到到底还有那些SQL语句在运行

ERP生产环境重建临时表空间TEMP1
create temporary tablespace temp1 tempfile
     SIZE 100M AUTOEXTEND ON NEXT 8192  MAXSIZE 30720M;
ALTER TABLESPACE TEMP1 ADD TEMPFILE
     SIZE 100M AUTOEXTEND ON NEXT 104857600  MAXSIZE 30720M;

----------------------------------------------------删除临时表空间
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/erpdb/EBSSIT/db/data/temp2.295.765923357' SIZE 100M REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 10240M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

SQL> shutdown immediate    
SQL> startup
SQL> drop TABLESPACE TEMP1 including contents and datafiles;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/erpdb/EBSSIT/db/data/temp1.260.765923355' SIZE 100M REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 10240M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

SQL> shutdown immediate
SQL> startup