ORACLE ORA-01653: unable to extend table 的錯誤
今天用PL SQL Developer往oracle資料庫中導入資料時,突然報錯,隻能終止,錯誤的具體内容如下:
ORA-01653: unable to extend table USER_DATA.JKHDFXJL by 128 in tablespace MSMS
大概意思是說USER_DATA表空間不足了,于是google了一下,大概有了些眉目。出現這種表空間不足的問題一般有兩種情況:一種是表空間的自動擴充功能沒有開;另一種是空間确實不夠用了,已經達到了自動擴充的上限。
是以我們解決問題的步驟就是先檢視Oracle資料庫表空間大小,然後看一下表空間下的所有的資料檔案的自動擴充功能是否打開;若确實是表空間不夠用了,那麼我們就需要擴大表空間了。
具體步驟如下:
在PL SQL Developer中執行如下SQL語句
SELECT a.tablespace_name "表空間名",
a.bytes / 1024 / 1024 "表空間大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空間(M)",
b.bytes / 1024 / 1024 "空閑空間(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC
"表空間大小(M)"表示表空間所有的資料檔案總共在作業系統占用磁盤空間的大小;
比如:USER_DATA表空間有2個資料檔案,datafile1為300MB,datafile2為400MB,那麼USER_DATA表空間的"表空間大小"就是700MB。
"已使用空間(M)"表示表空間已經使用了多少;
"空閑空間(M)"表示表空間剩餘多少;
"使用比"表示已經使用的百分比;
比如從步驟1中檢視到USER_DATA表空間已使用百分比達到90%以上,可以檢視該表空間總共有幾個資料檔案,每個資料檔案是否自動擴充,可以自動擴充的最大值。
SELECT file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB"
WHERE tablespace_name = 'USER_DATA';
檢視 MSMS 表空間是否為自動擴充
SELECT file_id, file_name, tablespace_name, autoextensible, increment_by
WHERE tablespace_name = 'USER_DATA'
ORDER BY file_id desc;
檢視"autoextensible"對應的值是YES還是NO,若是NO,說明MSMS表空間的自動擴充功能沒有開,改成YES就可以了。
比如MSMS表空間目前的大小為0.9GB,但最大每個資料檔案隻能為1GB,資料檔案快要寫滿,那麼我們就需要擴大表空間了。
擴大表空間又分兩種做法:一種是增大資料檔案大小,一種是增加資料檔案數量。
首先找出該表空間對應的資料檔案及路徑
SELECT * FROM dba_data_files t WHERE t.tablespace_name='表空間名稱';
--查找對應的表空間中的資料檔案的全路徑,該路徑對應FILE_NAME字段。
解決方法1:增大資料檔案
alter database datafile '全路徑的資料檔案名稱' resize ***M;
--增加對應的表空間裡面的某個資料檔案的大小為***M。
解決方法2:增加資料檔案
擷取建立表空間的語句:
SELECT dbms_metadata.get_ddl('TABLESPACE', 'USER_DATA') FROM dual;
确認磁盤空間足夠,增加一個資料檔案【用作業系統UNIX、Linux中的df -g指令(檢視下可以使用的磁盤空間大小)】
alter tablespace 表空間名稱 add datafile '全路徑的資料檔案名稱' size ***M
autoextend on maxsize 20G;
--新增一個資料檔案,全路徑的資料檔案名稱為該新增資料檔案的全路徑檔案名稱。大小為***M,自動擴充功能打開,且該資料檔案的最大擴充值為20G。
驗證已經增加的資料檔案
SELECT file_name, file_id, tablespace_name
WHERE tablespace_name = '表空間名稱'
如果删除表空間資料檔案
altertablespace 表空間名稱
DROP datafile '/u01/app/oracle/product/10.2.0/oradata/orcl/USER_DATA.dbf '
PS:表空間一般讓空閑百分比保持在10%以上,且資料檔案大小最好不要超過2G。當表空間不足時,要麼resieze datafile,要麼增加datafile。