最近遇到一问题,在oracle存储过程中使用DBLINK时,会报这个错:ORA-02046:分布式事务处理已经开始
ORA-02063:preceding line from SZDWSZDW为DBLINK名称。用完DBLINK后已关闭SESSIONALTER SESSION COLSE DATABASE LINK SZDW;而且在用DBLINK前已经预先进行了一步异常处理用来关闭SESSION。但是偶尔还是会报上面的错误,而且有时会报,有时不会报。贴部分代码:
BEGIN
----步骤4关闭DBLINK
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4关闭DBLINK';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'ALTER SESSION CLOSE DATABASE LINK SZDW';
EXECUTE IMMEDIATE V_STMT; V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
省略 WHEN DBLINK_NO_OPEN THEN
V_SQLCODE := SQLCODE;
V_SQLMSG := SQLERRM;
V_JOB_ROWS := -1;
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP,
V_JOB_NAME, V_JOB_ROWS,V_SQLMSG, 2,V_SQLCODE,NULL,'DBLINK NOT OPEN');
COMMIT;
END; ----步骤4向表RAHEBS.PROD_BO_ORDER插入数据
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4向表RAHEBS.PROD_BO_ORDER插入数据';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'INSERT INTO RAHEBS.PROD_BO_ORDER
SELECT GENERATE_DATE,
BO_ID,
PROD_ID,
OL_ID,
BO_ACTION_TYPE_CD,
BO_ACTION_TYPE_NAME,
COMPLETE_DT,
SS_STAFF_ID,
SS_STAFF_NAME,
SALE_STAFF_NUMBER,
STAFF_NAME,
CHANNEL_ID,
CHANNEL_NAME,
CHANNEL1_ID,
CHANNEL1_NAME
FROM FACT_CO_BO_LIST_'|| TO_CHAR(IN_STAT_MONTH)||'@SZDW FACT_T
WHERE FACT_T.BO_ACTION_TYPE_NAME NOT IN(''服务信息变动'',''退订'',''订购'')';
EXECUTE IMMEDIATE V_STMT;
V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
省略 COMMIT;
execute immediate 'alter session close database link szdw'; --session回收
COMMIT;请高手指点,上述代码该如何改呢。
ORA-02063:preceding line from SZDWSZDW为DBLINK名称。用完DBLINK后已关闭SESSIONALTER SESSION COLSE DATABASE LINK SZDW;而且在用DBLINK前已经预先进行了一步异常处理用来关闭SESSION。但是偶尔还是会报上面的错误,而且有时会报,有时不会报。贴部分代码:
BEGIN
----步骤4关闭DBLINK
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4关闭DBLINK';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'ALTER SESSION CLOSE DATABASE LINK SZDW';
EXECUTE IMMEDIATE V_STMT; V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
省略 WHEN DBLINK_NO_OPEN THEN
V_SQLCODE := SQLCODE;
V_SQLMSG := SQLERRM;
V_JOB_ROWS := -1;
RAHEAP.AU_ETL_STEP_MONTH_LOG(IN_STAT_MONTH,V_SP_JOB_ID,-1,V_JOB_ID,2,V_JOB_BT,SYSTIMESTAMP,
V_JOB_NAME, V_JOB_ROWS,V_SQLMSG, 2,V_SQLCODE,NULL,'DBLINK NOT OPEN');
COMMIT;
END; ----步骤4向表RAHEBS.PROD_BO_ORDER插入数据
V_JOB_ID := 8010002000904;
V_JOB_NAME := '步骤4向表RAHEBS.PROD_BO_ORDER插入数据';
V_JOB_BT := SYSTIMESTAMP;
V_STMT := 'INSERT INTO RAHEBS.PROD_BO_ORDER
SELECT GENERATE_DATE,
BO_ID,
PROD_ID,
OL_ID,
BO_ACTION_TYPE_CD,
BO_ACTION_TYPE_NAME,
COMPLETE_DT,
SS_STAFF_ID,
SS_STAFF_NAME,
SALE_STAFF_NUMBER,
STAFF_NAME,
CHANNEL_ID,
CHANNEL_NAME,
CHANNEL1_ID,
CHANNEL1_NAME
FROM FACT_CO_BO_LIST_'|| TO_CHAR(IN_STAT_MONTH)||'@SZDW FACT_T
WHERE FACT_T.BO_ACTION_TYPE_NAME NOT IN(''服务信息变动'',''退订'',''订购'')';
EXECUTE IMMEDIATE V_STMT;
V_JOB_ROWS := SQL%ROWCOUNT;
--步骤监控
省略 COMMIT;
execute immediate 'alter session close database link szdw'; --session回收
COMMIT;请高手指点,上述代码该如何改呢。
Oracle Server - Enterprise Edition - Version: 7.3.4.0 to 9.0.1.5
This problem can occur on any platform.Symptoms
Transactions fail when connecting via a dblink with ORA-2046 & ORA-2063 errors. The remote instance will show ORA-600 [4400], [48] errors are occurring, e.g.:Local instanceORA-02046: distributed transaction already begun
ORA-02063: preceding %s%s from %s%sRemote instanceORA-00600: internal error code, arguments: [4400], [48], [], [], [], [], []
The call stack trace will resemble:ktcddt kssdch ktcbod kssdch ksuxds ksudel opilof opiodr ttcpip opitsk opiino opiodr opidrv sou2o main startCause
This error is caused by a secondary error being encountered during execution of the transaction. In this particular case, a trigger executing a java stored procedure was erroring with an "ORA-1821: date format not recognized" error, and hence this was due to bug:1913494 fixed in 9.2.
Solution
Resolve any error occurring on the remote site when executing the transaction. The error can be identified by setting event 10046 in the instance init.ora file, e.g.:event = "10046 trace name context forever, level 4"and then restart the instance and replicate the error.
楼主数据库什么版本的
上面是在metalink上查到的信息 你看看
oracle错误一览表,希望对楼主有用
Oracle Database Error ORA-02074
ORA-02074 cannot string in a distributed transaction
问题
A commit or rollback was attempted from session other than the parent of a distributed transaction.
解决
Only commit or rollback from the parent session.
Oracle Database Error ORA-02074
ORA-02074 cannot string in a distributed transaction
问题
A commit or rollback was attempted from session other than the parent of a distributed transaction.
解决
Only commit or rollback from the parent session.