How to enable remote debugging in SQL Developer

Remote debugging is a very cool feature in SQL Developer. I ran into many issues while trying to use this feature.

Install SQL Developer

Download latest version of sql developer and jdk:

I have downloaded:

a) sqldeveloper-4.1.3.20.78-no-jre.zip
b) jdk-8u73-linux-x64.tar.gz


tar -xvzf  jdk-8u73-linux-x64.tar.gz 


unzip sqldeveloper-4.1.3.20.78-no-jre.zip
cd sqldeveloper
chmod +x sqldeveloper.sh
./sqldeveloper.sh

When you see below message, give the location where you have extracted jdk:

"Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored under your home folder.in /home/rajiv/.sqldeveloper/4.1.0/product.conf"

For eg in my box I gave below location (where i have extracted jdk)
/scratch/rajiv/sqldeveloper/jdk1.8.0_73

Enable remote debugging

Connect as sys and grant below privileges.

grant DEBUG CONNECT SESSION to SCHEMA_NAME;
grant DEBUG ANY PROCEDURE to SCHEMA_NAME;

SCHEMA_NAME is the db user name to which you want to connect from SQL Developer for debugging (ex: SCOTT)

Run below anonymous block to add the client host to ACL

--add ip address of the host where sql developer is running
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => 'IP_ADDRESS',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
principal_name => 'SCHEMA_NAME',
principal_type => xs_acl.ptype_db)
);
END;

Otherwise below error is thrown while trying to connect to debugger port.

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1

You need to compile the PL/SQL package or procedure using "Compile for Debug" option first.
Now you can set breakpoints in the PL/SQL code. And then right click the procedure name and select "Debug" option. This will show a popup, where you need to provide some value for the input parameters and hit "OK".

Rajiv Kuriakose

Oracle Data Pump Example

How to export and import table using Data Pump

This example illustrates how to export a table from one schema and import it into another schema.

Create a directory on the host where database is running.

$ mkdir -p /scratch/dtpump

Use this dir to create directory in database. Data pump will export data to this folder.

Connect as sys or system user and create directory

create directory dtpump as '/scratch/dtpump';

--create a user and sample table to try export

create user rajiv identified by welcome1;
grant connect,resource to rajiv;
grant read,write on directory dtpump to rajiv;

Create Table and insert sample data. This table will be exported.

create table test( name varchar2(100));
insert into  test values('rajiv');
insert into  test values('venkat');
commit;

Enter bash

$bash

Set below environment variables

ORACLE_HOME=/scratch/oracledb/base/rdbms/12.1.0.2/;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH

CON_STR=rajiv/welcome1@//hostname:1521/orcl.xyz.com; export CON_STR

Export:

expdp $CON_STR tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log

got below error message

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "RAJIV.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1048
ORA-01950: no privileges on tablespace 'USERS'

Solution:

Connect to sys or system user and run below command.

SQL> ALTER USER rajiv quota unlimited on users;

Try again:

bash-4.1$ expdp $CON_STR tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log

Export: Release 12.1.0.2.0 - Production on Mon Jan 25 07:32:28 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "RAJIV"."SYS_EXPORT_TABLE_01":  rajiv/********@//hostname.xyz.com:1521/orcl.xyz.com tables=TEST directory=dtpump dumpfile=TEST.dmp logfile=TEST.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "RAJIV"."TEST"                              5.531 KB       2 rows
Master table "RAJIV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJIV.SYS_EXPORT_TABLE_01 is:
  /scratch/dtpump/TEST.dmp
Job "RAJIV"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 25 15:32:37 2016 elapsed 0 00:00:08

Import data 

Create another user to import table

create user rk identified by welcome1;
grant connect,resource to rk;
grant read,write on directory dtpump to rk;
ALTER USER rk quota unlimited on users;


Prepare a par file


bash-4.1$ cat dp1.par

directory=dtpump
dumpfile=TEST.dmp
remap_schema=rajiv:rk

Import

bash-4.1$ impdp rk/welcome1@//hostname.xyz.com:1521/orcl.xyz.com  parfile=dp1.par

Import: Release 12.1.0.2.0 - Production on Mon Jan 25 07:37:29 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

Solution:
--connect as system or sys user and grant below privilege
grant IMP_FULL_DATABASE to rk;

Try again:

bash-4.1$ impdp rk/welcome1@//hostname.xyz.com:1521/orcl.xyz.com  parfile=dp1.par

Import: Release 12.1.0.2.0 - Production on Mon Jan 25 07:44:10 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "RK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "RK"."SYS_IMPORT_FULL_01":  rk/********@//hostname.zyx.com:1521/orcl.zyz.com parfile=dp1.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RK"."TEST"                                 5.531 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RK"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 15:44:22 2016 elapsed 0 00:00:12