更新語句執行很長時間並拋出 ORA-01654 表空間錯誤

我有一個簡單的更新語句，它執行了很長時間（>15 分鐘）來更新 oracle19c 中大約 120K 的記錄，但它也因 ORA-01654 表空間錯誤而最終失敗。

有沒有辦法減少這個更新語句的執行時間以及如何克服這個表空間錯誤。

UPDATE TABLE1 F 
SET F.SYSTEM_ID = NVL(
(select TC.NEW_SYSTEM_ID  from TABLE2 TC where TC.OLD_SYSTEM_ID=F.SYSTEM_ID),F.SYSTEM_ID);

То 克服錯誤“ORA-01654：無法擴展索引…”在大多數情況下，您必須擴展相應的表空間。根據具體情況和您的偏好，有三種不同的方法可以做到這一點：

• 向表空間添加一個新的數據文件，例如ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/data/users02.dbf' SIZE 200M：
• 增加表空間現有數據文件的大小，例如：ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users01.dbf' RESIZE 200M;
• 允許對錶空間的現有數據文件進行原子擴展，例如：ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 200M.

有關詳細資訊，請參閱Oracle 21c 數據庫文件，數據庫管理員指南，第二部分 Oracle 數據庫結構和儲存，11 管理表空間，11.8 更改和維護表空間，11.8.1 增加表空間的大小。有關上述 SQL 命令的特定語法和選項，請參閱Oracle 21c 數據庫文件，SQL 語言參考。

要減少更新的執行時間，您可以執行以下部分（或全部）操作：

• 如果 TABLE1.SYSTEM_ID 列上有任何索引，請嘗試在更新前刪除它們並在更新後重新創建它們。
• 如果您沒有索引，請在 TABLE2.OLD_SYSTEM_ID 列上創建索引。從現有的更新語句來看，該列的值是唯一的。如果這是真的，那麼最好在列上設置主鍵或唯一鍵約束——它將使用現有索引，如果沒有合適的索引，它將創建一個新索引。
• 重新制定更新語句。這應該有所作為。在這裡，我看到了兩種選擇：使用可更新的內聯視圖或使用合併語句。

因此，要創建可更新視圖，您必須在 TABLE2.OLD_SYSTEM_ID 列上具有主鍵或唯一鍵約束（請參閱之前的建議）。新的更新語句將如下所示：

update
(
 select f.SYSTEM_ID, tc.NEW_SYSTEM_ID
 from TABLE1 f join TABLE2 tc on f.SYSTEM_ID = tc.OLD_SYSTEM_ID
)
set SYSTEM_ID = NEW_SYSTEM_ID

IMO 這是“修復”此聲明的最佳方式。

否則，要使用合併語句，您必須對不包括 SYSTEM_ID 列的 TABLE1 具有主鍵約束。合併語句將如下所示：

merge into TABLE1 t
using
(
 select f.PKCOL1, tc.NEW_SYSTEM_ID
 from TABLE1 f join TABLE2 tc on f.SYSTEM_ID = tc.OLD_SYSTEM_ID
) s
on (t.PKCOL1 = s.PKCOL1)
when matched then update set t.SYSTEM_ID = s.NEW_SYSTEM_ID

這裡 PKCOL1 是 TABLE1 中的假設主鍵列。

萬不得已，如果無法製作/使用 TABLE1 的主鍵，可以將合併語句中的主鍵列替換為rowid. 如果表中同時有其他“寫入者”，這是不安全的——它會起作用，但某些行可能不會收到更新。

引用自：https://dba.stackexchange.com/questions/313422