oracle 儲存過程執行報錯ORA-12828

    作業系統：windows server 2008R2

    資料庫版本：oracle 11.2.0.3 單例項 

   今天，一開發同事找來，說他的儲存過程在龍巖地市執行報錯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相關

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 to 11.2.0.4 [Release 11.2]
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:

CAUSE

As 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.

REFERENCES

BUG:14684769 - REMOTE PARALLEL INDEX REBUILD RUNS SERIAL
BUG:2166879 - ORA-12828 WHEN INDEX CREATED IN PARALLEL MODE THROUGH REMOTE PROCEDURE

    

    檢視開發同事提供的程式碼並沒有使用到dblink，根據oracle官方（ID 1535660.1）提示，ORA12828與索引重建有關，

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

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

另外，根據他提供的資訊該程式碼並不是在所有地市都報錯，只有目前的龍巖windows server 2008R2 oracle 11.2.0.3單例項下。