Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle SQLcl and Data Pump

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).

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

December 13, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

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!