【错误】ORA-1652

ORA-1652是oracle常见错误之一，该错误主要是由于临时表空间不足导致。

1.ORACLE数据库中涉及到排序操作的一些行为：

  排序是非常消耗系统资源的操作，所以应该尽量避免或者减少.以下行为会涉及到排序操作：

  创建索引
  GROUP BY或者ORDER BY操作
  DISTINCT操作
  UNION,INTERSECT,MINUS操作
  Sort-Merge join操作(如果没有索引的情况下，等连接加上排序操作通常会采用该连接方式)
  ANALYZE操作
  CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, CREATE TABLE AS SELECT

2.查询临时表空间使用率

  临时表空间和回滚表空间一样，分配的extents都是可以重复使用的，他们的使用率查询不能和普通表空间一样查询dba_free_space等视图，对于临时表空间的使用率
  我们可以借助以下视图和方法进行查询：

  V$SORT_USAG
  V$SORT_SEGMENT
  V$TEMP_SPACE_HEADER
  V$TEMP_EXTENT_POOL

1）SQL> select 'the '||name||' temp tablespaces '||tablespace_name||' idle '||round(100-(s.tot_used_blocks/s.total_blocks)*100,3)||'% at '||to_char
                     (sysdate,'yyyymmddhh24miss')
2）SQL> col DatafileName for a30
        SQL> set lin 150
        SQL> Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
          2         round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2)  "Free MB" ,
          3         d.file_name "DatafileName",
          4         round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
          5         round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
          6         round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2)  "Free KB",
          7         round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",
          8         0 "Fragmentation Index"
          9  from   V$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, V$TEMP_EXTENT_POOL p
         10  where  f.tablespace_name(+) = d.tablespace_name
         11  and    f.file_id(+) = d.file_id
         12  and    p.file_id(+) = d.file_id;
3）SQL> COL SQL_TEXT FOR A30
        SQL> SELECT SESS.SID, SESS.SERIAL#, SEGTYPE, BLOCKS*8/1024 "MB" ,SESS.SQL_ID ,SQL_TEXT
          2  FROM V$SORT_USAGE SORT, V$SESSION SESS ,V$SQL SQL
          3  WHERE SORT.SESSION_ADDR = SESS.SADDR
          4  AND SQL.SQL_ID = SESS.SQL_ID
          5  ORDER BY BLOCKS DESC;
4）SQL> COL USENAME FOR A10
        SQL> COL OSUSER FOR A10
        SQL> COL TABLESPACE FOR A15
        SQL> COL SQL_TEXT FOR A30
        SQL> SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT
          2  FROM V$SESSION A, V$TEMPSEG_USAGE B, V$SQLAREA C
          3  WHERE A.SADDR = B.SESSION_ADDR
          4  AND C.ADDRESS= A.SQL_ADDRESS
          5  AND C.HASH_VALUE = A.SQL_HASH_VALUE
          6  ORDER BY B.TABLESPACE, B.BLOCKS;

3.  ORA-1652错误出现了，我们就需要找出到底是什么操作占用了大量的临时表空间，一般我们可以通过在第二部分中提供的查询方法去查询正在占用大量临时段的操作，但是
  事实上当我们发现警告日志中的ORA-1652错误的时候，排序操作已经完成并且释放了空间，这种情况下我们可以借助以下几种方法来查找相应的SQL：

1）通过以下两个个视图来捕捉(局限：如果对应SQL被ageout就查不到了)：
    V$SQL_WORKAREA
    V$SQL_PLAN_STATISTICS_ALL

SQL> SELECT SQL_ID,LAST_TEMPSEG_SIZE FROM V$SQL_WORKAREA WHERE SQL_ID='6r2k8sy8mtk25';

2)通过V$SQL相关几个视图来捕捉(局限：直接路径读写还包含排序之外的其他操作)：

SQL> SELECT SQL_ID,DIRECT_WRITES/DECODE(EXECUTIONS,0,1,EXECUTIONS) 
                  2  FROM V$SQL WHERE SQL_ID='6r2k8sy8mtk25';