关于ORA-02064: distributed operation not supported的解决方案

今天帮开发人员看到一个这样的“ORA-02604 不支持分布操作”的错误。

错误原型环境模拟：

SQL> conn /@ora101a.world as sysdba;

Connected. 

SQL> create user test identified by test; 

User created. 

SQL> grant connect, resource, dba to test; 

Grant succeeded. 

SQL> conn /@ora101b.world as sysdba;

Connected. 

SQL> create user test identified by test; 

User created. 

SQL> grant connect, resource, dba to test; 

Grant succeeded. 

SQL> create public database link ora101a.world using 'ORA101A.WORLD'; 

Database link created. 

SQL> conn test/test@ora101b.world 

Connected. 

SQL> create database link ora101a.world connect to test identified by test; 

Database link created. 

SQL> select * from global_name@ora101a.world; 

GLOBAL_NAME                     

------------------------------------------------------------------------------

ORA101A.WORLD                                                            

   

SQL> CREATE TABLE ALL_DB_USERS

  2  (

  3    DB_NAME                      VARCHAR2(30 BYTE)  NULL,

  4    DB_ID                        NUMBER(7)         NULL,

  5    USERNAME                     VARCHAR2(30 BYTE) NOT NULL,

  6    USER_ID                      NUMBER           NOT NULL,

  8    ACCOUNT_STATUS               VARCHAR2(32 BYTE) NOT NULL,

  9    LOCK_DATE                    DATE,

10    EXPIRY_DATE                  DATE,

11    DEFAULT_TABLESPACE           VARCHAR2(30 BYTE) NOT NULL,

12    TEMPORARY_TABLESPACE         VARCHAR2(30 BYTE) NOT NULL,

13    CREATED                      DATE             NOT NULL,

14    PROFILE                      VARCHAR2(30 BYTE) NOT NULL,

15    INITIAL_RSRC_CONSUMER_GROUP  VARCHAR2(30 BYTE),

16    EXTERNAL_NAME                VARCHAR2(4000 BYTE)

17  )

18  -- TABLESPACE RADBA_DATA

19  PCTUSED    0

20  PCTFREE    10

21  INITRANS   1

22  MAXTRANS   255

23  STORAGE    (

24              INITIAL          64K

25              MINEXTENTS       1

26              MAXEXTENTS       2147483645

27              PCTINCREASE      0

28              FREELISTS        1

29              FREELIST GROUPS  1

30              BUFFER_POOL      DEFAULT

31             )

32  LOGGING

33  NOCOMPRESS

34  NOCACHE

35  NOPARALLEL

36  MONITORING

37  / 

Table created. 

SQL> merge into all_db_users  c1

  2  USING ( SELECT

  4     ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,

  5     DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED,

  6     PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME

  7  FROM dba_users@ora101a.world)   c2

  8  -- FROM dba_users@remotedb)   c2

  9    on (c1.user_id = c2.user_id )

10   when not matched then

11   INSERT (

13       ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,

14       DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED,

15       PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME)

16       VALUES (

18          C2.ACCOUNT_STATUS, C2.LOCK_DATE, C2.EXPIRY_DATE,

19          C2.DEFAULT_TABLESPACE, C2.TEMPORARY_TABLESPACE, C2.CREATED,

20          C2.PROFILE, C2.INITIAL_RSRC_CONSUMER_GROUP, C2.EXTERNAL_NAME) ;

merge into all_db_users  c1

           *

ERROR at line 1:

ORA-02064: distributed operation not supported 

这是oracle10G针对不同数据库实例下使用merge语句时出现的错误，该错误是10G的一个bug，bug ID为4311273。此bug在oracle11G有修复。所以，如果想避免该错误的出现，只能从业务逻辑上绕过去。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/637736/viewspace-312612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/637736/viewspace-312612/