DB出现了如下问题
ORA-01114: IO error writing block to file 255 (block # 1047029)
ORA-27063: skgfospo: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
FILE 255不存在
查ORACLE的错误解释如下
orasbp@svodbp01:$ oerr ora 27063
27063, 00000, "skgfospo: number of bytes read/written is incorrect"
// *Cause: the number of bytes read/written as returned by aiowait
// does not match the original number, additional information
// indicates both these numbers
// *Action: check errno
orasbp@svodbp01:$ oerr ora 01114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause: The device on which the file resides is probably offline. If the
// file is a temporary file, then it is also possible that the device
// has run out of space. This could happen because disk space of
// temporary files is not necessarily allocated at file creation time.
// *Action: Restore access to the device or remove unnecessary files to free
// up space.
当时TEMPORARY是16G，AUTOEXTEND ON
查找原因,首先看是否真正用到这么多TEMPORARY空间
v$sort_usage将会告诉我们是谁在做什么
1* select username,session_addr,sqladdr,sqlhash from v$sort_usage
SQL> /
no rows selected
无人使用,再看v$sort_segment可以看temp的比较详细的使用情况,证实下
SQL> select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS
-------------------------------------------------------------- -------------
TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------ ----------- -----------
TEMP 0
1059584 0 1059584
我的DB_BLOCK_SIZE 是16K，所以TEMP总共分配了16G，看到TEMPORARY SEGMENTS全部FREE，TEMPORARY SEGMENTS就像ROLLBACK一样，虽然占用了16G的空间，但是并没有全部使用，然而系统却提示了空间已满。
也可以多表连接来找出用TEMPORARY 的SQL和SESSION信息
SQL> select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;
no rows selected
再看其他相关信息
SQL> show parameter polic
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
workarea_size_policy string
AUTO
SQL> show parameter pga
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
pga_aggregate_target big integer
1572864000
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------------------------------------ ----------
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
-------------- ----------- ----------- ----------- ------------ ----------
STATUS CONTENTS LOGGING FORCE_
------------------ ------------------ ------------------ ------
EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGE SEGMENT_SPAC DEF_TAB_COMPRESS
-------------------- ------------------ ------ ------------ ----------------
SYSTEM 16384
32768 16384 1 1017 50 0
ONLINE PERMANENT LOGGING NO
DICTIONARY USER NO MANUAL DISABLED
UNDO01 16384
65536 1 2147483645 65536
ONLINE UNDO LOGGING NO
LOCAL SYSTEM NO MANUAL DISABLED
TEMP 16384
4194304 4194304 1 0 4194304
ONLINE TEMPORARY NOLOGGING NO
LOCAL UNIFORM NO MANUAL DISABLED
DATA01 16384
262144 262144 1 2147483645 0 262144
ONLINE PERMANENT LOGGING NO
LOCAL UNIFORM NO AUTO DISABLED
可以得出结论, 并不是由于真正TEMPORARY 空间不足或有大应用在消耗TEMPORARY , 而是在非常空闲的情况下产生的问题.
TEMPORARY 的问题要从4个方面来考虑解决
1，
考虑SQL和INDEX的合理性，如果SQL或INDEX设计的不好会导致大量使用TEMPORARY SEGMENT。
2，
coalescing the temp tablespace
3，
increasing the sort_area_size parameter
4，
increasing the temp tablespace or recreate temp tablespace
针对以上解决方向的分析
v
第一个解决方向需要找到消耗TEMPORARY大的SQL来针对解决,但显然, 本次遇到的问题是在TEMPORARY非常空闲的情况下发生的, 不适合次方法.
v
第二个解决方向, 需要设置
1, SQL>alter tablespace temp increase 1;
等SMON 自动coalesce后再
SQL>alter tablespace temp increase 0
SQL>alter tablespace temp increase 0
2, SQL>alter tablespace temp coalesce;
需要考虑SORT AREA SIZE和PGA的设置,来减少TEMPORARY 的使用,
SQL> alter tablespace temp coalesce;
alter tablespace temp coalesce
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
因为是LOCAL MANAGEMENT所以导致coalesce语法无效。
3, 可以把TEMP先改成PERMANENT然后再使其变成TEMPORARY。因为PERMANENT会由SMON来回收空间。 但是这么做得时候系统会变得非常慢并运行一段时间，所以是不建议使用的。PERMANENT TABLESPACE会比TEMPORARY TABLESPACE更好地自动释放空间，但是速度会比TEMPORARY慢许多。
TEMPORARY TABLESPACE也是由EXTENTS组成，但是为了避免ALLOCATE和FREE的消耗，ORACLE会一次性完成分配，所以，ORACLE不会DEALLOCATE TEMPORARY TABLESPACE
但是TEMPORARY转变为PERMANENT与版本相关，只有create tablespace temp .... temporary才可以被ALTER为PERMANENT，而create TEMPORARY TABLESPACE不行。(见dba_tablespaces中的CONTENTS字段)
这些方法对LOCALLY MANAGEMENT无效，但是我还是建议TEMPORARY是LOCALLY MANAGED并且UNIFORM SIZE。LMT在重用空间方面效率很高而且基本无碎片。
各版本语句的比较
<Oracle 7.3
CREATE TABLESPACE temp DATAFILE ...; -
Oracle 7.3 & 8.0
CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
>Oracle 8i and above
CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;
因为本系统是
ORACLE 9,,
所以也不能采用这种方式
.
v
第三个解决方向是increasing the sort_area_size parameter, 本次遇到的问题是在TEMPORARY非常空闲的情况下发生的, 不适合次方法.
v
第四个解决方向increasing the temp tablespace or recreate temp tablespace
如
SQL>alter tablespace temp tempfile '......' offline drop;
SQL>alter tablespace temp add tempfile '......';
SQL>alter tablespace temp add tempfile '......';
试试改小如何?
SQL> alter database tempfile '/oradata/SBP/sandata2/TEMP.DBF' resize 10000M
2 /
alter database tempfile '/oradata/SBP/sandata2/TEMP.DBF' resize 10000M
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
当这四个方向都解决不了问题的时候, 我想当AUTOEXTEND ON的时候ORACLE宁愿新分配EXTEND也不去使用旧的EXTEND。因为新分配EXTEND效率更高，但是这样也导致EXTEND的时候没有边界随意扩展知道OS空间满了为止，所以，TEMPORARY和UNDO TABLESPACE最好设置一个比较大的值并且取消AUTOEXTEND。而现在TEMPORARY 16G我认为是比较符合本数据库的一个大小 , 所以设置AUTOEXTEND OFF并同时增加磁盘空间为20G (并不是给TEMPORARY使用). 此时再询问开发人员, 问题已不再发生.
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME
BYTES BLOCKS STATUS RELATIVE_FNO AUTOEX MAXBYTES
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------- ------------ ---------- -----------
/oradata/SBP/sandata2/TEMP.DBF 1 TEMP
1.7364E+10 1059840 AVAILABLE 1 NO 0
0 0 1.7360E+10 1059584
总结: .
当登陆数据库后执行一个排序操作，在cursor open的时候就拥有了temporary extents，当close cursor的时候此temporary就会标识为可重用，如果永远没有CLOSE则次extents被永远处于使用状态。
TEMPORARY SEGMENTS
就像
ROLLBACK
一样，虽然占用了
16G
的空间，但是并没有全部使用，然而系统却提示了空间已满。
TEMPORARY TABLESPACE
也是由
EXTENTS
组成，但是为了避免
ALLOCATE
和
FREE
的消耗，
ORACLE
会一次性完成分配，这样做可以避免
RECURSIVE SQL
管理
TEMP SEGMENTS
的消耗，这也是为什么
TEMPORARY
比
PERMANENT
建立的时候与使用的时候都更快的原因。在取得性能的优势的同时也失去了管理方面的特性比如
COALESCE
与
REUSE
。
当
AUTOEXTEND ON
的时候
ORACLE
宁愿新分配
EXTEND
也不去使用旧的
EXTEND
。因为新分配
EXTEND
效率更高，但是这样也导致
EXTEND
的时候没有边界随意扩展知道
OS
空间满了为止。
所以建议
TEMPORARY
设置成
UNIFORM SIZE
，
LMT
，
AUTOEXTEN OFF
，并要分配足够的空间比如
10G
以上。
请参考另一篇文章
TEMPORARY
表空间和文件