Slide 1
Slide 1 text
Jeff Smith
1
Oracle SQL Developer Web
Database Actions
Distinguished Product Manager
[email protected]
https://www.thatjeffsmith.com || @thatjeffsmith
Slide 2
Slide 2 text
• SQL Worksheet
• Charts
• Schema Browser
• Performance & Instance Management
• Diagramming/Dictionary Reports
• JSON Document IDE
• REST IDE
• User Management
• Data Loading
• Scheduling
• APEX Workspace Management
2
SQL Developer Web
The power of SQL Developer…in your browser
Slide 3
Slide 3 text
How It Works
• Delivered via ORDS Plugin
• HTML, JS, Oracle JET
• DB User Authentication via
REST Enabled Schemas
3
Slide 4
Slide 4 text
Installation & Configuration
• Install ORDS
• Say “YES” to SQL Developer Web
Video Tutorial:
https://www.youtube.com/watch?v=1LBmLTe8sIc
4
You can use SQL Developer anywhere you can run ORDS
Slide 5
Slide 5 text
SQLDev Web & Autonomous Database
• Service Console
• ADMIN SSO
• Updated Quarterly
• ORDS is managed
by Oracle
5
Slide 6
Slide 6 text
Database Logins
• Database User/Password
• REST Enable SCHEMAs
• ADMIN enabled by
default in Autonomous DB
6
ORDS API – ENABLE SCHEMA
Slide 7
Slide 7 text
Generic Login URL
/ords/sql-developer
7
Slide 8
Slide 8 text
Database Actions
• “Launchpad”/Home
• Access to features
determined by:
• Environment
• Version of Oracle
• Database USER privs
8
Slide 9
Slide 9 text
Usability Features: Mega Menu
9
Slide 10
Slide 10 text
Help & Docs
Help
10
In-App Help Oracle Docs
Slide 11
Slide 11 text
Guided On-Screen Tours
Application Tour
11
Slide 12
Slide 12 text
Language (9) Support
12
Slide 13
Slide 13 text
SQL
• Queries & Scripts
• Tuning
• Imports & Exports
• Editor Assistance
• Formatting
• Insight
• Parser
• SQL History
• Command Palete
13
Slide 14
Slide 14 text
SQL
• Same Keyboard Shortcuts,
e.g., CTRL+ENTER
• Grid or text presentation
• SQL*Plus Script Engine Support
• Quick Download Popular Formats
• CSV, JSON, XML, TEXT
14
Query Execution
Slide 15
Slide 15 text
SQL: full featured editor
15
Slide 16
Slide 16 text
Data Grids
• Click to Sort
• Single Record View
• Advanced Export
• Type Viewers
16
Slide 17
Slide 17 text
Grids: Single Record View
17
Columnar vs Row Display
Slide 18
Slide 18 text
Grids: Manage Columns & Sorts
Hide & Reorder Columns & Apply Multiple Sort Conditions
18
Slide 19
Slide 19 text
Grid: Type Viewers
Click Cell Pencil to Activate
19
Dates/Timestamps
Intervals
BLOBs
Slide 20
Slide 20 text
Grid: Exports
Right Click, Export
20
Slide 21
Slide 21 text
Worksheet - Queries
• Automatic Formatting
• SQL*Plus Style Reports
• Same shortcut as
desktop (F5)
21
Script Execution
Slide 22
Slide 22 text
Worksheet: Explain Plan & AutoTrace
22
Slide 23
Slide 23 text
Worksheet Assistance: SQL History
Previous Queries/Scripts Executed
23
Slide 24
Slide 24 text
Worksheet Assistance: Insight
CTRL+Space to Invoke
24
Slide 25
Slide 25 text
Worksheet Assistance: Parser
Mouse Over Gutter or Grammar Squiggles for Advice
25
Slide 26
Slide 26 text
Worksheet Assistance: Formatter
26
Before
After
Slide 27
Slide 27 text
Worksheet: Browser
27
Slide 28
Slide 28 text
Worksheet: Browser
• Search
• Context menus
• Drag ‘n’ Drop
28
All Objects: Fuzzy Search
Slide 29
Slide 29 text
Worksheet: Browser
PL/SQL – Browse and Generate Anon Exec Blocks
29
Slide 30
Slide 30 text
Worksheet: Browser
Create Table
30
Video Demonstration
Slide 31
Slide 31 text
Worksheet: Browser
Edit Table
31
Slide 32
Slide 32 text
Worksheet: Browser
Import data to NEW or Existing TABLEs
+ JSON
+ XML
+ AVRO
+ CSV
+ Excel
32
Slide 33
Slide 33 text
Worksheet: Browser
33
Importing JSON to a Relational Table
Slide 34
Slide 34 text
Charting
34
Take SQL query results and create rich HTML charts | combine for dashboards
Slide 35
Slide 35 text
Creating a Chart
35
Just supply the SQL
Slide 36
Slide 36 text
Dashboards
36
Take your favorite charts and combine in any order/grouping you want
Slide 37
Slide 37 text
Charting: Getting Started
37
Promote Query from SQL worksheet
Slide 38
Slide 38 text
User Management
38
Slide 39
Slide 39 text
User Management, Create/Edit Users
39
Slide 40
Slide 40 text
Instance Viewer
40
Slide 41
Slide 41 text
Instance Viewer, Memory/Storage
41
Slide 42
Slide 42 text
Activity/DBA
• DB Status
• Storage
• Sessions
• Waits
• Users
• Expired PWDs
• Alerts
• Top SQL
42
Instance Overview and Performance Pages
Slide 43
Slide 43 text
Activity/DBA
Storage Drill Down
43
Slide 44
Slide 44 text
Activity/DBA
Storage Drill Down/View Data Files
44
Slide 45
Slide 45 text
Activity/DBA
Sessions Drill Down
45
Slide 46
Slide 46 text
Activity/DBA
Alerts Drill Down
46
Slide 47
Slide 47 text
Activity/DBA
TOP SQL Drill Down
47
Slide 48
Slide 48 text
DBA
Parameters
48
Slide 49
Slide 49 text
Activity/DBA
Performance Hub
49
Slide 50
Slide 50 text
Data Modeler
Reverse Engineer/Diagrams from your SCHEMA
50
Video Demonstration
Slide 51
Slide 51 text
Data Modeler
Automatic and STAR Schema Layouts
51
Slide 52
Slide 52 text
Data Modeler
Implied Foreign Keys
52
Slide 53
Slide 53 text
Data Modeler
Customize Objects, Relationships, Colors
53
Slide 54
Slide 54 text
Data Modeler
DDL Generation
54
Slide 55
Slide 55 text
Data Modeler
Data Dictionary Reports
55
Slide 56
Slide 56 text
Data Modeler
• Save Diagrams (stored in local database TABLE)
• Search Diagrams, Preview before opening
56
Managing Your Diagrams
Slide 57
Slide 57 text
Data Modeler
• Open Diagrams from Oracle SQL Developer Data Modeler
• And Vice Versa!
57
Desktop Integration
Built in SQL Developer Web Built in SQL Developer Data Modeler (Desktop)
Slide 58
Slide 58 text
REST
58
Full RESTful Web Service Development Interface
Slide 59
Slide 59 text
REST Dashboard
59
Slide 60
Slide 60 text
Browse, Edit, Create Modules
60
Slide 61
Slide 61 text
Cards vs Grids
61
All browsers supports both views
Slide 62
Slide 62 text
Templates
62
Slide 63
Slide 63 text
Handlers
63
Slide 64
Slide 64 text
REST IDE
64
Slide 65
Slide 65 text
Parameters
65
Slide 66
Slide 66 text
OAUTH2
66
Slide 67
Slide 67 text
Creating OAUTH2 Clients
67
Slide 68
Slide 68 text
Documentation & Testing
68
Built-in OpenAPI3 {swagger} clients & doc
Slide 69
Slide 69 text
JSON
69
JSON Document Store – Create collections, manage documents, query, data guides
Slide 70
Slide 70 text
JSON – Add Document
70
Slide 71
Slide 71 text
JSON Editor
71
Slide 72
Slide 72 text
JSON – Filtering (Query by Example)
72
Slide 73
Slide 73 text
JSON Filtering, get the SQL!
73
Slide 74
Slide 74 text
JSON Data Guide (Diagram)
74
Slide 75
Slide 75 text
JSON – Create INDEX or VIEW
75
Slide 76
Slide 76 text
Data Pump
76
Monitor jobs, check logs, download DMP files
Slide 77
Slide 77 text
Scheduling
77
Reporting for DBMS_SCHEDULER, Job Forecasting, & Chain Diagramming
Slide 78
Slide 78 text
APEX Workspace Management
78
Create, Delete, Get URL, Navigate to SCHEMA
Slide 79
Slide 79 text
Watch the Movie
79
50 Minute Video Tour
https://www.youtube.com/watch?v=szqH2r-6KzU
Slide 80
Slide 80 text
Thank You!
Questions? Contact me!
Jeff Smith
Email: [email protected]
Twitter: @thatjeffsmith
Blog: https://www.thatjeffsmith.com
80