Oracle SQL Developer

Ajith Narayanan

• Publisher: Packt Publishing
• Year: 2016
• ISBN:
  9781785281273
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

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.

Oracle SQL Developer

---

Table of Contents

Oracle SQL Developer

Credits

About the Author

About the Reviewer

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Downloading the color images of this book

Errata

Piracy

Questions

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

Microsoft Windows

Alternative platforms

Mac OS X

Linux

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

Removing extensions

Resetting shortcut keys

Reset the environment, do not reinstall the product

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

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

Summary

3. The Power of SQL Reports

Introducing SQL Developer reports

Who can run reports?

When do you use reports?

Tree layout for ease of navigation

Running a report

Privileges required for running reports

Running data dictionary reports

Getting to know the data dictionary

About the database

Reviewing privileges and security reports

Assisting with quality assurance

Using PL/SQL reports

Running ASH and AWR reports

Migration reports

Application Express reports

Data Modeler reports

More report categories

Running reports from other menus

The Monitor Sessions report

Managing the database

Real-time SQL monitoring

Creating your own reports

Getting started

Creating folders

Storing reports

Creating general reports

Building general tabular reports

Adding bind variables

Drilling down through reports

Creating a drill-down report

Master-detail reports

Creating master-detail reports

Adding charts

Importing and exporting

Sharing reports through user-defined extensions

DB Doc generation

Summary

4. Working with PL/SQL

Creating, compiling, and debugging PL/SQL

Writing PL/SQL in the SQL Worksheet

Using code insight

Using code snippets and code templates

Working with triggers

Using the Create Trigger dialog

Creating INSTEAD OF triggers

Controlling triggers

Adding functions or procedures

Working with errors

Creating packages

Creating the body

Refactoring code

Searching for code

Finding a DB Object

Debugging PL/SQL code

Using the debugging mechanism in SQL Developer

Using the Oracle debugging packages

Debugging

Working with Explain Plan

Controlling the Explain Plan output

Execution plan details

Using SQL reports

Running the Top SQL reports

Summary

5. SQL Developer for DBAs

Database instance viewer

Finding database objects

Database configuration – initialization parameters

Automatic undo management

Current database properties

Restore points

Data Pump

Export Jobs

Import Jobs

Performance

Snapshots (filtered)

Baselines

Baseline templates

Automatic Database Diagnostic Monitor (ADDM)

ASH Report Viewer

AWR

RMAN Backup/Recovery

Backup jobs

Backup sets

Image copies

RMAN settings

Scheduled RMAN actions

Resource manager

Consumer group mappings

Consumer groups

Plans

Settings

Statistics

Scheduler

Global Attributes

Job Classes

External Destinations

Security

Audit Settings

Profiles

Roles

Users

Storage

Archive Logs

Control Files

Datafiles

Redo Log Groups

Rollback Segments

Tablespaces

Temporary Tablespace Groups

Summary

6. SQL Develo...

Table of contents

• Oracle SQL Developer