oracle存儲過程執行報錯ORA-12828該怎么辦

oracle存儲過程執行報錯ORA-12828該怎么辦，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

   今天，一開發同事找來，說他的存儲過程在龍巖地市執行報錯ORA-12828，說代碼執行到如下代碼塊的時候報錯退出：

EXECUTE IMMEDIATE 'ALTER TABLE FACT_COSTS TRUNCATE PARTITION P_FACT_COSTS_'||PRM_MONTH||' update global indexes';

    查看oracle聯機文檔對ORA-12828的解釋：

[oracle@se31 ~]$ oerr ora 12828
12828, 00000, "Can't start parallel transaction at a remote site"
// *Cause: PDML transaction cannot be started because we are not in the
//         coordinator site of the distributed transaction.
// *Action: Do not use PDML at remote sites.
[oracle@se31 ~]$ 

    查詢oracle support知識庫，有篇文檔（ID 1535660.1）與ORA-12828相關

Information in this document applies to any platform.
SYMPTOMS

The remote parallel index rebuild runs serial even if enabling parallel replication propagation at the database link level. For example if a database link is created for a particular destination database, and enabling parallel propagation for a database link, then Oracle uses multiple parallel slave processes to replicate to the corresponding destination. But it doesn't work while parallel index rebuild remotely through a procedure. In detail:
CAUSEAs the Bug 14684769 states, this works as expected and it is not supported to parallelize the index by using the dblink.
SOLUTION

The dblink is not supported to parallelize the rebuild index.

As per the fix for Bug 2166879, create index will not be compiled for parallel execution if it is being executed in a distributed txn.

BUG:2166879 - ORA-12828 WHEN INDEX CREATED IN PARALLEL MODE THROUGH REMOTE PROCEDURE<br font-size:16px;white-space:normal;background-color:#FFFFFF;" />

    

    查看開發同事提供的代碼并沒有使用到dblink，根據oracle官方（ID 1535660.1）提示，ORA12828與索引重建有關，

讓開發同事嘗試去掉update global indexes子句重新執行，可成功執行。其實更新表數據同步更新索引，是防止分區表分區索引失效。

于是，建議他將索引重建子句去除，另外在存儲過程中添加專門的索引重建語句（經他測試，可以執行）。

另外，根據他提供的信息該代碼并不是在所有地市都報錯，只有目前的龍巖windows server 2008R2 oracle 11.2.0.3單實例下。

看完上述內容，你們掌握oracle存儲過程執行報錯ORA-12828該怎么辦的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！