临时表空间无法扩展32G，操作系统限制？数据库限制？

今天有同事反映，一条语句运行了N长时间后异常终止了。经排查，原因是由于SQL书写得不够优化，导致耗尽了所有。
这个SQL执行故障最终是通过优化SQL得到有效处理。

不过通过这个案例引伸出了一个问题，临时表空间数据文件已经被设置为autoextend on，那为什么临时表空间扩展还有呢，是的限制，还是本身的限制呢？

整理一下思路，给出最终的原因。

1.临时表空间无法扩展的错误提示
警告日志中记录了下面的信息
ORA-1652: unable to extend temp segment by 128 in tablespace     TEMP

2.确认临时表空间属性
sys@ora10g> col tablespace_name for a15
sys@ora10g> col file_name for a30
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes/1024/1024/1024 GB,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                         AUT         GB MAXBLOCKS
--------------- --------------------------------- --- ---------- ---------
TEMP            /oracle/oradata/ora10g/temp01.dbf YES 31.9999847   4194302

此时临时表空间TEMP具有自动扩展属性，此处的MMAXBYTES显示，该临时表空间最大可以使用到32G！这是为什么呢？

3.第一种推测：操作系统的限制
此推测被否定。
经确认，Linux操作系统对file大小没有大小限制。
ora10g@secDB /home/oracle$ ulimit -a
core file size          (blocks, -c) 0
seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 266239
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

4.第二种推测：数据库的限制
推测正确。
当数据库的db_block_size被设置为8K时，数据文件最大可扩大到32G（比32GB小一点点）。
sys@ora10g> show parameter db_block_size

NAME               TYPE                 VALUE
------------------ -------------------- ----------
db_block_size      integer              8192

这个数据库的限制的根源与ROWID有关，在的Rowid中，会使用22位代表Block号，因此，22位最多只能代表2^22(4194304)个数据块。
有关ROWID的定义可以参考Oracle官方文档有关“ROWID Pseudocolumn”的描述。


我们来做一个简单的运算
sys@ora10g> col x for 99999999999
sys@ora10g> col y for 99999999999
sys@ora10g> select (power(2,22)-2)*8*1024 x,power(2,22)-2 y from dual;

           X            Y
------------ ------------
 34359721984      4194302

计算出来的结果与下面的MAXBYTES和MAXBLOCKS一致。
sys@ora10g> col maxbytes for 9999999999999999
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                         AUT    MAXBYTES MAXBLOCKS
--------------- --------------------------------- --- ----------- ---------
TEMP            /oracle/oradata/ora10g/temp01.dbf YES 34359721984   4194302

5.增加临时文件扩大突破临时表空间的32G限制
如果想要在现有基础上使用超过32G的临时表空间，最简单的方法就是给临时表空间再添加一个或几个临时文件。
1）简单添加一个临时文件
sys@ora10g> alter tablespace TEMP add tempfile '/oracle/oradata/ora10g/temp02.dbf' size 10m;

Tablespace altered.

2）观察一下最大值
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                         AUT    MAXBYTES MAXBLOCKS
--------------- --------------------------------- --- ----------- ---------
TEMP            /oracle/oradata/ora10g/temp02.dbf NO            0         0
TEMP            /oracle/oradata/ora10g/temp01.dbf YES 34359721984   4194302

3）调整为自动扩展
sys@ora10g> alter database tempfile '/oracle/oradata/ora10g/temp02.dbf' autoextend on;

Database altered.

4）此时临时表空间便看可以使用近64G
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                         AUT    MAXBYTES MAXBLOCKS
--------------- --------------------------------- --- ----------- ---------
TEMP            /oracle/oradata/ora10g/temp02.dbf YES 34359721984   4194302
TEMP            /oracle/oradata/ora10g/temp01.dbf YES 34359721984   4194302

6.创建大文件临时表空间突破临时表空间的32G限制
1）创建大文件临时表空间
sys@ora10g> create bigfile temporary tablespace temp_big tempfile '/oracle/oradata/ora10g/temp_big01.dbf' size 10m;

Tablespace created.

2）注意观察TEMP_BIG
sys@ora10g> col file_name for a34
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                             AUT    MAXBYTES  MAXBLOCKS
--------------- ------------------------------------- --- ----------- ----------
TEMP            /oracle/oradata/ora10g/temp02.dbf     YES 34359721984    4194302
TEMP            /oracle/oradata/ora10g/temp01.dbf     YES 34359721984    4194302
TEMP_BIG        /oracle/oradata/ora10g/temp_big01.dbf NO            0          0

3）调整大文件临时表空间TEMP_BIG为自动扩展
sys@ora10g> alter database tempfile '/oracle/oradata/ora10g/temp_big01.dbf' autoextend on;

Database altered.

4）再次观察最大可用大小
sys@ora10g> select tablespace_name,file_name,AUTOEXTENSIBLE,maxbytes,maxblocks from dba_temp_files;

TABLESPACE_NAME FILE_NAME                             AUT       MAXBYTES  MAXBLOCKS
--------------- ------------------------------------- --- -------------- ----------
TEMP            /oracle/oradata/ora10g/temp02.dbf     YES    34359721984    4194302
TEMP            /oracle/oradata/ora10g/temp01.dbf     YES    34359721984    4194302
TEMP_BIG        /oracle/oradata/ora10g/temp_big01.dbf YES 35184372064256 4294967293

7.小结
实际生产环境不可将任何文件设置为自动扩展，不可出现无人关注的死角。针对文章的实际情况可以通过添加临时文件的方式来解决。
细节之处，无限精彩。

Good luck.


10.01.29

-- The End --