Oracle SQL Developer

✍ Scribed by Narayanan, Ajith

• Publisher: Packt Publishing, Limited
• Year: 2016
• Tongue: English
• Leaves: 578
• Category: Library

✦ Synopsis

Learn Database design, development, and administration using the feature-rich SQL Developer 4.1 interfaceAbout This Book Explore all the SQL Developer 4.1 features useful for Oracle database developers, architects, and administrators Understand how this free tool from Oracle has evolved over the years and has become a complete tool that makes life easy for Oracle and third-party database users The author, Ajith Narayanan, has a total of 10+ years of work experience as an Oracle [APPS] DBAWho This Book Is ForThis book is intended for Oracle developers who are responsible for database management. You are expected to have programming knowledge of SQL and PL/SQL, and must be familiar with basic Oracle database concepts. What You Will Learn Install and navigate through all the advanced features of SQL Developer that were introduced in version 4.1 Browse, create, edit, and delete (drop) database objects Use the SQL worksheet to run SQL statements and scripts, edit and debug PL/SQL code, manipulate and export (unload) data Carry out all DBA-related activities such as exporting/importing, tuning, and analyzing database performance issues Quickly analyze, create, and edit the data model using data modeler Extend the SQL developer capabilities by exploring the APEX related pages, enabling and working with RESTful services Use the available reports and create new custom reports with custom scripts Grasp how to connect to third-party databases and work smoothly with themIn DetailAt times, DBAs support 100s of databases at work. In such scenarios, using a command-line tool like putty adds to the difficulty, while SQL Developer makes the life of a developer, DBA, or DB architect easier by providing a graphical user interface equipped with features that can bolster and enhance the user experience and boost efficiency. Features such as DBA panel, Reports, Data Modeler, and Data Miner are just a few examples of its rich features, and its support for APEX, REST Services, timesten, and third-party database drivers demonstrate its extensibility. You may be a newbie to databases or a seasoned database expert, either way this book will help you understand the database structure and the different types of objects that organize enterprise data in an efficient manner. This book introduces the features of the SQL Developer 4.1 tool in an incremental fashion, starting with installing them, making the database connections, and using the different panels. By sequentially walking through the steps in each chapter, you will quickly master SQL Developer 4.1. Style and approachThis book follows a step-by-step approach and is in a conversational and easy-to-follow style. Screenshots, and detailed explanations of the basic and advanced features of SQL Developer 4.1 that will make your work and life easy.;Cover -- Copyright -- Credits -- About the Author -- About the Reviewer -- www.PacktPub.com -- Table of Contents -- Preface -- Chapter 1: Getting Started with SQL Developer 4.1 -- Preparing your environment -- Finding and downloading the software -- Downloading and installing the Java Development Kit -- Installing and starting SQL Developer -- Working with different platforms -- Migrating settings from a previous release -- Maintaining your environment -- Verifying the current release -- Using Check for Updates -- Managing the system folder and other files -- Alternative installations of SQL Developer -- Oracle JDeveloper -- Oracle Database 12c -- Troubleshooting -- A quick overview -- Sample schemas -- Creating your first connection -- Using basic commands in the SQL Worksheet -- Browsing and updating data -- Running reports -- Navigating around SQL Developer -- Managing SQL Developer windows -- Tiling windows -- Splitting documents -- Maximizing detail -- Resetting the window layout -- Database certification matrix (Oracle & Third-Party) -- Finding more help -- Summary -- Chapter 2: Database Connections and SQL Worksheet -- Working with the Connections navigator -- Opening connections -- Working with database objects -- Display editors -- General display editors -- Working with the display editors -- Using the SQL display editor -- Working with the data grids -- Reviewing other database object nodes -- Working with PL/SQL objects -- Accessing objects you don't own -- Other users -- Synonyms and filters -- Recycle Bin -- Creating new database objects -- Editing database objects -- putting context menus to work -- Editing objects -- Script Runner/Running scripts -- Execution plan -- Autotrace pane -- DBMS Output pane -- OWA output pane -- Query Builder -- Command-line interface for SQL formatting -- SQL Worksheet "hints" for formatting output.

✦ Table of Contents

Oracle SQL Developer......Page 19
Credits......Page 21
About the Author......Page 23
About the Reviewer......Page 25
www.PacktPub.com......Page 27
Support files, eBooks, discount offers, and more......Page 29
Why subscribe?......Page 30
Free access for Packt account holders......Page 31
Instant updates on new Packt books......Page 32
Preface......Page 33
What this book covers......Page 35
What you need for this book......Page 37
Who this book is for......Page 39
Conventions......Page 41
Reader feedback......Page 43
Customer support......Page 45
Downloading the example code......Page 47
Downloading the color images of this book......Page 48
Errata......Page 49
Piracy......Page 50
Questions......Page 51
1. Getting Started with SQL Developer 4.1......Page 52
Preparing your environment......Page 54
Downloading and installing the Java Development Kit......Page 55
Microsoft Windows......Page 57
Migrating settings from a previous release......Page 58
Maintaining your environment......Page 60
Verifying the current release......Page 61
Using Check for Updates......Page 62
Managing the system folder and other files......Page 63
Troubleshooting......Page 65
Resetting shortcut keys......Page 66
Reset the environment, do not reinstall the product......Page 67
A quick overview......Page 69
Sample schemas......Page 71
Creating your first connection......Page 72
Using basic commands in the SQL Worksheet......Page 74
Browsing and updating data......Page 76
Running reports......Page 77
Navigating around SQL Developer......Page 79
Managing SQL Developer windows......Page 81
Tiling windows......Page 82
Splitting documents......Page 83
Maximizing detail......Page 84
Resetting the window layout......Page 85
Database certification matrix (Oracle & Third-Party)......Page 87
Finding more help......Page 89
Summary......Page 91
2. Database Connections and SQL Worksheet......Page 93
Working with the Connections navigator......Page 95
Opening connections......Page 96
Working with database objects......Page 98
Display editors......Page 100
General display editors......Page 102
Working with the display editors......Page 104
Using the SQL display editor......Page 105
Working with the data grids......Page 106
Reviewing other database object nodes......Page 109
Working with PL/SQL objects......Page 111
Synonyms and filters......Page 112
Recycle Bin......Page 113
Creating new database objects......Page 114
Editing database objects – putting context menus to work......Page 117
Editing objects......Page 119
Script Runner/Running scripts......Page 120
Execution plan......Page 123
Autotrace pane......Page 125
DBMS Output pane......Page 127
OWA output pane......Page 130
Query Builder......Page 132
Command-line interface for SQL formatting......Page 133
SQL Worksheet "hints" for formatting output......Page 135
Summary......Page 137
3. The Power of SQL Reports......Page 139
Introducing SQL Developer reports......Page 141
Who can run reports?......Page 142
When do you use reports?......Page 143
Tree layout for ease of navigation......Page 144
Privileges required for running reports......Page 145
Getting to know the data dictionary......Page 147
About the database......Page 148
Assisting with quality assurance......Page 149
Using PL/SQL reports......Page 150
Running ASH and AWR reports......Page 151
More report categories......Page 152
The Monitor Sessions report......Page 153
Managing the database......Page 154
Real-time SQL monitoring......Page 155
Creating your own reports......Page 158
Creating folders......Page 160
Storing reports......Page 161
Building general tabular reports......Page 162
Adding bind variables......Page 163
Creating a drill-down report......Page 165
Creating master-detail reports......Page 168
Adding charts......Page 171
Importing and exporting......Page 174
Sharing reports through user-defined extensions......Page 175
DB Doc generation......Page 176
Summary......Page 179
4. Working with PL/SQL......Page 181
Creating, compiling, and debugging PL/SQL......Page 183
Using code insight......Page 184
Using code snippets and code templates......Page 185
Using the Create Trigger dialog......Page 187
Creating INSTEAD OF triggers......Page 189
Controlling triggers......Page 190
Adding functions or procedures......Page 192
Working with errors......Page 193
Creating the body......Page 195
Refactoring code......Page 197
Finding a DB Object......Page 200
Using the debugging mechanism in SQL Developer......Page 201
Debugging......Page 202
Working with Explain Plan......Page 205
Controlling the Explain Plan output......Page 206
Execution plan details......Page 207
Running the Top SQL reports......Page 208
Summary......Page 210
5. SQL Developer for DBAs......Page 212
Database instance viewer......Page 214
Finding database objects......Page 217
Database configuration – initialization parameters......Page 219
Automatic undo management......Page 221
Current database properties......Page 223
Restore points......Page 225
Data Pump......Page 227
Export Jobs......Page 229
Import Jobs......Page 231
Performance......Page 233
Snapshots (filtered)......Page 235
Baselines......Page 237
Baseline templates......Page 239
Automatic Database Diagnostic Monitor (ADDM)......Page 241
ASH Report Viewer......Page 243
AWR......Page 245
RMAN Backup/Recovery......Page 248
Backup jobs......Page 250
Backup sets......Page 251
Image copies......Page 252
RMAN settings......Page 253
Scheduled RMAN actions......Page 254
Resource manager......Page 255
Consumer group mappings......Page 257
Consumer groups......Page 259
Plans......Page 261
Settings......Page 263
Statistics......Page 265
Scheduler......Page 267
Global Attributes......Page 269
Job Classes......Page 271
External Destinations......Page 273
Security......Page 275
Audit Settings......Page 277
Profiles......Page 279
Roles......Page 281
Users......Page 283
Storage......Page 285
Archive Logs......Page 287
Control Files......Page 289
Datafiles......Page 291
Redo Log Groups......Page 293
Rollback Segments......Page 295
Tablespaces......Page 297
Temporary Tablespace Groups......Page 299
Summary......Page 301
6. SQL Developer Accessibility......Page 303
Screen reader readability......Page 305
Keyboard access......Page 306
Font and color choices......Page 311
No audio-only feedback......Page 313
Screen magnifier usability......Page 315
Change the editor or tabbed view of a file......Page 317
Read text in a multi-line edit field......Page 319
Read the line number in the source editor......Page 321
Customize the accelerators keys......Page 323
The look and feel of SQL Developer......Page 325
Customize syntax highlighting......Page 329
Display line numbers in Code Editor......Page 331
Timing for completion insight......Page 333
Specify the columns in the Debugger......Page 335
Summary......Page 337
7. Importing, Exporting, and Working with Data......Page 339
Exporting data......Page 341
Exporting instance data......Page 342
Exporting table data......Page 344
Setting up the export file......Page 345
Exporting to HTML......Page 347
Supporting export for SQL*Loader......Page 348
Exporting to XML......Page 349
Selecting multiple tables for DDL export......Page 351
Starting the export wizard......Page 353
Specifying objects......Page 354
Running the script......Page 355
Importing data......Page 357
Importing data from SQL script files......Page 359
Importing data from XLS and CSV files......Page 360
Creating a table on XLS import......Page 364
Summary......Page 365
8. Database Connections and JDBC Drivers......Page 367
Working with Oracle connections......Page 369
Using alternative Oracle connection types......Page 370
Accessing the tnsnames.ora file......Page 371
Accessing LDAP server details......Page 373
Creating advanced connections with JDBC URLs......Page 374
Connecting to Oracle TimesTen......Page 375
Fixing connection errors......Page 377
OS Authentication......Page 378
Creating a connection using OS Authentication......Page 379
Using proxy authentication......Page 380
Kerberos authentication using the thin JDBC driver......Page 382
Creating non-Oracle database connections......Page 385
Using Check for Updates......Page 387
Manually adding JDBC drivers......Page 388
Summary......Page 390
9. Introducing SQL Developer Data Modeler......Page 392
Oracle SQL Developer Data Modeler......Page 394
Integrated architecture......Page 395
Getting started......Page 397
Oracle clients and JDBC drivers......Page 399
Creating your first models......Page 400
Creating a database connection......Page 402
Using the Import Wizard......Page 403
Reviewing the results......Page 404
Working with diagrams and their components......Page 406
Formatting the elements......Page 408
Adjusting the width and height across the model......Page 409
Analysis, design, and generation......Page 411
Building the relational model......Page 413
Creating an ERD......Page 414
Creating entities......Page 415
Adding attributes......Page 416
Working with relationships......Page 417
Creating domains......Page 419
Using domains to implement check constraints......Page 420
Forward engineering......Page 422
Reverse engineering models......Page 423
Creating relational models......Page 424
Importing a schema from the Data Dictionary......Page 425
Generating the DDL......Page 426
Creating a new model......Page 427
Summary......Page 428
10. Extending SQL Developer......Page 430
Introducing extensibility......Page 432
Extensibility in SQL Developer......Page 433
Who builds extensions?......Page 434
Why extend?......Page 435
SQL Developer XML extension types......Page 436
Adding an XML extension......Page 437
Sharing user-defined reports......Page 438
Building the XML file for a display editor......Page 440
Working with context menus......Page 443
Adding a context menu to the connections menus......Page 444
Creating a utility using context menus......Page 445
Including the user-defined extension for context menus......Page 447
Including user-defined extensions for a navigator node......Page 449
Adding a new tree to the navigator......Page 450
Working with extensions......Page 454
Removing extensions......Page 456
Sharing extensions......Page 457
Summary......Page 458
11. Working with Application Express......Page 460
Setting the scene......Page 462
Creating a workspace and database schema......Page 463
Creating an Application Express user......Page 464
Browsing and working with schema objects......Page 466
Browsing the applications......Page 467
Drilling down into lists of values......Page 468
Focusing on pages......Page 469
Mapping objects from SQL Developer to Application Express......Page 470
Tuning with Explain Plan......Page 471
Working with PL/SQL code......Page 472
Replacing the anonymous block in Application Express......Page 474
Importing applications......Page 476
Modifying applications......Page 477
Controlling services......Page 478
Reporting on applications using SQL Developer......Page 479
Summary......Page 480
12. Working with SQL Developer Migrations......Page 482
Introducing SQL Developer Migrations......Page 484
An overview of the migration process......Page 485
Offline or online migration choices......Page 486
Supported third-party databases......Page 487
Setting up your environment......Page 488
Setting up JDBC drivers......Page 490
Creating third-party connections......Page 491
Browsing database objects......Page 492
Creating the repository......Page 493
Associating a repository with a user......Page 494
Setting up the target database connection......Page 495
Migrating......Page 496
Using Quick Migrate......Page 498
The migration......Page 501
Reviewing the logs......Page 503
Generating scripts......Page 504
Migration reports......Page 507
Summary......Page 508
13. Oracle Data Miner 4.1......Page 510
Data source node......Page 512
Creating a new project......Page 515
Creating a new workflow......Page 516
Addition of nodes to the workflow......Page 517
Link nodes......Page 519
Run nodes......Page 520
View reports......Page 521
JSON data type......Page 522
JSON Query node......Page 524
The APIs for data mining......Page 525
Summary......Page 527
14. REST Data Services and REST Development......Page 529
About Oracle REST Data Services......Page 531
The Oracle REST Data Services Administration toolbar and context menu......Page 537
Connecting to Oracle REST Data Services......Page 540
REST Data Services navigator – global and database settings......Page 541
About RESTful services......Page 543
RESTful services terminology......Page 545
RESTful services requirements and setup......Page 546
Summary......Page 547
Index......Page 549