Oracle analyze table or analyze index validate structure failed ORA-00054

2022-02-11

5
0
Oracle
2022-02-17

1. 在進行 index 分析時，出現 ORA-00054
( Output ORA-00054 when analyze index )

SQL> analyze index schema.index_name validate structure;
analyze index schema.index_name validate structure;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

2. 原因是有Session正在執行DML動作，但不能隨意kill session，只能等該session執行完畢；隨後，依錯誤的內容，可以判斷設定timeout或nowait的設定來排除
( The reason is that there is a session that is executing DML actions, but you cannot kill the session at will, you can only wait for the session to finish executing; then, according to the content of the error, you can determine the setting of timeout or nowait to solve)

-- Solution 1 (NOWAIT)
SQL> analyze index schema.index_name validate structure online;

-- Solution 2 (TIMEOUT)
SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> analyze table analyze_test validate structure;
....
-- waiting for commit --
....
Table analyzed.

參考資料：

analyze table .. validate structure cascade fails with ORA-00054 (Doc ID 2205066.1)

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com

回首頁