ORA-1653: unable to extend table by 1024 in tablespace(oracle表空間滿了的解決方案)

Moshow鄭鍇發表於2020-04-07
Oracle

前言

今天某專案的一個Repository的正常CRUD操作,發現報錯資訊ORA-1653:unable to extend table by 1024 in tablespace,什麼意思呢,就是表空間已滿,無法擴充套件.

問題分析

USERS表空間是預設使用者表空間,在建立一個使用者並沒有指定此使用者使用表空間時,該使用者所有資訊都會放入到users表空間中,如果有指定則一般是使用者名稱相關的表空.

--檢視錶空間檔案
select file_name t from dba_data_files t where t.tablespace_name='xxxx';

--檢視錶記錄大小
select t.tablespace_name,t.TABLE_NAME,t.NUM_ROWS from all_tables t where t.tablespace_name='xxxx' order by num_rows desc;

--檢視錶空間大小
select t.file_name,t.tablespace_name,t.bytes/1024/1024 "bytes MB",t.maxbytes/1024/1024 "maxbytes MB" from t.dba_data_files where tablespace_name='xxx';

查詢使用xxx表空間的表,按行級降序排序,一般多個表使用相同表空間,存在大量資料導致USER表佔滿,像剛才檢視的這個問題的表,超過一千萬條記錄.

解決方案

  1. 擴充套件表空間: alter datafile '/oracle/oradata/dba/users01.dbf' resize 30G;
  2. 擴充套件到最大30G檔案無法繼續擴充套件,可增加資料檔案：alter tablespace users add datafile 'users02.dbf' size 1024m autoextend on next 1024m maxsize 30G;
  3. truncate刪除無用表釋放空間,假如未釋放,對TEST表進行收縮shrink,執行下面三個語句:
-- 啟用行遷移：
alter table  TEST enable row movement;
-- shrink表test：
alter TABLE  TEST shrink SPACE;
-- 關閉行遷移：
alter table  TEST DISABLE row movement;

Oracle “高水位”

資料被刪除後(無論是 delete 還是 truncate table),資料檔案大小不一定會縮小, 是Oracle"高水位"所致,想要降低資料檔案大小需降低高水位的正確做法是先降低HWM,再確定實際佔有大小,再resize資料檔案,執行如下4個語句:

  1. 查詢表空間檔案編號：select file#, name from v$datafile;
  2. 根據檔案 ID 查詢這個資料檔案最大資料塊(data block)的編號：select max(block_id) from dba_extents where file_id=4;
  3. 計算該表空間實際佔用的空間,先查詢資料塊大小,預設是8192： select value from v$parameter where name='db_block_size'
  4. 計算實際佔用磁碟大小： select 65673*8/1024 from dual;
  5. 把資料檔案大小resize到比實際佔用磁碟大小大一些就行了,這樣資料檔案大小就變小了,節約空間 :alter database datafile '/oracle/oradata/dba/users01.dbf' resize 600m;
  6. 需要使用的表,修改表空間alter table xxx move tablespace new_tablespace,建表時需養成習慣,指定好表空間.

更多詳情可以參考 <<Oracle官方Changing Datafile Size>>

相關文章