Oracle SQL Developer 2.1电子书

Oracle SQL Developer 2.1

Table of Contents

Oracle SQL Developer 2.1

Credits

About the Author

Acknowledgement

About the Reviewers

Preface

What this book covers

I: Getting started

II: Delving a little deeper

III: Focus areas

Standards and assumptions

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Getting Started with SQL Developer

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

Sharing preferences

Alternative installations of SQL Developer

Oracle JDeveloper

Oracle Database 11g

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

Finding more help

Summary

2. Browsing and Editing Database Objects and Data

Browsing objects

Working with the Connections navigator

Opening connections

Working with objects

Filtering objects

Schema level filtering

Display editors

General display editors

Working with the display editors

Using the SQL display editor

Working with the data grids

Controlling the column display

Sorting the data

Filtering columns

More data grid context menus

Highlighting data

Count Rows and the Single Record View

Save Grid as Report

Working with Updating Data Grids

Sorting and filtering data

Updating data

Working with LOBS in the data grid

Using flashback

Reviewing other database object nodes

Working with PL/SQL objects

Accessing objects you don't own

Other users

Synonyms and filters

Recycle Bin

Creating and updating database objects

Creating new objects

Creating tables

Early decisions

Adding constraints

Creating column sequences

Additional properties

Storage

Partitions

Creating views

Writing the SQL query

Using the Quick-pick objects

Building the query using elements in the tree

Reviewing a few specific objects

Creating new users

Setting the privilege to create new objects

Editing objects: Putting context menus to work

Editing objects

Diving into context menus

Using context menus as utilities

Normalizing data

Triggers and sequences

Summary

3. Working with the SQL Worksheet

Introducing the SQL Worksheet

Controlling the environment

Opening SQL Worksheets

Working with multiple worksheets

Switching connections

Getting started

Writing and executing commands

Writing your first statements

Running statements

Run script

Using SQL*Plus commands

Supporting SQL*Plus

Bind variables

The advantage of using bind variables

Bind variables and performance

Security and bind variables

Running scripts

Reviewing unsupported SQL*Plus commands

Working with SQL

Dragging and dropping tables to create queries

Formatting code

Managing the case

Formatting SQL for use in other languages

Working with code completion insight

Controlling code completion insight in the SQL Worksheet

Including code snippets

Managing the Snippets window

Adding code snippets

Creating code templates

Using the File navigator

Opening files

Using extra features in the worksheet

SQL History

DBMS Output

OWA Output

Using the Query Builder

Building an SQL query

Selecting the tables, column, and joins

Viewing the Results

Adding the WHERE clause

Returning to the SQL Worksheet

Summary

4. The Power of SQL Reports

Introducing SQL Developer reports

Who can run reports?

When do you use reports?

Running a report

Using bind variables

Privileges required for running reports

Switching users

Browsing shipped reports

Running data dictionary reports

Getting to know the data dictionary

About the database

Reviewing Privileges and Security reports

Assisting with quality assurance

Using the PL/SQL reports

Running ASH and AWR reports

Other categories

Migration reports

Application Express reports

Data Modeler reports

Running reports from other menus

Monitor sessions

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

Creating sibling details

Adding charts

Building other graphical reports

Creating pie chart reports

Using gauges in reporting

Other reports styles

Using the Script style report

Using PL/SQL in a report

Sharing reports

Copying and reusing reports

Importing and exporting

Sharing reports through user defined extensions

Summary

5. Working with PL/SQL

Creating PL/SQL code

Writing PL/SQL in the SQL Worksheet

Using code insight

Using code snippets and code templates

Creating and compiling PL/SQL program units

Working with triggers

Using the Create Trigger dialog

Creating INSTEAD OF triggers

Creating system triggers on the database or schema

Viewing trigger details

Controlling triggers

Adding triggers that populate columns

Adding functions or procedures

Editing program units

Working with errors

Testing and executing program units

Creating packages

Creating the body

Reviewing a package

Testing packages

Editing code

Refactoring code

Searching for code

Finding DB Object

Debugging PL/SQL

Debugging PL/SQL code

Using the debugging mechanism in SQL Developer

Using the Oracle debugging packages

Debugging

Navigating through the code

Remote debugging

Preparing for remote debugging

Remote debugging with Application Express

Summary

6. SQL and PL/SQL Tuning Tools

Support for tuning code in the SQL Worksheet

Working with EXPLAIN PLAN

Controlling the Explain Plan output

Execution plan details

Using Autotrace

Additional performance tuning tools

Using SQL reports

Running the Top SQL reports

Monitoring your environment

Inspecting SQL trace files

Profiling PL/SQL

Getting started

Preparing the environment

Reviewing the output

Summary

7. Managing Files

Introducing source code control

Overview

Ways of working

The repository is the point of truth

SQL Developer integration

Subversion (SVN)

Concurrent Versions System (CVS)

Other version control systems

Getting started

Invoking the Files navigator

Browsing and editing files

Reviewing the file editors

Editing other file types

Working with the file history

Introducing the Versioning Navigator

Managing general version control preferences

Setting up the repository

Working with the Subversion repository

Adding CVS repositories

Creating connections to a version repository

Browsing files in the repository

Working with files under version control

Placing files under version control

Importing files into the repository

Working with directories

Performing a Check Out after import

Refreshing the repository

Understanding revision numbers

Checking out files

Identifying checked out files

Tracking the working copy

Saving files

Pending changes preferences

Checking files in

Updating the working copy

Reverting changes

Committing changes to the repository

Comparing and merging code

Creating patches

Summary

8. Importing, Exporting, and Working with Data

Exporting data

Exporting instance 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

Specifying data

Running the script

Importing data

Importing data from SQL script files

Importing data from XLS and CSV files

Creating a table on XLS import

Using the Database Copy wizard

Comparing the database copy alternatives

Running the Database Copy wizard

Comparing schemas

Summary

9. 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 Developers 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 Kerberos authentication

Implementing Kerberos authentication in SQL Developer

Kerberos authentication using the thin JDBC driver

Kerberos authentication using the thick driver

Set up the sqlnet.ora file

Setting up the user

Preparing the authentication ticket

Connecting to Oracle using Kerberos, SQLNET, and SQL Developer

Using RADIUS authentication

Creating non-Oracle database connections

Setting up JDBC drivers

Using Check for Updates

Manually adding JDBC drivers

Creating connections to the third-party databases

Connecting to IBM DB2

Microsoft Access

Connecting to Sybase Adaptive Server or Microsoft SQL Server

Connecting to MySQL

Organizing your connections

Creating folders

Working with folders

Managing folders

Exporting and importing connections

Summary

10. Introducing SQL Developer Data Modeler

Oracle SQL Developer Data Modeler

Feature overview

Integrated architecture

Getting started

Installing and setting up the environment

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

Saving designs

Working with diagrams and their components

Formatting the elements

Changing the default format settings

Setting general diagram properties

Creating subviews and displays

Adding subviews to your design

Adding displays

Creating a composite view

Controlling the layout

Adjusting the level of detail displayed

Adjusting the width and height across the model

Controlling alignment

Working with lines

Managing lines with elbows

Managing straight lines

Analysis, design, and generation

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 arcs

Supporting alternate notations

Supporting subtypes and supertypes

Creating constraints, domains, and setting default values

Working with domains

Creating domains

Using domains to implement check constraints

Assigning domain valid values to an attribute or column

Setting valid values at attribute or column level

Adding a default value

Introducing forward and reverse engineering

Forward engineering

General engineering dialog features

Maintaining the model layout

Reverse engineering models

Creating relational models

Working with the relational model

Creating a table and adding columns

Including Foreign Key constraints

Setting naming standards templates

Applying the templates to the model

Updating the object names prefix

Building the physical model

Importing a schema from the data dictionary

Creating a new physical model

Adding new database sites

Reviewing physical properties

Propagate properties

Generating the DDL

Reviewing and applying Design Rules

Generating the DDL script

Updating an existing schema

Integration with Oracle SQL Developer

Creating a new model

Creating and running reports

Setting up the reporting schema

Summary

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

Adding multiple nodes

Reviewing an example

Adding support for dimensions

Adding a dimensions navigator

Adding dimension display editors

Adding context menus

Working with extensions

Controlling existing extensions

Adding in new Java extensions

Removing extensions

Sharing extensions

Summary

12. Working with Application Express

Setting the scene

Setting up in 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 in 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

13. 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 the JDBC drivers

Creating third-party connections

Accessing non-Oracle databases

Browsing database objects

Using the SQL Worksheet

Managing the repository

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

Delving into a complex migration

Preparing for the migration

Offline versus online migration

Capturing the model

Reviewing the captured model

Updating the model

Converting objects at a more granular level

Converting the model

Setting the conversion data mapping

Reviewing the logs

Translating procedural code

Generating the scripts

Executing the script

Populating the target tables with data

Offline migrations

Additional migration activities

Migration reports

Summary

Index