Oracle SQL Developer电子书

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 Developer Accessibility

Screen reader readability

Keyboard access

Font and color choices

No audio-only feedback

Screen magnifier usability

Change the editor or tabbed view of a file

Read text in a multi-line edit field

Read the line number in the source editor

Customize the accelerators keys

The look and feel of SQL Developer

Customize syntax highlighting

Display line numbers in Code Editor

Timing for completion insight

Specify the columns in the Debugger

Summary

7. Importing, Exporting, and Working with Data

Exporting data

Exporting instance data

Exporting table data

Setting up the export file

Exporting SQL DML

Exporting to HTML

Supporting export for SQL*Loader

Exporting to Microsoft Excel

Exporting to XML

Exporting DDL (Metadata)

Exporting table DDL

Selecting multiple tables for DDL export

Using the Database Export wizard to export DDL and data

Starting the export wizard

Selecting objects for generation

Specifying objects

Running the script

Importing data

Importing data from SQL script files

Importing data from XLS and CSV files

Creating a table on XLS import

Summary

8. Database Connections and JDBC Drivers

Working with Oracle connections

Using alternative Oracle connection types

Reviewing the Basic connection

Accessing the tnsnames.ora file

Accessing LDAP server details

Creating advanced connections with JDBC URLs

Connecting to Oracle TimesTen

Reviewing JDBC drivers

Oracle JDBC thin driver (Type IV driver)

Oracle JDBC thick driver (Type II driver)

SQL Developer's shipped drivers

Fixing connection errors

Using different authentication methods

OS Authentication

Setting the database environment variables

Creating a connection using OS Authentication

Using proxy authentication

Using the Kerberos authentication

Implementing Kerberos authentication in SQL Developer

Kerberos authentication using the thin JDBC driver

Creating non-Oracle database connections

Setting up JDBC drivers

Using Check for Updates

Manually adding JDBC drivers

Summary

9. Introducing SQL Developer Data Modeler

Oracle SQL Developer Data Modeler

Feature overview

Integrated architecture

Getting started

Oracle clients and JDBC drivers

Creating your first models

Importing from the Data Dictionary

Creating a database connection

Using the Import Wizard

Reviewing the results

Working with diagrams and their components

Formatting the elements

Controlling the layout

Adjusting the level of detail displayed

Adjusting the width and height across the model

Analysis, design, and generation

The flow of work

Starting with analysis (top down)

Importing existing models (bottom up)

Building the relational model

Logical models

Creating an ERD

Creating entities

Adding attributes

Working with relationships

Creating constraints, domains, and setting default values

Working with domains

Creating domains

Using domains to implement check constraints

Introducing forward and reverse engineering

Forward engineering

Reverse engineering models

Creating relational models

Building the physical model

Importing a schema from the Data Dictionary

Generating the DDL

Creating a new model

Summary

10. Extending SQL Developer

Introducing extensibility

Extensibility in SQL Developer

Who builds extensions?

Why extend?

SQL Developer XML extension types

Adding an XML extension

Sharing user-defined reports

Adding display editors

Examples of display editors

Include subpartitions

Use restricted queries

Add display editors for new navigator nodes

Building the XML file for a display editor

Working with context menus

Adding a context menu to the connections menus

Passing parameters

Creating a utility using context menus

Including the user-defined extension for context menus

Adding new nodes to the Connections navigator

Including user-defined extensions for a navigator node

Adding a new tree to the navigator

Working with extensions

Controlling existing extensions

Adding in new Java extensions

Removing extensions

Sharing extensions

Summary

11. Working with Application Express

Setting the scene

Setting up Application Express

Creating a workspace and database schema

Creating an Application Express user

Browsing applications in SQL Developer

Creating a connection in SQL Developer

Browsing and working with schema objects

Browsing the applications

Drilling down into lists of values

Focusing on pages

Mapping objects from SQL Developer to Application Express

Tuning SQL and PL/SQL code using SQL Developer

Working with Region Source SQL

Tuning with Explain Plan

Working with PL/SQL code

Replacing the anonymous block in Application Express

Managing applications in SQL Developer

Importing applications

Modifying applications

Deploying applications

Controlling services

Reporting on applications using SQL Developer

Summary

12. Working with SQL Developer Migrations

Introducing SQL Developer Migrations

An overview of the migration process

Offline or online migration choices

Supported third-party databases

Setting up your environment

Setting up JDBC drivers

Creating third-party connections

Accessing non-Oracle databases

Browsing database objects

Managing the repository

Setting up the repository owner:

Creating the repository

Associating a repository with a user

Planning database connections

Setting up the source database connection

Setting up the target database connection

Migrating

Using Quick Migrate

The migration

Verifying the results

Reviewing the logs

Generating scripts

Migration reports

Summary

13. Oracle Data Miner 4.1

Data source node

Creating a new project

Creating a new workflow

Addition of nodes to the workflow

Link nodes

Run nodes

View reports

JSON data type

JSON Query node

The APIs for data mining

Summary

14. REST Data Services and REST Development

About Oracle REST Data Services

The Oracle REST Data Services Administration toolbar and context menu

Connecting to Oracle REST Data Services

REST Data Services navigator – global and database settings

About RESTful services

RESTful services terminology

RESTful services requirements and setup

Summary

Index