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

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  4. Data Pump Wizard: Copy to OSS/Import

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  7. Data Pump
    • Client
    • DBMS_DATAPUMP PL/SQL API
    • DBMS_CLOUD integration
    • Interfaces in SQL Developer & SQL Developer Web
    • ORDS & DB API
    • OEM
    • …

    View full-size slide

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

    View full-size slide

  9. Monitor jobs, check logs, download DMPs

    View full-size slide

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

    View full-size slide

  11. ORDS DBAPI & Data Pump

    View full-size slide

  12. datapump (dp)
    export
    • Create and start job that exports current schema
    • DATA_PUMP_DIR
    • JOB Name: {schema}.ESQL_###
    • Output: ESQL_####.DMP
    ESQL_####.LOG

    View full-size slide

  13. Flags, lot of flags
    • Need to deviate from defaults?
    • No problem!

    View full-size slide

  14. All the options dp help syntax

    View full-size slide

  15. dp help examples

    View full-size slide

  16. Generate and show me the code

    View full-size slide

  17. Run & Monitor

    View full-size slide

  18. …and now…the rest of the story!
    • Live Demos
    • Cloud magic!

    View full-size slide