Download SQLcl 21.4

First things first, SQLcl DOES NOT use log4j – so there are no security vulnerabilities in THIS or ANY version of SQLcl.

This version of SQLcl offers a variety of bug fixes and one new major feature: Data Pump command support.

Brian and I take you on a quick overview of Data Pump in SQLcl.

Want a shorter, sweeter version w/o Brian and I going on and on with PPT?

Here’s a 5 minute version from the indomitable Oracle-Base.

Tim does more than videos, he also has a little site called Oracle-Base you may have heard of before. He has a very nice series of articles on SQLcl, and so of course he also covers Data Pump in more detail there as well.

We hate typing, so you can shorten ‘datapump’ to ‘dp.’ And all of the command parameters have short name options as well.

Example: dp export -s HRREST,HR2 -f some_of_the_hrs3.dmp -lf hrs4.log -j HRREST_HR2_EXPORT_JOB_4

Make it as simple as possible

To export your current schema, simply do:

SQL> dp export
 
** Datapump Command START ** at 2021.12.16-13.50.01
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:32 CON_ID:0
Log Location: DATA_PUMP_DIR:ESQL_3202.LOG
Starting "HRREST"."ESQL_3202":
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/STATISTICS/MARKER
Processing object TYPE SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object TYPE SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object TYPE SCHEMA_EXPORT/TABLE/TABLE
Processing object TYPE SCHEMA_EXPORT/TABLE/COMMENT
Processing object TYPE SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object TYPE SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object TYPE SCHEMA_EXPORT/VIEW/VIEW
Processing object TYPE SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object TYPE SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object TYPE SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object TYPE SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HRREST"."UNTAPPD"                          805.4 KB    2168 ROWS
. . exported "HRREST"."all_the_hrs_1"                    900.9 KB    1773 ROWS
. . exported "HRREST"."HRREST_HR2_EXPORT_JOB_1"          988.7 KB    1771 ROWS
. . exported "HRREST"."ESQL_3082"                        560.7 KB    1719 ROWS
. . exported "HRREST"."EMPLOYEES"                        17.08 KB     107 ROWS
. . exported "HRREST"."LOCATIONS"                        8.437 KB      23 ROWS
. . exported "HRREST"."JOB_HISTORY"                      7.195 KB      10 ROWS
. . exported "HRREST"."JOBS"                             7.101 KB      19 ROWS
. . exported "HRREST"."DEPARTMENTS"                      7.125 KB      27 ROWS
. . exported "HRREST"."COUNTRIES"                        6.367 KB      25 ROWS
. . exported "HRREST"."REGIONS"                          5.546 KB       4 ROWS
Master TABLE "HRREST"."ESQL_3202" successfully loaded/unloaded
******************************************************************************
Dump file SET FOR HRREST.ESQL_3202 IS:
  /u01/app/oracle/admin/orclcdb/dpdump/8A34DEF16CD55C76E0530100007F040C/ESQL_3202.DMP
Job "HRREST"."ESQL_3202" successfully completed at Thu DEC 16 13:50:52 2021 elapsed 0 00:00:49
DataPump Operation STATUS 'COMPLETED'
Jobname = ESQL_3202
** Datapump Command END ** at 2021.12.16-13.50.54
SQL>

There is so much more to talk about for this feature, but we will cover those in 2022 🙂

Wait, one last trick

I like this feature so much, I need to show it now.

Don’t run anything, just generate the code for me please.

Just use the -noexec or -ne parameter.

Wait, one final last trick

Don’t forget you can monitor and check up on your Data Pump jobs in SQL Developer Web – plus download those dmp files!

Data Pump in SQL Developer Web.

Sorry, just one more thing

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment