I use the SQLcl datapump (or dp) command which is a wrapper around the DBMS_DATA_PUMP API (ref: https://docs.oracle.com/en/database/oracle/sql-developer-command-line/26.2/sqcug/data-pump-command.html ).
One of the issues with using this command is that the “_job tables_” (which defaults in name to ESQL_<n> | ISQL_<n> unless overridden with the -jobname argument) are never automatically removed. And over time, without manual management, these pile up.
I don't believe that there is any mention in the online documentation that the user should expect this to happen and needs to manually remove these tables.
Consequently, I think this is maybe a bug – unless Oracle can confirm that this is the intended behavior.
This is reproducible in a number of database and SQLcl version. For demonstration versions, I've tested with the latest versions of both:
- Oracle AI Database 26ai Enterprise Edition Release 23.26.3.1.0
- Oracle SQLDeveloper Command-Line (SQLcl) version: 26.2.0.0 build: 26.2.0.181.2110
Full details in a reproducible test case
First, steps for setting up a test user/schema with a basic table:
Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.3.1.0 - Production
Version 23.26.3.1.0
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 26.2.0.0 build: 26.2.0.181.2110
SQL> -- STEP 1: Create a new user for testing
SQL> CREATE USER IF NOT EXISTS u1 IDENTIFIED BY **************** DEFAULT TABLESPACE users QUOTA 100m ON users;
User U1 created.
SQL> GRANT create session, create table TO u1;
Grant succeeded.
SQL> GRANT read,write ON DIRECTORY data_pump_dir TO u1;
Grant succeeded.
SQL> -- STEP 2: Connnect as the new user
SQL> connect u1/****************@****************
Connected.
SQL> show user
USER is "U1"
SQL>
SQL> -- STEP 3: Create a table with a few rows of data as the new test user
SQL> CREATE TABLE IF NOT EXISTS u1.t1 (c1 NUMBER);
Table U1.T1 created.
SQL> INSERT INTO t1 VALUES (1),(2),(3);
3 rows inserted.
SQL> commit;
Commit complete.
SQL
Next, run some test Data Pump commands from SQLcl to illustrate the problem with both export and import:
SQL> show user
USER is "U1"
SQL>
SQL> -- STEP 4: Test data pump export
SQL> dp export -dumpfile test.dmp -includelist table
Initiating DATA PUMP
Database Time Zone: VERSION:45 CON_ID:0
Log Location: DATA_PUMP_DIR:ESQL_204.LOG
Starting "U1"."ESQL_204":
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "U1"."T1" 5.1 KB 3 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "U1"."ESQL_204" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.ESQL_204 is:
/u03/dbfs/56E70C863E6FAF2AE0638717000A86E3/data/dpdump/test.dmp
Job "U1"."ESQL_204" successfully completed at Mon Jul 20 19:03:49 2026 elapsed 0 00:00:13
DataPump Operation Status 'COMPLETED'
Jobname = ESQL_204
SQL>
SQL> tables
TABLES
___________
T1
ESQL_204
SQL>
SQL> -- STEP 5: Run another data pump export
SQL> dp export -dumpfile test.dmp -includelist table
Initiating DATA PUMP
Database Time Zone: VERSION:45 CON_ID:0
Log Location: DATA_PUMP_DIR:ESQL_206.LOG
Starting "U1"."ESQL_206":
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "U1"."ESQL_204" 296.8 KB 1599 rows
. . exported "U1"."T1" 5.1 KB 3 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "U1"."ESQL_206" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.ESQL_206 is:
/u03/dbfs/56E70C863E6FAF2AE0638717000A86E3/data/dpdump/test.dmp
Job "U1"."ESQL_206" successfully completed at Mon Jul 20 19:04:47 2026 elapsed 0 00:00:12
DataPump Operation Status 'COMPLETED'
Jobname = ESQL_206
SQL>
SQL> tables
TABLES
___________
ESQL_206
T1
ESQL_204
SQL>
SQL> -- Step 6: Test a data pump import
SQL> dp import -dumpfile test.dmp -tableexists truncate
Initiating DATA PUMP
Database Time Zone: VERSION:45 CON_ID:0
Log Location: DATA_PUMP_DIR:ISQL_210.LOG
Master table "U1"."ISQL_210" successfully loaded/unloaded
Starting "U1"."ISQL_210":
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "U1"."T1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "U1"."ESQL_204" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U1"."ESQL_204" 296.8 KB 1599 rows
. . imported "U1"."T1" 5.1 KB 3 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "U1"."ISQL_210" successfully completed at Mon Jul 20 19:07:52 2026 elapsed 0 00:00:10
DataPump Operation Status 'COMPLETED'
Jobname = ISQL_210
SQL>
SQL> tables
TABLES
___________
ESQL_206
ISQL_210
T1
ESQL_204
SQL>
As per the above easy to reproduce test steps, the job tables are never automatically removed. Making it somewhat difficult to use SQLcl dp commands in scripts, etc (as they'd need to parse the output to determine the job table name and then remove the tables carefully vs simply relying of the command's return code to determine succesful completion.)
Is this a SQLcl bug?