Oracle SQL Developer : learn database design, development, and administration using the feature-rich SQL Developer 4.1 interface

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

Learn Database design, development, and administration using the feature-rich SQL Developer 4.1 interface

About 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] DBA

Who This Book Is For

This 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 them

In Detail

At 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 approach

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

Learn Database design, development,and administration using the feature-rich SQL Developer 4.1 interface About 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] DBA Who This Book Is For This 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 them In Detail At 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 approach This 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. Annotation 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 Learn Database design, development,and administration using the feature-rich SQL Developer 4.1 interfaceKey FeaturesExplore all the SQL Developer 4.1 features useful for Oracle database developers, architects, and administratorsUnderstand 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 usersThe author, Ajith Narayanan, has a total of 10+ years of work experience as an Oracle [APPS] DBABook DescriptionAt 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.What you will learnInstall and navigate through all the advanced features of SQL Developer that were introduced in version 4.1Browse, create, edit, and delete (drop) database objectsUse the SQL worksheet to run SQL statements and scripts, edit and debug PL/SQL code, manipulate and export (unload) dataCarry out all DBA-related activities such as exporting/importing, tuning, and analyzing database performance issuesQuickly analyze, create, and edit the data model using data modelerExtend 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 scriptsGrasp how to connect to third-party databases and work smoothly with themWho 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.