Slide 1
Slide 1 text
Master Product Manager
[email protected]
@thatjeffsmith
Oracle SQL Developer
…for the DBA!
Jeff Smith
Slide 2
Slide 2 text
SQL Developer Snapshot
2
Time Tested
Launched in
2005
An Industry Standard
5M+ Oracle DBAs
& Developers
Always Improving
Quarterly
Releases
4
Slide 3
Slide 3 text
• No v20.1 (pandemic)
• v20.2, all products - Early Summer!
Roadmap / Direction
• SQL Developer Web => more features
• SQLcl => ‘fancier’ editor, Liquibase, OCI enhancements
• SQL Developer => tweaks & bug fixes as needed
2020 News
Slide 4
Slide 4 text
• SQL editor
• PL/SQL IDE
• GUI for browsing and managing database objects
• Ad Hoc reporting
• Database Design & Data Modeling
• Database Administration
• 3rd Party RDBMS Migrations to Oracle
• Deploy and Administer Oracle REST Data Services
• REST Enablement of the Database
• Command Line Interface
Major Feature Areas
Slide 5
Slide 5 text
• Config
• Multitenant
• Sharding/DG
• Status
• Data Pump
• Diagnostics Pack (AWR, ASH, ADDM)
• SQL Monitoring
• RMAN
• Resource Manager
• Storage
• Security
DBA Feature Categories
Slide 6
Slide 6 text
• Accessed from the View menu
• Add Existing Connections
• Now supports folders
• Intended for HIGHLY privileged users
Where to find these features
Slide 7
Slide 7 text
Database Configuration
Slide 8
Slide 8 text
Instance Viewer
Slide 9
Slide 9 text
SQL Developer Web
Slide 10
Slide 10 text
User Management in SDW
Slide 11
Slide 11 text
Data Pump
Slide 12
Slide 12 text
Data Pump via HTTPS (DB REST API)
• Inputs
• What directory?
• What type of export?
• How many threads?
• What are we exporting?
• Response
• Links to get status/log
Slide 13
Slide 13 text
Data Pump – Export 2 Tables in Local Schema
{
"datapump_dir": "DATA_PUMP_DIR",
"filter": "HOCKEY_STATS, UNTAPPD",
"job_mode": "TABLE",
"threads": 2
}
POST BODY Response
Slide 14
Slide 14 text
Data Pump – Response Resource Links
"links": [
…,
{
"rel": "related",
"href": "http://.../database/datapump/jobs/HR,DATAPUMP_REST_EXPORT_20190105140710/EXPDAT-2019-05-01-14_07_11.LOG"
},
{
"rel": "self",
"href": "http://.../database/datapump/jobs/HR,DATAPUMP_REST_EXPORT_20190105140710/"
}
]
Job Log File
The Job and its metadata
Slide 15
Slide 15 text
Data Pump – GET the Log File
Slide 16
Slide 16 text
Data Pump – GET the Job Details
Slide 17
Slide 17 text
Data Pump – Download the DMP!
Slide 18
Slide 18 text
Performance
Slide 19
Slide 19 text
Automatic Workload Repository (AWR)
Slide 20
Slide 20 text
Resource Manager
Slide 21
Slide 21 text
Scheduler
Slide 22
Slide 22 text
Auditing!
Configure Settings
View Audit Trails
1. Logins
2. Privs
3. Objects
4. Statements
Security
Slide 23
Slide 23 text
Storage
Slide 24
Slide 24 text
Oracle Multitenant Support
Manage Pluggable Databases
Application Containers
We have REST APIs, too!
Multitenant
Slide 25
Slide 25 text
Connected to a CDB
Slide 26
Slide 26 text
• SQL Monitor
• Monitor Sessions
• Database Diff
• Side by side comparison
• between source and target
• Reports
• Data Dictionary Views
• Quality Assurance, Grants/Privileges, Sessions
• User Defined – Customize and Share Reports
What Else?
Slide 27
Slide 27 text
Modern CLI for Oracle (SQLcl)
• 25 mb download, no Client required
• Automatic formatting
• SQL History
• Tab completion
• Built-in editor
• New commands!
• info
• Liquibase
• ddl
• load
Slide 28
Slide 28 text
Planned 20.2 Features
• DBMS_DEBUG support
• Execution plan search
• Better DDL generation
• Load command
• Formatter improvements
Slide 29
Slide 29 text
• SlideShare (2011-2019) /SpeakerDeck (2020-)
• thatjeffsmith.com
• @oraclesqldev @oraclesqlcl @oraclerest
SQLDev on
Other Resources