今天有同事反映，一條SQL語句執行了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
data 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有關，在Oracle的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.

secooler
10.01.29

-- The End --

【限制】32G，作業系統限制？資料庫限制？

相關文章