【Oracle专栏】跨服务器调用ORA-02064: 不支持分布式操作

Oracle && OceanBase 相关文档，希望互相学习，共同进步

风123456789～-CSDN博客

1.背景

        前几天反馈跨服务器调存储过程包，报错 ORA-02064: 不支持分布式操作。本文就该问题进行实验及解决。

2. 实验

2.1 实验准备

准备两台服务器：A服务器（即调用服务器，ip 192.168.3.14） 、

                             B服务器（即被调用服务器，ip 192.168.3.12）

2.1.1 A服务器14 准备- 表、存储过程、包

创建测试表：ew_dayend_log

创建存储过程：GET_LOGGER  插入表逻辑

创建包：day_end_test  函数中调用存储过程

–表：
create table EW_DAYEND_LOG
(
logid VARCHAR2(45),
function_name VARCHAR2(200),
table_name VARCHAR2(200),
data_issue VARCHAR2(7),
log_time DATE,
log_type VARCHAR2(5),
message VARCHAR2(500)
);

–过程：
CREATE OR REPLACE PROCEDURE GET_LOGGER(pDataIssue IN varchar2, –期次
pFunctionName IN varchar2, –方法名称
pTableName IN varchar2, –处理表名称
pType IN varchar2, –0成功 1失败 3异常
vMsg IN varchar2) is –处理信息
–插入日志
BEGIN

INSERT INTO EW_DAYEND_LOG
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES
(SYS_GUID(),
pFunctionName ,
pTableName ,
pDataIssue ,
SYSDATE,
pType ,
vMsg );
COMMIT;

END GET_LOGGER;
–包
create or replace package day_end_test is

function day_end(pDataIssue in varchar2) return varchar2;

end day_end_test;
/

CREATE OR REPLACE PACKAGE BODY day_end_test is

function day_end (pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
GET_LOGGER(pDataIssue, 'day_end_test', 'log', '0', '调用测试');
/* INSERT INTO EW_DAYEND_LOG
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES
(SYS_GUID(),
'day_end_test' ,
'log' ,
pDataIssue,
SYSDATE,
'0',
'调用测试1');
COMMIT;*/

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER(pDataIssue, 'day_end_test', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test;
/

2.1.2 B服务器12 准备- dblink、包

创建dblink：ew_14

创建包：day_end_test2

–创建dblink
create public database link ew_14
connect to xxx identified by xxx
using '192.168.3.14/orcl';
–包
create or replace package day_end_test2 is
function day_end_run(pDataIssue in varchar2) return varchar2;
end day_end_test2;
/
CREATE OR REPLACE PACKAGE BODY day_end_test2 is

function day_end_run(pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
GET_LOGGER@ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '0', '12调用测试');

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER@Ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test2;
/

2.2 实验测试

2.2.1 在B服务器14 测试本地的存储过程、包

test包发现没有问题，保证本地是正确的。

执行结果：ok

2.2.2 在A服务器12 测试调用 B服务器

查询dblink 14：ok

–查询14
select * from EW_DAYEND_LOG@EW_14 ;

结果：查询ok

表直接插入14：ok

–插入
INSERT INTO EW_DAYEND_LOG@Ew_14
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES (SYS_GUID(), 'day_end_test' , 'log' ,'2026-08', SYSDATE,'0','调用测试1');
COMMIT;

select * from EW_DAYEND_LOG@Ew_14;

结果：插入ok

包调用 远端存储过程 ：ok

测试结果：

查看结果：ok

 包调用 远端包 ：ORA-02064

–修改包体：改为调用远程包
CREATE OR REPLACE PACKAGE BODY day_end_test2 is

function day_end_run(pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER@ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '0', '12调用测试');

vResult := day_end_test.day_end@ew_14(pDataIssue);
EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER@Ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test2;

 再次测试：返回1 报错

报错：ORA-02064: 不支持分布式操作

19行就是插入commit 的时候。

至此，问题复现。

2.3 实验解决

2.3.1 修改A服务器14的包  测试no

修改A服务器14的包：将包中的存储过程注释调 ，改为直接插入表，再测试

CREATE OR REPLACE PACKAGE BODY day_end_test is

function day_end (pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER(pDataIssue, 'day_end_test', 'log', '0', '调用测试');
INSERT INTO EW_DAYEND_LOG
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES
(SYS_GUID(),'day_end_test' ,'log' , pDataIssue,SYSDATE, '0','调用测试33');
COMMIT;

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER(pDataIssue, 'day_end_test', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test;

在B服务器调用A的包，测试结果：依然报错 ORA-02064: 不支持分布式操作

说明： 调用远端包不可以，调用远端存储过程可以。

2.3.2 增加A服务器含包的存储过程  测试 no

–增加存储过程
CREATE OR REPLACE PROCEDURE PRO_TEST(pDataIssue IN varchar2,
vResult OUT varchar2) is

BEGIN

vResult := day_end_test.day_end(pDataIssue);

END PRO_TEST;

本地测试：ok

远端调用b：依然报错

–修改远端调用包：改为调用存储过程
CREATE OR REPLACE PACKAGE BODY day_end_test2 is

function day_end_run(pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER@ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '0', '12调用测试');

–vResult := day_end_test.day_end@ew_14(pDataIssue);
PRO_TEST@ew_14(pDataIssue,vResult);

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER@Ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test2;

测试结果：报错 ORA-02064: 不支持分布式操作

2.3.3 解决思路1：被调用的不要commit

     解决思路：被调用的不要commit，rollback等数据库事务操作，统一在服务端执行。

          服务器A 14 被调用端：代码注释commit。服务器B 12 调用端，增加commit;

          代码修改如下：

–服务器A 被调用端，去掉commit

CREATE OR REPLACE PACKAGE BODY day_end_test is

function day_end (pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER(pDataIssue, 'day_end_test', 'log', '0', '调用测试');
INSERT INTO EW_DAYEND_LOG
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES
(SYS_GUID(),'day_end_test' ,'log' , pDataIssue,SYSDATE, '0','调用测试33');
–COMMIT;

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER(pDataIssue, 'day_end_test', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test;

–服务器b 12上：增加commit
CREATE OR REPLACE PACKAGE BODY day_end_test2 is

function day_end_run(pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER@ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '0', '12调用测试');

vResult := day_end_test.day_end@ew_14(pDataIssue);
–PRO_TEST@ew_14(pDataIssue,vResult);
commit;
EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER@Ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test2;

执行结果：ok

但是，被调用的包中不可能只有一个commit，而且里面还调用的子过程，所以还是采用另外一种方式解决：自治事务。

2.3.4 解决思路2：被被调用的采用自治事务 

     解决思路：用Oracle自治事务来解决。在调用端中的procedure中添加oralce自治事务的声明方法为：PRAGMA AUTONOMOUS_TRANSACTIO

     即在服务器B侧增加语句，即将分布式调用设置成为自主提交. 修改代码如下：

–服务器A 14 被调用端：增加自治事务申明
CREATE OR REPLACE PACKAGE BODY day_end_test is

function day_end (pDataIssue in varchar2) return varchar2 is
PRAGMA AUTONOMOUS_TRANSACTION;
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER(pDataIssue, 'day_end_test', 'log', '0', '调用测试');
INSERT INTO EW_DAYEND_LOG
(LOGID, FUNCTION_NAME, TABLE_NAME, DATA_ISSUE, LOG_TIME, LOG_TYPE, MESSAGE)
VALUES
(SYS_GUID(),'day_end_test' ,'log' , pDataIssue,SYSDATE, '0','调用测试33');
COMMIT;

EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER(pDataIssue, 'day_end_test', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test;

–服务器B 12 调用端：去掉commit,改回正常调用
CREATE OR REPLACE PACKAGE BODY day_end_test2 is

function day_end_run(pDataIssue in varchar2) return varchar2 is
vResult varchar2(10);
vMsg varchar2(1000);
begin
vResult := '0';

BEGIN
–GET_LOGGER@ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '0', '12调用测试');

vResult := day_end_test.day_end@ew_14(pDataIssue);
–PRO_TEST@ew_14(pDataIssue,vResult);
–commit;
EXCEPTION
WHEN OTHERS THEN
vMsg := '报错位置：' || dbms_utility.format_error_backtrace || '报错信息：' ||
substr(SQLERRM, 1, 200);
GET_LOGGER@Ew_14(pDataIssue, 'day_end_test2.day_end_run@12', 'log', '3', vMsg);
vResult := '1';
END;
return vResult;
end;
end day_end_test2;

测试结果：ok

将被调用端 改回最初的包（包里调用的子过程），再次测试：

测试结果：ok  

自此，问题彻底解决

3.总结   

1）报错场景 

     Oracle不支持分布式操作：ORA-02064: 不支持分布式操作

一、使用DBLink 调用远程的存储过程，远程存储过程存在Commit语句，会导致此异常。

二、 使用DBLink 调用远程的存储过程时，远程存储过程通过引用游标返回结果集，也会导致此异常。

    例如： 数据库A ,B 通过DBlink互相访问, 数据库A调用数据库B的存储过程pro_b , pro_b里面有DML语句,且有commit ,或rollback. 这时数据库A通过DBlink 的调用pro_b@B就会产生这个错误。

2）分布式事务原理💡

         当需要在多个Oracle数据库之间进行数据一致性操作时，就会用到分布式事务。

        分布在本地和远程两个db的事务同时操作，就构成了一个分布式事务。分布式事务采用 Two-Phase Commit 提交机制，保证分布在各个节点的子事务能够全部提交或全部回滚的原子性。

在这种机制下，事务处理过程分为三个阶段：

• PREPARE：发起分布式事务的节点通知各个关联节点准备提交或回滚。
• COMMIT：写入commited SCN，释放锁资源
• FORGET：悬疑事务表和关联的数据库视图信息清理

        各关联节点此时会做三个事情：刷新redo信息到redo log中；将持有的锁转换为悬疑事务锁；取各节点中最大的SCN号进行同步。

     SQL Server依赖分布式事务协调器Distributed Transaction Coordinator（MSDTC）来使用分布式事务，Oracle Client使用Oracle Services for Microsoft Transaction Server服务来支持分布式事务。

3）Oracle 自治事务

        oracle一个存储过程中调用了另外一个存储过程，为了使两个存储过程之间的事务不会相互影响，就需要自治事务处理：

       PRAGMA  AUTONOMOUS_TRANSACTION;  — 用于标记子程序为自主事务处理

到此：ok 

项目管理–相关知识   

项目管理-项目绩效域1/2-CSDN博客

项目管理-项目绩效域1/2_八大绩效域和十大管理有什么联系-CSDN博客

项目管理-项目绩效域2/2_绩效域 团不策划-CSDN博客

高项-案例分析万能答案（作业分享）-CSDN博客

项目管理-计算题公式【复习】_项目管理进度计算题公式:乐观-CSDN博客

项目管理-配置管理与变更-CSDN博客

项目管理-项目管理科学基础-CSDN博客

项目管理-高级项目管理-CSDN博客

项目管理-相关知识（组织通用治理、组织通用管理、法律法规与标准规范）-CSDN博客

Oracle其他文档，希望互相学习，共同进步

Oracle-找回误删的表数据(LogMiner 挖掘日志)_oracle日志挖掘恢复数据-CSDN博客

oracle 跟踪文件–审计日志_oracle审计日志-CSDN博客

ORA-12899报错，遇到数据表某字段长度奇怪现象：“Oracle字符型，长度50”但length查却没有50_varchar(50) oracle 超出截断-CSDN博客

EXP-00091: Exporting questionable statistics.解决方案-CSDN博客

Oracle 更换监听端口-CSDN博客