Oracle12c通过SQL Developer工具调试procedure报错: ORA-24247解决方法

最新推荐文章于 2024-06-03 10:44:02 发布

dingguanyi

最新推荐文章于 2024-06-03 10:44:02 发布

阅读量1.8k

点赞数

分类专栏：数据库文章标签： oracle

数据库专栏收录该内容

131 篇文章 9 订阅

订阅专栏

1.首选赋予用户debug权限
(1)以管理员登录：sqlplus / as sysdba; show user;
(2)赋权：grant DEBUG CONNECT SESSION , DEBUG ANY PROCEDURE to c##scott;

2.但是在Oracle12c中, 如果仅有此权限, 在debug时还会报如下错误:
Connecting to the database USERXXX.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.10.101’, ‘61116’ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database USERXXX.

3.登录SYS用户执行以下语句即可

BEGIN  
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE  
    (  
        host => '192.168.1.109', --指定host
        lower_port => null,  
        upper_port => null,  
        ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),  
        principal_name => 'c##scott', --指定user  
        principal_type => xs_acl.ptype_db)  
    );  
END;

4.原因
从Oracle 12c开始，如果通过基于JDWP(Java Debug Wire Protocol)协议的调试器(如SQL Developer, JDeveloper)调试PL/SQL存储过程, 需要给用户赋予JDWP ACL权限，允许其通过某台主机用调试工具连接数据库

5.参考
https://galobalda.wordpress.com/2014/02/17/sql-developers-plsql-debugger-and-oracle-12c/