Oracle SQL Developer 2.1. Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this b

Oracle SQL Developer 2.1. Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this b - Opis

As technology rapidly evolves, many developers are looking for valuable tools to assist them with their daily tasks. When dealing with databases, a clean, easy-to-navigate interface for working and browsing is essential. Oracle SQL Developer is a graphical user interface that makes life much easier by allowing you to browse database objects, run SQL statements and scripts, and create, edit, and debug PL/SQL statements in the most efficient way. It enhances productivity and simplifies your database development tasks. Although the SQL Developer journey looks simple and easy, there are many areas that can go undiscovered, leaving you just scratching the surface.It's easy to get started and master this powerful tool with this book to hand. It will provide you with in-depth details about all aspects of using SQL Developer to assist you in your day-to-day database tasks and activities. You will learn to utilize SQL Developer's extensible environment to support your ongoing needsThis book offers detailed instructions for installing, configuring, and effectively using Oracle SQL Developer. You will learn how to utilize every feature of this development tool and make the most out of it. While none of the tasks are complex, the book progresses from the easy, most commonly used features, such as browsing objects and writing queries in the SQL Worksheet, to the more involved and possibly less frequently used features, such as Tuning and Testing SQL and PL/SQL, and adding User Extensions, and finally to those features used by a smaller more targeted audience, such as Migrations, (...) więcej Oracle APEX, and the Data Modeler. Throughout the book there are tips and suggestions gathered as a result of working with the current SQL Developer user base. This book will also show you how to assess the health of your database with built-in as well as customized reports.By the end of the book you will be confident in making the best use of SQL Developer, and be able to set up and maintain a productive environment for quick and easy database development. Spis treści:
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 dont 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 mniej