Oracle的临时文件过大一般是以下原因造成的

1、ORDER BY or GROUP BY

2、索引创建或重建

3、union & intersect & minus sort-merge joins;

4、distinct操作

查看是哪些SQL造成临时表空间文件占用过大

SELECT vt.inst_id,

         vs.sid,

         vs.serial#,

         vs.machine,

         vs.saddr,

         vs.program,

         vs.module,

         vs.logon_time,

         vt.tempseg_usage,

         vt.segtype

    FROM gv$session vs,

         (  SELECT inst_id,

                   username,

                   session_addr,

                   segtype,

                   ROUND (SUM (blocks) * 8192 / 1024 / 1024 / 1024, 2)

                      tempseg_usage

              FROM gv$tempseg_usage

          GROUP BY inst_id,

                   username,

                   session_addr,

                   segtype

          ORDER BY 4 DESC) vt

   WHERE vs.inst_id = vt.inst_id AND vs.saddr = vt.session_addr

ORDER BY tempseg_usage DESC;