ora-01114錯誤原因

昨天在做測試的時候發現一個非常奇怪的問題：在程式的查詢模組中做查詢的時候，開始速度很快，但是過了一段時間以後速度就變慢，最後乾脆就報錯，不工作了。在排錯的過程中，發現Oracle暫存資料表空間暴漲，達到了幾十個GB，在Oracle中對Session進行跟蹤，發現磁碟空間還在不停的消耗，幾乎是每隔5s，暫存資料表空間就會增長500MB左右，最後報錯的原因應該是因為沒有磁碟空間可以分配造成的。這是一件十分恐怖的事情。

        我們知道Oracle暫存資料表空間主要是用來做查詢和存放一些緩衝的資料的，磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁碟空間的（記憶體）的分配上，Oracle使用的是貪心演算法，如果上次磁碟空間消耗達到1GB，那麼暫存資料表空間就是1GB，如果還有增長，那麼依此類推，暫存資料表空間始終保持在一個最大的上限。像上文提到的恐怖現象經過分析可能是以下幾個方面的原因造成的。
        1. 沒有為暫存資料表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空白間不夠而出錯的問題，暫存資料表空間設定太小會影響效能，暫存資料表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
        2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會產生一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的暫存資料表空間。
       3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到暫存資料表空間，而如果有索引的話，一般只是將索引的資料複製到暫存資料表空間中。
       針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。

       總結：
       1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
       2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自訂查詢的軟體中。
       3.仔細規劃表索引。
       如果有那位高人遇到過類似的問題，請給予您的建議。

 

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.

3.查看物理磁碟剩餘空間還有20多個Ｇ，排除磁碟空間不足的原因。(我遇到的可能是磁碟空間不足的原因）。

4.查看各個資料檔案大小：

  SQL> SELECT bytes/1024/1024/1024 AS "大小(G)",NAME FROM v$datafile ORDER BY bytes;

   大小(G)　　 NAME
---------- 　　--------------------------------------------------------------------------------
0.00488281 D:/ORACLE/ORADATA/KDC/TEMP02.ORA
0.01525878 C:/DATAFILE/KDCWZ_PLAN.ORA
0.01678466 D:/ORACLE/ORADATA/KDC/TOOLS01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/CWMLITE01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/DRSYS01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/ODM01.DBF
0.02441406 D:/ORACLE/ORADATA/KDC/INDX01.DBF
0.03723144 D:/ORACLE/ORADATA/KDC/XDB01.DBF
0.14587402 D:/ORACLE/ORADATA/KDC/EXAMPLE01.DBF
0.34765625 D:/DATAFILE/KDCWZ_BILL.ORA
0.41015625 D:/ORACLE/ORADATA/KDC/SYSTEM01.DBF
0.49328613 E:/DATAFILE/KDCWZ_STOCK.ORA
1.50741577 D:/ORACLE/ORADATA/KDC/UNDOTBS01.DBF
2.03735351 D:/ORACLE/ORADATA/KDC/USERS01.DBF

14 行 已選擇

 

SQL> SELECT bytes/1024/1024/1024 AS "大小(G)",NAME FROM v$tempfile ORDER BY bytes;

   大小(G) 　NAME
---------- --------------------------------------------------------------------------------
8.00000000 D:/ORACLE/ORADATA/KDC/TEMP01.DBF

看到系統中最大資料檔案D:/ORACLE/ORADATA/KDC/TEMP01.DBF 大小為8G,初步估計是temp資料表空間無法擴充的原因。

5.重建temp資料表空間：

1.以sysdba身份登入

2.建立臨時中轉的暫存資料表空間TEMP2

　SQL> create  temporary  tablespace  TEMP2  TEMPFILE  D:/ORACLE/ORADATA/KDC/TEM
P02.DBF'  SIZE  5M  REUSE  AUTOEXTEND  ON  NEXT  640K  MAXSIZE  UNLIMITED
  2  /

資料表空間已建立。

3.修改預設暫存資料表空間為TEMP2

 SQL> alter database default temporary tablespace temp2;

資料庫已更改。

4.drop原來的暫存資料表空間TEMP:

SQL> drop tablespace temp including contents and datafiles;

資料表空間已丟棄。

5.重新建立暫存資料表空間TEMP:

　SQL> create  temporary  tablespace  TEMP  TEMPFILE  D:/ORACLE/ORADATA/KDC/TEM
P01.DBF'  SIZE  5M  REUSE  AUTOEXTEND  ON  NEXT  640K  MAXSIZE  UNLIMITED
  2  /

資料表空間已建立。

6.更改預設暫存資料表空間為TEMP:

 SQL> alter database default temporary tablespace temp;

資料庫已更改。

7.drop暫存資料表空間TEMP2

SQL> drop tablespace temp2  including contents and datafiles;

資料表空間已丟棄。

6.重新進入〔物資供應系統－調撥單下帳〕視窗，成功，問題解決。

 

 

 

--尋找暫存資料表空間

select * from database_properties

 

where property_name='DEFAULT_TEMP_TABLESPACE';

 

--查看暫存資料表空間檔案大小

select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files;

 

--sql語句

select * from v$sql;

 

--尋找佔用暫存資料表空間的sql

Select se.username,se.sid,se.serial#,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;