Oracle SQL Developer

Oracle SQL Developer

by Ajith Narayanan
Released January 2016
Publisher(s): Packt Publishing
ISBN: 9781785281273

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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.

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
    6. www.PacktPub.com
      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

Product information

  • Title: Oracle SQL Developer
  • Author(s): Ajith Narayanan
  • Release date: January 2016
  • Publisher(s): Packt Publishing
  • ISBN: 9781785281273