已索引

临时表空间的作用

临时表空间是用来存放临时段的，大体说来，有两大作用：1、排序；2、存放临时表

如下这些操作会用到临时表空间：

• Order by
• Group by (10.2之前一定会排序，10.2之后不一定会排序）
• Distinct
• index (Create 或 rebuild，注意 impdp 也会使用临时表空间)
• 集合运算Minus、intersect、Union (注意，union all不会排序）
• Sort-Merge joins
• Analyze 操作
• 异常导致的temp暴涨

临时段

临时段并不是只会出现在临时表空间中，在索引表空间和数据表空间中也可能出现临时段。

NOTE:
A "temp segment" is not necessarily a SORT segment in a temporary tablespace.
It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.
eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data. This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation. It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend it report ORA-1652 rather than an INDEX related space error.

A TEMPORARY segment may be from:

排序

当用户会话对数据进行排序时，排序是在PGA中进行的，但PGA中用来排序的空间是有限的。如果排序的数据过多，PGA处理不了时，Oracle会把要排序的数据分成多份，每次只取一份放在PGA中进行排序，其他的部分都放到临时表空间中，当PGA里的部分排序完成后，把排序好的部分交换到临时表空间中，同时再从临时表空间里取一份没有排序的数据到PGA中进行排序，这样直到所有数据排序完成为止。

排序使用临时表空间的演示：
将临时表空间对应的文件删除后，通过 dba_objects 构建一个大表，执行如下语句：

select * from big_table order by 1,3,2,4,7,6,8

排序用到临时表空间时将触发报错，说找不到临时表空间文件。

排序结束（或失败）后，临时表空间将会被自动释放。
将占用临时表空间的会话结束也会回收会话相关的资源，包括其占用的临时表空间。

临时表

临时表的特性

• 相同用户的不同session都能使用临时表，但是每个session的数据在session级别是隔离的，互不影响，当前session在表中操作的数据其它session是看不到的。Data in a temporary table is private to the session, which means that each session can only see and modify its own data.
• 基于事务的临时段在事务结束后收回临时段，基于会话的临时段在会话结束后收回临时段。
• 没有DML锁，没有约束，会产生少量redo，节省资源，访问数据快。
• 如果不指定临时表存放的临时表空间，则默认存放在当前用户的默认临时表空间中。
• 在没有会话使用临时表的前提下，可以删除临时表，直接删除就行，不用purge，它不进垃圾站。

建表语法

create global temporary table cyt(id int) 
[on commit delete rows] [on commit preserve rows];

两种模式（不论哪种模式，会话始终是隔离的，只是数据的保留方式有区别而已）：
1）基于事务的临时段：在事务提交时，就会自动删除记录，on commit delete rows。
2）基于会话的临时段：当用户退出session时，才会自动删除记录，on commit preserve rows。

create global temporary是语法就这样，如果没有global关键字，报错：
ERROR at line 1:
ORA-14459: missing GLOBAL keyword

临时表与索引、视图和触发器

临时表上可以建索引、视图和触发器（但基本不这么用，也不建议这么用）。
Indexes created on temporary tables are also temporary. 类似临时表，同样是会话隔离的。
不论是基于会话的临时表，还是基于事务的临时表，索引一旦创建，定义就永久保留下来了。

对于基于事务的临时表，不论临时表是否正在被使用，不论是正在使用临时表的会话还是没有使用临时表的会话，都可以成功创建索引。
对于基于会话的临时表，只有某会话还未使用过临时表时，才能创建索引，否则会报如下错误：
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

例如，会话1对基于会话的临时表做了DML操作，则会话1不能创建索引，但是新的会话2可以创建索引。
索引创建后，会话1可以执行 commit 命令，但是执行 delete tablename 命令则触发了报错：
ORA-00600: internal error code, arguments: [kcbgcur_1], [], [], [], [], [], [],
[], [], [], [], []

会话1和会话2均不能删除该索引，只有当会话1退出后，才能删除索引，否则报错如下：
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

临时表的应用场景

对于中间处理或数据暂存的表，使用临时表就一个非常不错的方案，没有DML锁，没有约束，会产生少量redo，数据自动清理。

生产中经常会遇到将一个普通表当临时表用，这是不推荐的，下面是一个生产案例。

默认临时表空间

在创建用户的时候可以指定用户的默认临时表空间，如果没有为用户指定默认临时表空间，则为系统默认的临时表空间。

查询系统默认的临时表空间：

SQL> set lines 200;
SQL> col PROPERTY_VALUE for a40;
SQL> select property_name, property_value 
from database_properties
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP

修改系统默认的临时表空间：

SQL> alter database default temporary tablespace cytemp;

可以建立多个临时表空间，为不同的用户指定不同的临时表空间，但默认的临时表空间只能有一个。
如果未指定默认的临时表空间，Oracle将会使用system作为临时表空间（非本地管理）。
默认的临时表空间不能offline和drop。但temporary file可以offline，可以把一个临时表空间中的所有临时文件都offline。

创建临时表空间

SQL> create temporary tablespace mytemp tempfile '/u01/app/oracle/oradata/demodb/mytemp01.dbf' size 1g autoextend on maxsize 30g;

临时表空间extent的分配只能使用 uniform (extent management local uniform)，不能使用autoallocate。uniform 表示分配 extent 时，每个 extent 的大小是固定的（默认1M）。不指定会自动使用uniform。

"Doc ID 19047.1"中有这样一句话：
It is worth making sure the TEMP tablespace PCTINCREASE is 0 and that it has a sensible (large) storage clause to prevent fragmentation. For TEMPORARY temp tablespaces make sure both INITIAL and NEXT are set to large values as extent sizes are taken from the NEXT clause and not the INITIAL clause.

在Oracle官方文档："SQL Language Reference" > "CREATE TABLESPACE" > "storage_clause" 中，有写这样一段话：
"NEXT" specify in bytes the size of the next extent to be allocated to the object.
In locally managed tablespaces, any user-supplied value for NEXT is ignored and the size of NEXT is determined by Oracle if the tablespace is set for autoallocate extent management. In UNIFORM tablespaces, the size of NEXT is the uniform extent size specified at tablespace creation time.

也就是说，当 autoallocate 时，next 值由 Oracle 自己决定，当 uniform 时，next 值由 uniform 值决定。所以对于临时文件，建议将 uniform 值适当提高。

预置的或者新建的临时表空间都是nologing的。

修改用户的临时表空间

可以通过dba_users视图查看用户的临时表空间。

修改用户的临时表空间：

SQL> create user xuw identified by xuw temporary tablespace cytemp;

注意：

• Undo表空间是通过初始化参数指定，所以一个实例只能使用一个undo表空间，临时表空间则不同，不同的用户可以使用不同的或者相同的临时表空间。
• 与default profile不同，删除了用户的默认临时表空间后，用户的临时表空间不会转回到默认的temp临时表空间，通过 dba_users 视图查看用户的默认临时表空间时仍然是之前的临时表空间。

增加、删除、修改临时文件

查看临时文件：
dba_temp_files或者v$tempfile

增加临时文件：

SQL> alter tablespace mytemp add tempfile '/oradata/ocp/mytemp02.dbf' size 1g autoextend on maxsize 30g [reuse]; 

给临时表空间增加临时文件时，在dba_temp_files中不能存在要添加的文件。
另外，增加临时文件时，建议把 uniform 值适当增大。

删除临时文件：

SQL> alter tablespace mytemp drop tempfile '/oradata/ocp/mytemp02.dbf';

修改临时文件大小：

SQL> alter database tempfile '/oradata/ocp/temp01.dbf' resize 200m;

临时表空间组

临时表空间组是一组由临时表空间组成的组，临时表空间组和临时表空间不能同名。临时表空间组不能显式地创建和删除。当把第一个临时表空间分配给某个临时表空间组时，会自动创建这个临时表空间组，将临时表空间组的最后一个临时表空间移除时，会自动删除临时表空间组。临时表空间组是一个逻辑上的概念，10.2 之后提出的概念。

同一用户将产生多个session，但是这些session都使用一个用户登录数据库，这就容易产生临时表空间争用，造成临时表空间不足。使用临时表空间组可以提高单个用户多个会话使用表空间的效率。

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate(缓和) problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

创建临时表空间组：
通过 create 或者 alter 把临时表空间分配给某个临时表空间组时，会自动创建这个临时表空间组。

SQL> create temporary tablespace temp2 tempfile '/oradata/ocp/temp2a.dbf' size 50m tablespace group temp_grp;

查看临时表空间组的相关信息：

SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ----------------------------------------
TEMP_GRP                       TEMP1

使用临时表空间组：
使用一个临时表空间组时，当成临时表空间来用就好了。
将用户的临时表空间改为临时表空间组：

SQL> alter user hr temporary tablespace temp_grp;

这样，hr用户的所有会话都可以使用多个临时表空间了。如果这个组被隐式删除了，hr用户的临时表空间将变为系统默认的临时表空间（系统有一定的延迟，要等一段时间后才能看到用户的临时表空间）

也可以将数据库的默认临时表空间修改为临时表空间组：

SQL> alter database default temporary tablespace temp_grp;

把某个临时表空间从临时表空间组中移出去：

SQL> alter tablespace temp2 tablespace group '';

要移除表空间组时，该组不能是缺省的临时表空间。

ORA-1652: unable to extend temp segment by %s in tablespace %s (Doc ID 1267351.1)

临时段不能扩展的问题，可能发生在临时表空间，也可能发生在永久表空间。
作为紧急的处理手段，可以通过增加临时文件或者数据文件的方式来解决。
但是，如果不找出产生问题的 SQL 语句加以优化，加了文件以后，很可能空间会很快再次用光。

对于永久表空间，当发生该报错时，去查表空间的使用率，发现使用率并不高，这可能有两个原因：
1.报错发生后，临时段被回收，表空间使用被释放。针对此类问题，做好临时段的使用监控可以快速找出相关的SQL语句。
2.语句的表空间需求量远远小于表空间的剩余量，但仍然报此错误，则为碎片导致。下面是一个生产案例。

找出消耗临时段的元凶

查询当前临时段的使用情况依据的视图是 V$TEMPSEG_USAGE 与 V$SORT_SEGMENT。
查询历史临时段的使用情况依据的视图是 V$ACTIVE_SESSION_HISTORY 或 DBA_HIST_ACTIVE_SESS_HISTORY。

V$TEMPSEG_USAGE describes temporary segment usage. V$TEMPSEG_USAGE 与 V$SORT_USAGE 的字段是一样的，查询出来的结果也是一样的。
V$SORT_SEGMENT displays information about every sort segment in a given instance. It is only updated when the tablespace is of the TEMPORARY type.

   INST_ID TABLESPACE_NAME      CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------- -------------------- ------------- ------------ ----------- -----------
         1 TEMP                            34      5500672        4352     5496320

当前的情况

set lines 300;
set pages 50;
col username for a10;
col schemaname for a10;
col tablespace for a10;
col osuser for a10;
col terminal for a10;
col sql_text for a40;
col program for a20;
select     s.sid,
        s.serial#,
        s.username,
        sql.sql_id,
        t.blocks*8/1024/1024 as "SIZE(G)",
        t.tablespace,
        t.segtype,
        t.extents,
        s.program,
        s.osuser,
        s.terminal,
        sql.sql_text
from v$sort_usage t, v$session s, v$sql sql
where t.session_addr = s.saddr
and t.sqladdr = sql.address
and t.sqlhash = sql.hash_value
order by "SIZE(G)";

如果连接查询不出结果，也可以直接查询 v$sort_usage 视图找到 SQL_ID，再由 SQL_ID 去定位 SQL_TEXT。

select * from
(select inst_id,session_addr,username,sql_id,contents,segtype,blocks*8/1024 MB
from gv$sort_usage order by blocks desc) 
where rownum<=100;

如果有多条相同SQL使用临时段的记录，按SQL_ID求知更直观：

select inst_id,username,sql_id,segtype,count(*) sess_count,sum(blocks)*8/1024/1024 GB   
from gv$sort_usage group by inst_id,username,sql_id,segtype order by GB;

展开讨论一下v$sort_usage中的SEGTYPE列的不同的值各有什么意义：
SORT：SQL排序使用的临时段。
DATA：临时表(Global Temporary Table)存储数据使有的段。
INDEX：临时表上建的索引使用的段。
HASH：hash算法，如hash连接所使用的临时段。
LOB_DATA和LOB_INDEX：临时LOB使用的临时段。

如果会话还没有释放临时段，就可以抓到相关的会话和SQL，但是，如果查询时，会话已经释放，则抓不到。
如果这个会话抓不到，我们就支查询 V$ACTIVE_SESSION_HISTORY 或 DBA_HIST_ACTIVE_SESS_HISTORY。

下面是为什么可能抓不到的具体分析：

EXAMPLE 1: 临时表空间

假设我们使用的临时表空间为 TEMP，大小为 50gb (a recommended minimum for 11g)。

TIME 1 : Session 1 starts a long running query
TIME 2 : Session 2 starts a query and at this point in time Session 1 has consumed 48gb of TEMP's free space
TIME 3 : Session 1 and Session 2 receive an ORA-1652 because the tablespace has exhausted of of its free space

Both sessions fail .. and all temp space used by the sessions are freed (the segments used are marked FREE for reuse)

TIME 4 : SMON cleans up the temporary segments used by Session 1 and Session 2 (deallocates the storage)
TIME 5 : Queries are run against the views V$SORT_USAGE or V$TEMPSEG_USAGE and V$SORT_SEGMENT ... and it is found that no space is being used (this is normal)

EXAMPLE 2: 索引表空间

Permanent tablespace INDEX_TBS is being used and has 20gb of space free.

TIME 1 : Session 1 begins a CREATE INDEX command with the index stored in INDEX_TBS
TIME 2 : Session 1 exhausts all of the free space in INDEX_TBS as a result the CREATE INDEX abends
TIME 3 : SMON cleans up the temporary segments that were used to attempt to create the index
TIME 4 : Queries are run against the views V$SORT_USAGE or V$TEMPSEG_USAGE ... and it is found that the INDEX_TBS has no space used (this is normal)

历史的情况(过去的情况)

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col sample_time for a40;
col event for a40;
col p1text for a20;
col p2text for a20;
col p3text for a20;
set lines 200;
select t.* from (
select INST_ID,SQL_ID,EVENT,TEMP_SPACE_ALLOCATED/1024/1024 as "TEMP_SPACE_ALLOCATED(M)"
from GV$ACTIVE_SESSION_HISTORY 
where SAMPLE_TIME > to_date('2024-02-20 16:00:00','yyyy-mm-dd hh24:mi:ss') 
and SAMPLE_TIME < to_date('2024-02-20 16:20:00','yyyy-mm-dd hh24:mi:ss') 
and event is not null
and TEMP_SPACE_ALLOCATED is not null
order by TEMP_SPACE_ALLOCATED DESC) t
where rownum < 11;

找出“可能”会大量消耗临时表空间的SQL

在平常工作中把这些SQL找出来，可以提前进行优化。

查询在PGA与临时表空间之间进行多次往返操作的SQL：

select sql_text, sum(onepass_executions) onepass_cnt, sum(multipasses_executions) mpass_cnt
from v$sql s, v$sql_workarea wa
where s.address = wa.address
group by sql_text
having sum(onepass_executions+multipasses_executions)>0;

说明：
optimal: SQL 语句能够完全在所分配的 SQL 工作区内完成所有的操作。这时的性能最佳。
onepass: SQL 语句需要与磁盘上的临时表空间交互一次才能够在所分配的 SQL 工作区中完成所有的操作。这个时候的性能较差。
multipass: 由于 SQL 工作区过小,从而导致 SQL 语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。

通常，磁盘排序的SQL，它的逻辑读/物理读/排序/执行时间等都是比较大的，所以我们通过v$sqlarea或v$sql将这些SQL找出来进行优化。

set lines 200;
col sql_text for a80;
select 
disk_reads/executions as DISK_READS, 
elapsed_time/1000000/executions as "ELAPSD_TIME(s)",
buffer_gets/executions bgets_per,
executions,
first_load_time as FIRST_TIME,
sql_text
from v$sql
where executions > 0 and 
(disk_reads/executions > 500 or buffer_gets/executions > 20000) and 
command_type = 3
order by DISK_READS,"ELAPSD_TIME(s)";

Command_type为3代表select。

1652 trace 追踪临时表空间消耗最大 SQL 的局限性

EXAMPLE

   Suppose we have a 90gb temporary tablespace (only one temp tablespace for this example)
    We also have enabled tracing for error 1652 using ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';

       At time 1 ... a long ... complicated query starts running
         after several hours ... it has consumed 85gb of space ... and will continue running for a long time

       At time 2 (time 1 + several hours) another query runs and quickly consumes 5gb of space ... and generates an ORA-1652
          this session then crashes and releases the 5gb of space ... This will not cause the query started at time 1 .. or any other query to fail
          unless they also needed MORE temp space (ie a new extent) .. at exactly the same moment

          At this point .. the errorstack for 1652 will show the query started at time 2 ... not the BIG temp space consumer ... started at time 1

       At time 3 ... This note is used to setup the monitoring job to watch the temp segment usage over time ...
            the first update should show our query that has been running since time 1 ... which has consumed 85gbgb of space

           Now .. suppose while setting up the job ... the query from time 1 ... ends ... and frees up its 85gb of space ... then our job will not show
             the consumption ... as it is now freed

       At time 4 .. the same query that consumed the 85gb of space is run again

       At time 5 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 5gb of temp space

       At time 6 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 50gb of temp space

       At time 7 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 85gb of temp space

       At time 8 ... another query is started that quickly consumes the remaining 5gb of space ... and as a result .. an ORA-1652 occurs

            Again .. our errorstack trace will point to this query .. an not the query started at Time 4 ...

            The job that is running ... WILL show the session that consumed the 85gb of space

   Once the 'top consumers' of space have been determined .. those query can be investigated for possible tuning to use less space ... and if they cannot be tuned then more space needs to be added to the temporary tablespace that is running out of space.

在上面的例子中，A会话消耗了85G的空间，后面B会话消耗了5G的空间而触发了 1652 错误，EVENT 抓住了导致 1652 的会话B，但没有把真正的大户A给抓住。
那在上面的例子中，我们可以通过 V$SORT_USAGE、V$TEMPSEG_USAGE 和 V$SORT_SEGMENT 进行分析，而幸运的抓主元凶。
但反过来想，如果是B先消耗了5G空间，然后A消耗了85G的空间而触发了 1652 错误，我们又没有提前打开 EVENT，那此时，通过视图就只能抓到B了。

所以，最好的办法是对临时段的使用做持续的监控，并将监控结果记录下来，当需要分析问题时，通过监控记录就可以查到故障时段消耗量最大的SQL。
文档 Doc ID 364417.1 对此作了详细的说明，文档中的方法是把持续的监控记录放到数据库的表中，并定期清理监控记录。
为了减少对生产数据库的侵入，我们通过 zabbix 对其进行监控，监控结果自动保存在 zabbix 数据库中，故障时检查检查监控记录或者直接到zabbix数据库中查询。

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_id, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
-- WHERE b.tablespace = 'TEMP'
where a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*8/1024 > 500
ORDER BY b.tablespace, b.blocks;

其实，我们也可以不监控，通过 GV$ACTIVE_SESSION_HISTORY 来分析是谁使用了临时表空间。

回收临时表空间的方法

重启实例

如果可以，则重启实例。即使临时文件被删除，重启后也会自动重新生成全新的临时文件。

收缩临时表空间

SQL> alter tablespace temp shrink space [keep 20m];
如果没有中括号里的内容，则是收缩至最小。括号中表示ensuring a minimum size of 20M。
Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

也可以收缩单个临时文件：
ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
上面命令中没有指定keep，则缩至最小。

注意：永久表空间和undo表空间是不能收缩的。

重建tempfile

SQL>alter database tempfile '......' drop; 或
SQL> alter tablespace mytemp drop tempfile '/oradata/ocp/mytemp02.dbf';
SQL>alter tablespace temp add tempfile '......';

合并碎片

SQL>alter tablespace temp coalesce;

诊断事件（手工清理临时表空间）

SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4'
说明：temp表空间的TS#为3，所以TS#+1=4

ADG 切换后 TEMP 临时表空间暴涨的问题

该问题已经发生两次，数据库版本均为 oracle11.2.0.4.161018。
两次临时表空间使用率告警都是发生在数据库切换演练物理主机重启后。
查询后，发现是 DBSNMP 用户的一个 SQL 语句大量消耗临时表空间。
查询 alert 日志，有临时表空间不能扩展的报错。
这个 DBSNMP 是一个内置用户，OEM 使用该用户来采集信息。
因为 OEM 在我行部署以后基本没有使用，所以我使用了在数据库服务器上直接关闭 agent 的方式。

[oracle]$ ps aux | grep java 查看 emctl 的路径
[oracle]$ /opt/database/em13c/agent/agent_13.2.0.0.0/bin/emctl stop agent

通过ORA-1652和V$BACKUP_PIECE_DETAILS在MOS中有匹配文档：
"ORA-1652: Unable To Extend Temp Segment By 128 In Tablespace TEMP" Due to X$ Tables (Doc ID 2575123.1)
Queries from V$BACKUP_PIECE_DETAILS or V$RMAN_STATUS Return Error ORA-01652 (Doc ID 2119607.1)

如下文章记录了类似案例。

一次临时表空间大量占用问题的处理

《摘自http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html 》

一个电信运营商客户的核心交易系统，临时表空间大量被占用，临时表空间被撑到了600GB。这样的问题复杂吗？取决于很多因素，不过今天所要讲的案例，并不复杂，如果我们对临时表空间在何种情况下使用有足够了解。
首先，我们要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。正如我们要想知道这个月的花费过大，去分析原因时就要去看是哪些开销过大、开销了多少金额、开销的用途等。
这个步骤比较简单，查询v$sort_usage就可以了：

select * from
(select inst_id,username,sql_id,contents,segtype,blocks*8/1024 MB
from gv$sort_usage order by blocks desc) 
where rownum<=100;

USERNAME    SESSION_ADDR     SQL_ID        CONTENTS  SEGTYPE            GB  
----------  ---------------- ------------- --------- --------- -----------
XXXX        0700002949BCD8A0 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294BD99628 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294CD10480 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294DD1AC88 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294CD68D70 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294DBDF760 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294EDB5D10 291nk7db4bwdh TEMPORARY SORT      .9677734375  
XXXX        070000294FD7D818 291nk7db4bwdh TEMPORARY SORT      .9677734375  
...结果较多，忽略部分输出...

或者按SQL_ID进行求和：

select inst_id,username,sql_id,segtype,count(*) sess_count,sum(blocks)*8/1024/1024 GB   
from gv$sort_usage group by inst_id,username,sql_id,segtype order by GB;

SQL_ID都是一样的，那这个SQL是否有其特殊性呢？SEGTYPE为SORT表明这个临时段是“排序段”，用于SQL排序，大小居然也是一样，会话占用的临时段大小将近1GB，几百个会话加在一起，想不让临时表空间不撑大都难。
看看这个相同的SQL ID代表的SQL是什么：

SQL> @sqlbyid 291nk7db4bwdh 

SQL_FULLTEXT 
--------------------------------------------------------------------------------------------------------------  
 SELECT  A.LLEVEL,  A.LMODE  FROM TABLE_XXX A  WHERE A.SERVICE_NAME = :SERVICE_NAME AND STATE='Y'

很明显，这是一条非常简单的SQL，没有ORDER BY ，也没有GROUP BY、UNION、DISTINCT等需要排序的，TABLE_XXX是一张普通的表，而不是视图。出现了什么问题？会不会是v$sort_usage的SQL_ID列有错误？我们查看其中一个会话正在执行的SQL：

select sid,prev_sql_id, sql_id from v$session where saddr='070000294AC0D050'; 

SID         PREV_SQL_ID           SQL_ID 
-----------     -------------             -------------
3163     291nk7db4bwdh

看到会话当前没有执行任何SQL，v$sort_usage中的SQL_ID是该会话前一条执行的SQL。为什么这里显示的是会话前一条执行的SQL，这是oracle bug（Bug 17834663），但至少有一点是可以判断的：如果大量的临时段都是由会话当前正在执行的SQL所产生的，那说明同时有几百个会话在执行需要大量临时空间的SQL，那系统早就崩溃了。所以这些临时表空间的占用不应该是由当前在执行的SQL所产生的，至少大部分不是。

大部分人的一个错误观点是，临时表空间中当前占用的空间是由会话当前正在执行的SQL所产生的。上面的一个简单的分析判断，情况不应该是这样。我们可以基于查询类SQL的执行过程来分析：

解析SQL语句(Parse)，生成一个游标(Open Cursor)。
执行SQL语句(Execute)，严格说就是执行新产生的游标。
在游标中取数据(Fetch)。
关闭游标(Close Cursor)。

关键在第3步。大家都知道取数据有一个array size的概念，表示一次从游标中取多少条数据，这是一个循环的过程。如果SQL查询得到的数据有1000条，每次取100条，则需要取10次。对于Fetch Cursor，有两点：
一个游标，或者说一条SQL语句，并不要求客户端把所有数据取完，只取了一部分数据就关闭游标也是可以的。
只要还没有关闭游标，数据库就要维护该游标的状态，如果是排序的SQL，也需要维持该SQL已经排好序的数据。

很显然，从上述第2点可以知道，如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。
基于这样的分析，我们只需要随便选择一个占用了接近1GB的会话（见第1个查询），查询v$open_cursor，查看其打开的游标中是否有大数据量排序的SQL：

SQL> select sql_id,sorts,rows_processed/executions from v$sql  
  2  where parsing_schema_name='ACCT' and executions>0 and sorts>0  
  3  and sql_id in (select sql_id from v$open_cursor where sid=4505)  
  4  order by 3;  
    
  SQL_ID        SORTS         ROWS_PROCESSED/EXECUTIONS  
-------------         -----------         -------------------------
...省略部分输出结果...  
86vp997jbz7s6       63283                       593  
cfpdpb526ad43         592               35859.79899  
cfpdpb526ad43         188               55893.61702  
cfpdpb526ad43         443                     71000  

最后三个游标，实际上都是同一条SQL语句，排序的数据量最大，我们来看看这条SQL是什么：

@sqlbyid cfpdpb526ad43  

SQL_FULLTEXT  
---------------------------------------------------------------------------------------------------  
select ... from  c, b, a, d, e where ... order by d.billing_cycle_id desc,e.offer_name,a.acc_name

基于为客户保密的原因，SQL做了处理，能知道这条SQL的确是排了序就行，不过在SQL中看不出来的是，这条SQL没有任何实质性的能够过滤大量数据的条件。那么我们count(*)这条SQL语句看看：

COUNT(*)  
--------  
12122698

出来的结果居然有1200多万条数据，一个前台应用，不知道取1200多万条数据干嘛。但是从rows_processed/executions只有几万的结果来看，应用在取了几万条数据之后，由于某些原因（最大的可能就是不能再处理更多的数据），不再继续取数据，但是游标也一直没有关闭。

问题分析到这里，很明显确认的是，应用存在问题，也许是业务逻辑问题；也许是根据前台选择的条件拼接的SQL，但是没有任何条件时就查询了所有数据。接下来就是找来开发人员。

临时表空间的异常占用，一种缓步增长的，另一种情况：一下撑满的通常是一个极大数据量的排序或极大的索引的创建。缓步增长的情况，跟系统的内存被逐渐占用类似，存在“泄露”。比如排序的SQL游标没有关闭，比如本文的案例；比如会话级临时表产生了数据后一直没有清除；临时LOB对象没有清理或泄露。前两种比较好去分析处理，但是临时LOB的泄露问题就复杂很多。

Oracle中的LOB变量，类似于C语句中的指针，或者类似于JAVA代码中的数据库连接Connection，是需要释放的。上述有问题的代码，缺少了释放LOB的代码：dbms_log.freetemporary(v_lob)。好在对于这种情况，Oracle提供了一个补救措施，就是设置60025事件可以自动清理掉不活动的LOB，只需要在参数文件中加上event='60025 trace name context forever'。
在Oracle数据库中，xmltype类型内部也实际上是LOB类型，xmltype类型的数据操作可能会产生较多的LOB临时段。lob类型的字段上的更改操作，比如lob拼接等，同样会产生LOB临时段。如果在v$sort_usage中发现大量的LOB类型的临时段，那么通常是由于代码存在问题，没有释放LOB，或者是由于Oracle本身的BUG。在MOS上，如果以lob temporary关键字搜索，会发现相当多的关于lob临时段的泄露或临时段没有释放相关的文档。
最后，不管是什么情况导致的临时表空间被过多占用，通常重启应用能够释放掉临时段，因为会话退出后，相对应的临时段就会被释放。看来，“重启”大法在这种情况下就很有用。