You are previewing Oracle SQL Developer.
O'Reilly logo
Oracle SQL Developer

Book Description

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.

    Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit and register to have the code file.

    Table of Contents

    1. Oracle SQL Developer
      1. Table of Contents
      2. Oracle SQL Developer
      3. Credits
      4. About the Author
      5. About the Reviewer
        1. Support files, eBooks, discount offers, and more
          1. Why subscribe?
          2. Free access for Packt account holders
          3. Instant updates on new Packt books
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Downloading the color images of this book
          3. Errata
          4. Piracy
          5. Questions
      8. 1. Getting Started with SQL Developer 4.1
        1. Preparing your environment
          1. Finding and downloading the software
            1. Downloading and installing the Java Development Kit
          2. Installing and starting SQL Developer
            1. Working with different platforms
              1. Microsoft Windows
              2. Alternative platforms
                1. Mac OS X
                2. Linux
            2. Migrating settings from a previous release
          3. Maintaining your environment
          4. Verifying the current release
          5. Using Check for Updates
            1. Managing the system folder and other files
          6. Alternative installations of SQL Developer
            1. Oracle JDeveloper
            2. Oracle Database 12c
            3. Troubleshooting
              1. Removing extensions
              2. Resetting shortcut keys
              3. Reset the environment, do not reinstall the product
        2. A quick overview
          1. Sample schemas
          2. Creating your first connection
          3. Using basic commands in the SQL Worksheet
          4. Browsing and updating data
          5. Running reports
        3. Navigating around SQL Developer
          1. Managing SQL Developer windows
            1. Tiling windows
            2. Splitting documents
            3. Maximizing detail
          2. Resetting the window layout
        4. Database certification matrix (Oracle & Third-Party)
        5. Finding more help
        6. Summary
      9. 2. Database Connections and SQL Worksheet
        1. Working with the Connections navigator
          1. Opening connections
        2. Working with database objects
        3. Display editors
          1. General display editors
          2. Working with the display editors
          3. Using the SQL display editor
        4. Working with the data grids
        5. Reviewing other database object nodes
          1. Working with PL/SQL objects
          2. Accessing objects you don't own
            1. Other users
            2. Synonyms and filters
          3. Recycle Bin
        6. Creating new database objects
        7. Editing database objects – putting context menus to work
          1. Editing objects
        8. Script Runner/Running scripts
          1. Execution plan
          2. Autotrace pane
          3. DBMS Output pane
          4. OWA output pane
          5. Query Builder
        9. Command-line interface for SQL formatting
        10. SQL Worksheet "hints" for formatting output
        11. Summary
      10. 3. The Power of SQL Reports
        1. Introducing SQL Developer reports
          1. Who can run reports?
          2. When do you use reports?
          3. Tree layout for ease of navigation
          4. Running a report
            1. Privileges required for running reports
          5. Running data dictionary reports
            1. Getting to know the data dictionary
            2. About the database
            3. Reviewing privileges and security reports
            4. Assisting with quality assurance
            5. Using PL/SQL reports
            6. Running ASH and AWR reports
            7. Migration reports
            8. Application Express reports
            9. Data Modeler reports
            10. More report categories
          6. Running reports from other menus
            1. The Monitor Sessions report
            2. Managing the database
            3. Real-time SQL monitoring
        2. Creating your own reports
          1. Getting started
            1. Creating folders
            2. Storing reports
          2. Creating general reports
            1. Building general tabular reports
            2. Adding bind variables
            3. Drilling down through reports
            4. Creating a drill-down report
          3. Master-detail reports
            1. Creating master-detail reports
            2. Adding charts
          4. Importing and exporting
          5. Sharing reports through user-defined extensions
        3. DB Doc generation
        4. Summary
      11. 4. Working with PL/SQL
        1. Creating, compiling, and debugging PL/SQL
          1. Writing PL/SQL in the SQL Worksheet
            1. Using code insight
            2. Using code snippets and code templates
            3. Working with triggers
            4. Using the Create Trigger dialog
              1. Creating INSTEAD OF triggers
            5. Controlling triggers
          2. Adding functions or procedures
            1. Working with errors
          3. Creating packages
            1. Creating the body
          4. Refactoring code
          5. Searching for code
            1. Finding a DB Object
          6. Debugging PL/SQL code
            1. Using the debugging mechanism in SQL Developer
              1. Using the Oracle debugging packages
              2. Debugging
          7. Working with Explain Plan
            1. Controlling the Explain Plan output
            2. Execution plan details
          8. Using SQL reports
            1. Running the Top SQL reports
        2. Summary
      12. 5. SQL Developer for DBAs
        1. Database instance viewer
        2. Finding database objects
        3. Database configuration – initialization parameters
        4. Automatic undo management
        5. Current database properties
        6. Restore points
        7. Data Pump
        8. Export Jobs
        9. Import Jobs
        10. Performance
        11. Snapshots (filtered)
        12. Baselines
        13. Baseline templates
        14. Automatic Database Diagnostic Monitor (ADDM)
        15. ASH Report Viewer
        16. AWR
        17. RMAN Backup/Recovery
          1. Backup jobs
          2. Backup sets
          3. Image copies
          4. RMAN settings
          5. Scheduled RMAN actions
        18. Resource manager
        19. Consumer group mappings
        20. Consumer groups
        21. Plans
        22. Settings
        23. Statistics
        24. Scheduler
        25. Global Attributes
        26. Job Classes
        27. External Destinations
        28. Security
        29. Audit Settings
        30. Profiles
        31. Roles
        32. Users
        33. Storage
        34. Archive Logs
        35. Control Files
        36. Datafiles
        37. Redo Log Groups
        38. Rollback Segments
        39. Tablespaces
        40. Temporary Tablespace Groups
        41. Summary
      13. 6. SQL Developer Accessibility
        1. Screen reader readability
        2. Keyboard access
        3. Font and color choices
        4. No audio-only feedback
        5. Screen magnifier usability
        6. Change the editor or tabbed view of a file
        7. Read text in a multi-line edit field
        8. Read the line number in the source editor
        9. Customize the accelerators keys
        10. The look and feel of SQL Developer
        11. Customize syntax highlighting
        12. Display line numbers in Code Editor
        13. Timing for completion insight
        14. Specify the columns in the Debugger
        15. Summary
      14. 7. Importing, Exporting, and Working with Data
        1. Exporting data
          1. Exporting instance data
          2. Exporting table data
            1. Setting up the export file
            2. Exporting SQL DML
            3. Exporting to HTML
            4. Supporting export for SQL*Loader
            5. Exporting to Microsoft Excel
            6. Exporting to XML
          3. Exporting DDL (Metadata)
            1. Exporting table DDL
            2. Selecting multiple tables for DDL export
          4. Using the Database Export wizard to export DDL and data
            1. Starting the export wizard
            2. Selecting objects for generation
            3. Specifying objects
            4. Running the script
        2. Importing data
          1. Importing data from SQL script files
          2. Importing data from XLS and CSV files
          3. Creating a table on XLS import
        3. Summary
      15. 8. Database Connections and JDBC Drivers
        1. Working with Oracle connections
          1. Using alternative Oracle connection types
            1. Reviewing the Basic connection
            2. Accessing the tnsnames.ora file
            3. Accessing LDAP server details
            4. Creating advanced connections with JDBC URLs
            5. Connecting to Oracle TimesTen
          2. Reviewing JDBC drivers
            1. Oracle JDBC thin driver (Type IV driver)
            2. Oracle JDBC thick driver (Type II driver)
            3. SQL Developer's shipped drivers
              1. Fixing connection errors
          3. Using different authentication methods
            1. OS Authentication
            2. Setting the database environment variables
            3. Creating a connection using OS Authentication
            4. Using proxy authentication
            5. Using the Kerberos authentication
              1. Implementing Kerberos authentication in SQL Developer
              2. Kerberos authentication using the thin JDBC driver
        2. Creating non-Oracle database connections
          1. Setting up JDBC drivers
            1. Using Check for Updates
            2. Manually adding JDBC drivers
        3. Summary
      16. 9. Introducing SQL Developer Data Modeler
        1. Oracle SQL Developer Data Modeler
          1. Feature overview
            1. Integrated architecture
        2. Getting started
          1. Oracle clients and JDBC drivers
          2. Creating your first models
          3. Importing from the Data Dictionary
            1. Creating a database connection
            2. Using the Import Wizard
            3. Reviewing the results
        3. Working with diagrams and their components
          1. Formatting the elements
          2. Controlling the layout
            1. Adjusting the level of detail displayed
            2. Adjusting the width and height across the model
        4. Analysis, design, and generation
          1. The flow of work
            1. Starting with analysis (top down)
            2. Importing existing models (bottom up)
            3. Building the relational model
          2. Logical models
            1. Creating an ERD
            2. Creating entities
            3. Adding attributes
            4. Working with relationships
          3. Creating constraints, domains, and setting default values
            1. Working with domains
            2. Creating domains
              1. Using domains to implement check constraints
          4. Introducing forward and reverse engineering
            1. Forward engineering
            2. Reverse engineering models
          5. Creating relational models
          6. Building the physical model
            1. Importing a schema from the Data Dictionary
          7. Generating the DDL
            1. Creating a new model
        5. Summary
      17. 10. Extending SQL Developer
        1. Introducing extensibility
          1. Extensibility in SQL Developer
          2. Who builds extensions?
          3. Why extend?
          4. SQL Developer XML extension types
          5. Adding an XML extension
          6. Sharing user-defined reports
          7. Adding display editors
            1. Examples of display editors
              1. Include subpartitions
              2. Use restricted queries
              3. Add display editors for new navigator nodes
            2. Building the XML file for a display editor
          8. Working with context menus
            1. Adding a context menu to the connections menus
            2. Passing parameters
            3. Creating a utility using context menus
            4. Including the user-defined extension for context menus
          9. Adding new nodes to the Connections navigator
            1. Including user-defined extensions for a navigator node
            2. Adding a new tree to the navigator
        2. Working with extensions
          1. Controlling existing extensions
            1. Adding in new Java extensions
            2. Removing extensions
          2. Sharing extensions
        3. Summary
      18. 11. Working with Application Express
        1. Setting the scene
          1. Setting up Application Express
            1. Creating a workspace and database schema
            2. Creating an Application Express user
          2. Browsing applications in SQL Developer
            1. Creating a connection in SQL Developer
            2. Browsing and working with schema objects
            3. Browsing the applications
              1. Drilling down into lists of values
              2. Focusing on pages
            4. Mapping objects from SQL Developer to Application Express
          3. Tuning SQL and PL/SQL code using SQL Developer
            1. Working with Region Source SQL
            2. Tuning with Explain Plan
            3. Working with PL/SQL code
            4. Replacing the anonymous block in Application Express
          4. Managing applications in SQL Developer
            1. Importing applications
            2. Modifying applications
            3. Deploying applications
            4. Controlling services
          5. Reporting on applications using SQL Developer
        2. Summary
      19. 12. Working with SQL Developer Migrations
        1. Introducing SQL Developer Migrations
          1. An overview of the migration process
          2. Offline or online migration choices
          3. Supported third-party databases
        2. Setting up your environment
          1. Setting up JDBC drivers
          2. Creating third-party connections
          3. Accessing non-Oracle databases
            1. Browsing database objects
          4. Managing the repository
            1. Setting up the repository owner:
            2. Creating the repository
            3. Associating a repository with a user
          5. Planning database connections
            1. Setting up the source database connection
            2. Setting up the target database connection
        3. Migrating
          1. Using Quick Migrate
            1. The migration
            2. Verifying the results
              1. Reviewing the logs
            3. Generating scripts
          2. Migration reports
        4. Summary
      20. 13. Oracle Data Miner 4.1
        1. Data source node
          1. Creating a new project
          2. Creating a new workflow
          3. Addition of nodes to the workflow
          4. Link nodes
          5. Run nodes
          6. View reports
        2. JSON data type
          1. JSON Query node
        3. The APIs for data mining
        4. Summary
      21. 14. REST Data Services and REST Development
        1. About Oracle REST Data Services
        2. The Oracle REST Data Services Administration toolbar and context menu
          1. Connecting to Oracle REST Data Services
          2. REST Data Services navigator – global and database settings
        3. About RESTful services
          1. RESTful services terminology
          2. RESTful services requirements and setup
        4. Summary
      22. Index