精华内容
下载资源
问答
  • NULL 博文链接:https://rongren.iteye.com/blog/1886071
  • ORA-02063ORA-02049

    2021-08-14 07:42:17
    ORA-02049:timeout:... ORA-02036:preceding... 原因:远端的目标表独占锁,导致本地的连接一直在等待,导致连接超时报错。 还有一种情形就是当通过dblink更新视图报错ORA-02036的时候,可能是创建的dblink包含order...

    背景:

    本地通过dblink更新远端的某一个表,报错:

    ORA-02049:timeout:...

    ORA-02036:preceding...

    原因:远端的目标表独占锁,导致本地的连接一直在等待,导致连接超时报错。

    还有一种情形就是当通过dblink更新视图报错ORA-02036的时候,可能是创建的dblink包含order by等子句,无法进行更新。

    可更新的视图不得包含以下任何构造:set运算符

    DISTINCT运算符
    聚合或分析函数
    GROUP BY,ORDER BY,MODEL,CONNECT BY或START WITH子句SELECT列表中的集合表达式SELECT列表中
    的子查询SELECT列表
    指定为WITH READ ONLY的子查询

    从视图定义中删除order by子句。

    展开全文
  • oracle连接多个sqlserver的链接,主要修改initxxx.ora(oracle安装用户下),listener.ora(rac环境修改grid下的),tnsnames.ora(oracle用户下的) [oracle@rac1 admin]$ pwd /oracle/app/oracle/product/19c/dbhome_...

    oracle连接多个sqlserver的链接,主要修改initxxx.ora(oracle安装用户下),listener.ora(rac环境修改grid下的),tnsnames.ora(oracle用户下的)

    [oracle@rac1 admin]$ pwd
    /oracle/app/oracle/product/19c/dbhome_1/dg4msql/admin
    [oracle@rac1 admin]$ ll
    total 36
    -rw-rw-r-- 1 oracle oinstall 11120 Dec 17  2013 dg4msql_cvw.sql
    -rw-rw-r-- 1 oracle oinstall   746 Jun  8  2007 dg4msql_tx.sql
    -rw-rw-r-- 1 oracle oinstall   472 Dec 11 10:23 initdg4msql.ora
    -rw-r--r-- 1 oracle oinstall   364 Dec 11 14:10 initemr.ora
    -rw-r--r-- 1 oracle oinstall   365 Dec 11 13:32 inittestdb.ora
    -rw-rw-r-- 1 oracle oinstall   411 Nov 26 13:17 listener.ora.sample
    -rw-rw-r-- 1 oracle oinstall   244 Nov 26 13:17 tnsnames.ora.sample
    [oracle@rac1 admin]$ cat initemr.ora
    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO=[192.168.52.128]:2433//emrtable
    # alternate connect format is hostname/serverinstance/databasename
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER

    [oracle@rac1 admin]$ pwd
    /oracle/app/oracle/product/19c/dbhome_1/network/admin
    [oracle@rac1 admin]$ more tnsnames.ora
    dg4msql =
      (DESCRIPTION =
        (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
      # (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.186)(PORT = 1521)) ##使用scan-ip将报错,需用vip
        )
        (CONNECT_DATA =
          (SID=dg4msql)
        )
        (HS = OK)
      )
    testdb =
      (DESCRIPTION =
        (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID=testdb)
        )
        (HS = OK)
      )
    emr =
      (DESCRIPTION =
        (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID=emr)
        )
        (HS = OK)
      )


    tnsnames.ora使用scan-ip将报错

    SQL> select * from t@sqlemr;
    select * from t@sqlemr
                    *
    ERROR at line 1:
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from SQLEMR


    [grid@rac1 admin]$ pwd
    /oracle/app/19c/grid/network/admin
    [grid@rac1 admin]$ cat listener.ora
    LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
    DGLISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=DGLISTENER))))                # line added by Agent
    LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
    LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
    ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = dg4msql)
          (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
          (PROGRAM = dg4msql)
        )
        (SID_DESC =
          (SID_NAME = testdb)
          (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
          (PROGRAM = dg4msql)
        )
        (SID_DESC =
          (SID_NAME = emr)
          (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
          (PROGRAM = dg4msql)
        )

      )

    配置完成需启停监听,检查emr服务是否注册。

    [grid@rac1 admin]$ lsnrctl status|grep emr
    Service "emr" has 1 instance(s).
      Instance "emr", status UNKNOWN, has 1 handler(s) for this service...
    [grid@rac1 admin]$ 

    [oracle@rac1 admin]$ tnsping emr

    TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-DEC-2020 14:19:11

    Copyright (c) 1997, 2020, Oracle.  All rights reserved.

    Used parameter files:


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))) (CONNECT_DATA = (SID=emr)) (HS = OK))
    OK (0 msec)

    [oracle@rac1 ~]$ sqlplus jyc/jyc@dbcenter

    SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 11 13:33:06 2020
    Version 19.8.0.0.0

    Copyright (c) 1982, 2020, Oracle.  All rights reserved.

    Last Successful login time: Fri Dec 11 2020 10:12:35 +08:00

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.8.0.0.0
     

    SQL> create public database link sqlemr connect to "sa" identified by "abcd1234" using 'emr';

    Database link created.
    注意:最好将用户名也加上双引号,避免大小写敏感问题。

    SQL> select * from t@sqlemr; --此处报错是原先initemr.ora配置1433端口,并没有对应到实际的实例上。sqlserver有多个实例的时候需要注意的不同端口问题。
    select * from t@sqlemr
                    *
    ERROR at line 1:
    ORA-01017: invalid username/password; logon denied
    [Oracle][ODBC SQL Server Wire Protocol driver][SQL Server] 'SA' ʧ
                                                                      {28000,NativeErr = 18456}
    ORA-02063: preceding 2 lines from SQLEMR

    重启emr数据库服务器后,可看到端口2433

    再次查询正常
    SQL> select * from t@sqlemr;

    name
    --------------------
    aaa
    aaa
    bb
     

    展开全文
  • 以上方式在oracle11g中查询正常,但是升级到oracle19c后,部分表查询时提示错误: ORA-00942: 表或视图不存在 ORA-02063: 紧接着 line (起自 my_db_link) 解决办法 在源库授权时,需添加schema,如下: GRANT ...

    问题描述

    在源库 d1中,添加只读查询权限给用户u2

    GRANT SELECT ON t1 TO u2;
    

    在目标库:d2中,创建数据库连接:

    create database link my_db_link
    connect to u2 identified by 123456
    using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=d1)))'
    ;
    

    跨库数据查询:

    select * from t1@my_db_link;
    

    以上方式在oracle11g中查询正常,但是升级到oracle19c后,部分表查询时提示错误:

    ORA-00942: 表或视图不存在
    ORA-02063: 紧接着 line (起自 my_db_link)
    

    解决办法

    在源库授权时,需添加schema,如下:

    GRANT SELECT ON u1.t1 TO u2;
    

    在目标库查询时,同样需要添加schema,如下:

    select * from u1.t1@my_db_link;
    
    展开全文
  • SYS@bak>select * from order@test; select * from order@test * ERROR at line 1: ...ORA-02063: preceding line from test 错误原因以及处理方法: hs/admin/init[sid].ora里配置的HS_FDS_SHAREAB

    SYS@bak>select * from order@test;

    select * from order@test

    *

    ERROR at line 1:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    ORA-02063: preceding line from test

    错误原因以及处理方法:

    hs/admin/init[sid].ora里配置的HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so不正确,应该是odbc的Lib包

    oracle 通过透明网关访问mysql报 ora-18500,ora-02063错误

    展开全文
  •  ORA-02063: preceding line from DZDZ_SJTS 行:32 文本:insert into sm123@DZDZ123 也不是所有的数据库都有这样的问题。   哪位大侠遇到过这样的问题,能否给个解决方案?非常感谢!     附:创建dblink的...
  • 本文章只解决oracle通过dblink访问sqlserver数据库时出现"ora-28500" and "ora-02063"的问题! 在安装oralce的目录下找到 xxx\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora 用编辑器打开initdg4msql.ora添加...
  • 从报错信息得知,报错在存储过程的24行: select count(1) into v_count1 from xxx.xxxx@xxxx where chsj<v_date; database link名称与报信息一致。 通过网上查找,出现此类报错的原因,是database link使用...
  • ora-00600 ora-06512 ora-02063

    千次阅读 2018-09-29 10:49:16
    记一次oracle12.2遇到的bug 数据库版本12.2.0.1.0:  调用过程的时候报错,错误如下: 过程很简单,就是入参是指定物化视图的情况下,就刷这一个物化视图,如果是空的其情况下,刷该用户下...ORA-00600 [kt...
  • 今天现场丢来一个sql,说是报数据库临时表空间不足,但是现场用到的两个库,临时表空间一个30g,一个60g,对于现场100万的用户数来说,足够了。 然后拿来现场的sql来看,sql本身也不复杂: ...
  • oracle 数据库,insert insert select的子句查询大表全表扫描,导致insert into select执行报错ORA-03150&ORA-02055&ORA-02063回滚退出。
  • Oracle dblink报错:ORA-01017、ORA-02063解决

    千次阅读 2017-11-23 13:46:10
    我使用PL/SQL的图形化操作创建了DBLink,使用时遇到如下错误: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from <link_name>
  • ORA-02409:超时:分布式事务处理等待锁定ORA-02063 一、错误现象与环境 前端应用程序运行时出现下面的错误提示: 事件添加失败:ORA-02409;超时:分布式事务处理等待锁定 ORA-02063:紧接着line(源于ITSPFDB....
  • Oracle创建dblink报错:ORA-01017、ORA-02063解决   一、创建dblink 原始语句: www.2cto.com   create public database link db_lsxy connect to lsxy identified by lsxy using '(DESCRIPTION =(ADDRESS_...
  • iamlaosong文 一个应用系统新增一个数据库服务器,因此需要建立一个数据链接以便访问,但链接...1、在要创建链接的数据库服务器上的 [TNSNAMES.ORA]文件添加对被链接数据库的连接字符: ORCLG9 = (DESCRIPTION =...
  • 通过dblink查询sql server某个表的时候... ORA-02063: preceding line from * 遇到下面的错误:经过一番折腾,修改了一些参数,无果,求助MOS,经过数周的来回取证,终于发现是gateway一个参数配置导致的。HS_NLS_NCHAR
  • 关于dblink链接报ORA-04054 与 ORA-02063

    千次阅读 2016-08-08 11:10:23
    本地实例:heyu tnsname.ora: inf=  (DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))  )  (CONNECT_DATA =  (SERVICE_NAME = R2)  )  )
  • ORA-02063,ORA-01555

    2015-01-23 10:09:27
    ORA-02063: preceding line from OFDQS_TO_NC   p_category这个表数据量并不大,只有200多条数据,不到10个字段,也没有clob等大字段, 确定这个错误并不是真的是undo段空间导致 查询MOS发现一片文章: ORA-1555...
  • 某汽车集团数据库环境为linux5.8,oracle 11g rac 11.2.0.4。dblink所连接的数据库环境未知,业务系统在通过dblink进行数据更新时在出现如下错误: ORA-00600: intern...
  •  ORA-02063: preceding line from  三、问题分析:  根据ORA-01017的提示是连接到另一方的用户密码错误,于是直接使用配置的用户密码(lsxy/lsxy)登录数据库发现正常登录,进一步的查看建立好后的dblink ...
  • 当前版本11.2.0.4 RAC,psu补丁达到19年1月。...ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MOVE_DATA [TABLE_DATA:"EPM_SC"."ARC_R_DATA":"AOTHER"."AOTHER_1707"] SELECT flags, NVL(targ...
  • 一回家,某运营商电话至,创建dblink报错ora-02063/ora-01017  关于这个BUG,遇到不是一次了,ORACLE对于该BUG 描述如下 Bug 6738104: ORA-01017 ORA-02063 WHILE CONNECTING FROM10G TO 11G VIA PUBLIC DBLINK ...
  • Oracle环境:oracle 10.2 创建的 public dblink 连接 oracle 11.2时由于版本不一致有时会出现以下错误 ... logon denied ORA-02063: preceding line from &lt;link_name&gt;  一.创建dblink create...
  • ORA-01017/ORA-02063DbLink建立错误问题分析及解决   故障介绍: 我在oracle10.2.0.4中建立连接到11.2.0.3的DBLINK验证的时候出现如下错误: ORA-01017: invalid username/password; logon denied ORA-...
  • 后来当Table1的数据量增大到34万条之后,物化视图就不再更新了,最后重新执行物化视图发现报出【ORA-02063: preceding line from DBLINK_NAME】这个错误。 2 思考解决方法的过程: 报出错误之后...
  • 操作系统环境:redhat5.6 源端数据库:oracle10.2.0.1 目标数据库:oracle 11.2.0.1 问题描述: 在源端数据库上创建了一个访问目标数据库的数据库链路: ...connect to strmadmin identified by ora

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,030
精华内容 412
关键字:

ora-02063