“Improving the PL/SQL Debugger:
In prior releases, it was necessary to change the application to include calls to start and stop debugging. With this improvement, one database session can start debugging, and stop debugging a different session.”
“With these improvements, if a problem occurs in a long running test or in a production environment, it is possible to investigate the problem from another session.” Oracle Database 12c Release 2 (12.2) New Features
Let’s take a closer look :
TEST ENV : ORACLE 22.214.171.124/OEL6/SQL Developer Version 126.96.36.199
Debugging a remote session the old way :
This is already well explained in this blog post Sqldeveloper remote debugger
“1. Developer – compiles a PL/SQL code in debug mode
2. Developer – starts remote listener debugger
3. Developer – sets breakpoint(s) in code
3. Client – establishes connection with remote listener debugger. This can be done as well inside code of a debugged code.
4. Client – starts code
5. Developer – waits and traps code executed by client. So developer will take over control on execution of code”
In previous release the debugged session (Client) has to call “DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ );” to establishes connection with remote listener debugger.This may need a change of the application code.
Debugging a remote session the new way :
Starting with oracle 188.8.131.52 the client does not need any more to call DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ ) to connect to the listener debugger.The debugger session (Developer/DBA) can enable debugging just by calling DBMS_DEBUG_JDWP.CONNECT_TCP( ‘remote_listener_debugger_ip’, ‘port’ ,’session_sid’,’session_serial#’).Here is a quick overview :
1-Create procedure (debug mode) and put a break point on it
2-Start the listener debugger :
3- Check for debuggable session using : v$plsql_debuggable_sessions
4-Establishes connection between the client and the remote listener debugger
5-That’s it you can now take control of the program when the client execute it !
That’s it 😀