Oracle SQLcl and Data Pump

Easily create Export & Import Data Pump jobs using the Oracle SQLcl command line interface, but also easily copy your export dmp file(s) to the Oracle Object Store in the Oracle Cloud for importing into your Oracle Cloud databases.

Video presentation: https://www.youtube.com/watch?v=xnbMsdbyFwg
Download SQLcl: https://www.oracle.com/tools/downloads/sqlcl-downloads.html

The 'db' datapump command is available in version 21.4 of SQLcl (Dec 2021).

Transcript

1. SQL Developer: The Next Generation Number One & Number Two Jeff Smith & Brian Spendolini Copyright © 2021, Oracle and/or its affiliates 1
2. Our 16 year Mission... Explore strange new databases Seek out new platforms and applications To boldly go where SQL*Plus never went before To easily copy data from one database to another, Cloud or otherwise. Copyright © 2021, Oracle and/or its affiliates 2
3. SQL Developer launches in 2005 • SQL scratchpad • PL/SQL IDE • Schema browsing • Database Administration • Data modeling • REST • 3rd party DBMS migration to ORCL • TimesTen, Data Miner, OLAP, … https://www.oracle.com/sqldeveloper Used by over 5 million database pros
4. Data Pump Wizard: Copy to OSS/Import
5. Jeff Smith
6. SQLcl • Modern command-line interface • Auto complete SQL syntax • Command history • Output json, csv, html, inserts, xml… • Liquibase schema versioning • Scripting friendly • No Oracle Home required • OCI & OSS Integration https://www.oracle.com/database/technologies/appdev/sqlcl.html SQL Command Line
7. Ridiculously Easy to Get Started • Installed by default with Oracle Database • Installed by default in Oracle CloudShell • yum & brew • https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
8. Data Pump • Client • DBMS_DATAPUMP PL/SQL API • DBMS_CLOUD integration • Interfaces in SQL Developer & SQL Developer Web • ORDS & DB API • OEM • …
9. SQL Developer Web • SQL Worksheet • Load data • Monitor and manage your DB • Schema diagramming • REST development interface • JSON Document Store (SODA) • User management • … and, Data Pump!!! https://www.oracle.com/database/technologies/appdev/db-actions.html
10. Monitor jobs, check logs, download DMPs
11. Oracle REST Data Services • Microservices & REST APIs for your database • Declaratively defined using SQL, PL/SQL • Desktop, CLI, Browser, and PL/SQL interfaces • Auto REST enable tables, views, stored procedures • JSON Document Store (SODA for REST) • Database Management REST APIs • REST Enabled SQL Service • Type III JDBC Driver https://www.oracle.com/rest HTTPS Results JSON SQL Meta Data Repo
12. ORDS DBAPI & Data Pump
13. datapump (dp) export • Create and start job that exports current schema • DATA_PUMP_DIR • JOB Name: {schema}.ESQL_### • Output: ESQL_####.DMP ESQL_####.LOG
14. Flags, lot of flags • Need to deviate from defaults? • No problem!
15. All the options dp help syntax
16. dp help examples
17. Generate and show me the code
18. Run & Monitor
19. …and now…the rest of the story! • Live Demos • Cloud magic!