Oracle SQL Developer & SQL Developer Data Modeler Kris Rice Senior Director of Development

Wednesday, September 7, 11

Oracle SQL Developer 3.0 • • • • • • • • • • • •

DBMS Scheduler Query Builder Database migration Integrated spatial support PDF file support DBA Navigator Schema browser Extended Tuning Support Extended Unit Testing PL/SQL updates Import and Export updates Integrated Data Miner support

2

Wednesday, September 7, 11

PL/SQL Unit Testing

• Create tests with one or more implementations • Build up suites of tests • Run test reports • Store reusable components in a library • Build static and dynamic lookups • Support multiple users through a repository • Build up code coverage stats • Run tests from the command line • Target any database

3

Wednesday, September 7, 11

Updated Data Grids

• Browse table and views data grid – Filter, Sort

• Context menus offer – – – – –

Single record view Duplicate row Count rows Save Grid as a report Highlight data

• Manage columns – Reorder – Hide and show columns – Data filters

• • • •

Track updates in message log Recall filters Single value updates in data grid Clear grid settings

4

Wednesday, September 7, 11

Schema Browser

• • • •

Alternate view of object browser Select and filter object type Select and filter users Additional client-side filter

5

Wednesday, September 7, 11

Query Builder

• Replaces current query builder • Integrated with Worksheet • Supports – Multiple object selection – Switch from Query Builder and Worksheet – Aggregates, Alias, Order By, Group By, Where clauses – Repeated tables – Union, intersect, minus and subqueries

6

Wednesday, September 7, 11

PL/SQL Updates

• Debugging PL/SQL tagged file • Run and debug procedures with ref cursors – Display ref cursor data

7

Wednesday, September 7, 11

Unload (Export) Data • Unload metadata and data for a schema, selected object types or selected objects • Customizable user interface • Configurable metadata and data format options • Target options to send output to single or multiple files and directories • Set delimiters, enclosures, record terminators, encoding and header Wednesday, September 7, 11

8

Export to PDF • New data format support for PDF • Configure PDF file format using preferences • Customizable Layouts • Provides security to encrypt and password-protect • Provides option to include BLOBs such as images, video, spreadsheets

9

Wednesday, September 7, 11

Import (Load) Data

• Various load methods – Insert and insert script – External table, staging external table, – SQL*Loader

• Customizable user interface • Delimited file support for specifying delimiters, enclosures, record terminators, encoding and header • Dynamic data preview provides view of file as changes to the definition are made

10

Wednesday, September 7, 11

Unit Test Updates

• Enhanced multi-user support • Support added for abstract data types – – – –

PL/SQL VARRAY PL/SQL Nested Table PL/SQL Associative Array Ref Cursors

• Multiple startups and teardowns • Purge run results – All results – Set of results

11

Wednesday, September 7, 11

DBA Navigator

• Browse and maintain database configuration • Review and manage database using the resource manager • Browse, update and create security options • Update and review storage objects • Create users and roles

12

Wednesday, September 7, 11

Extended Tuning Support

• Current Tuning support includes – – – –

Monitor SQL PL/SQL Hierarchical Profiler Explain Plan Autotrace

• SQL Developer 3.0 – SQL Tuning Advisor – Diff tool for explain plans

13

Wednesday, September 7, 11

SQL Tuning Advisor

• Requires advisor and administer SQL Tuning set privileges • Review all advice or select individual components – Stale or missing statistics – Generation of SQL Profiles with in depth detail – Manual restructuring of SQL – Use of indexes

14

Wednesday, September 7, 11

Compare Explain Plan and Autotrace Results

• Compare results of two queries – Gather Explain Plan/Autotrace results for first query – Pin results – Modify query – Gather second set of plan results – Compare plans

15

Wednesday, September 7, 11

DBMS Scheduler

• DBMS Scheduler allows you to control when and where various tasks take place • Scheduler node provides – – – – –

Chains Credentials Jobs & Job Classes Programs Schedules

• Design Editor view for graphical representation

16

Wednesday, September 7, 11

Database Migration Projects

• Migration navigator for 3rd party database migration • Copy to Oracle – Right click and copy to a connection

• Server Capture for multi-database • Migration Wizard – Drives all phases of migration

• Command line support – All migration features

• Migration Reports – – – –

Project Estimation/Server Analysis Conversion Issues Data Quality metrics Target objects verification

• Client application scanning – Identifies 3rd party database calls in applications

17

Wednesday, September 7, 11

TimesTen Support in SQL Developer 3.0

• Browse, edit, create, modify TimesTen database objects including users • In-Memory Database Cache – Create/drop/alter cache groups – Load/unload, flush, refresh cache data

• PL/SQL support – Create/Replace/Drop, Edit, Compile, Run, and Export procedures/functions/packages

• Show SQL execution plans and update table and column statistics • Access SQL Worksheet for ad-hoc SQL queries, execute TimesTen built-in procedures and ttIsql commands • Run pre-defined database and objects reports • Manipulate cached data in both TimesTen and Oracle at the same time 18

Wednesday, September 7, 11

Oracle Data Miner in SQL Developer 3.0

• Graphical User Interface for data analysts • Build and evaluate data mining models • Apply predictive models • Share analytical workflows • Deploy SQL, apply code and scripts • Predict customer behavior • Identify key factors • Predict next-likely product • Customer profiling • Detect fraud and anomalies • Mine “text” and unstructured data 19

Wednesday, September 7, 11

Data Modeler Viewer

• Read-only model viewer • Open any data model created in SQL Developer Data Modeler • All properties available for review through dialogs • Create ad hoc diagrams through drag and drop

20

Wednesday, September 7, 11

Managing Oracle Spatial Information

• Data Management – Maintain spatial metadata and indexes – Support for spatial validation • Data Querying – Create spatial queries – Generate spatial shapes – Display query results in map view • Map support – Display layers in a map based on map queries – Drag and drop spatial tables onto map view for quick display – Use spatial snippets 21

Wednesday, September 7, 11

Oracle SQL Developer Data Modeler - Overview

• A no cost diagramming and data modeling tool • A single tool for different users and functionality – Data Architect builds logical data models – Database Developer models relational models (tables and columns) – DBA adds tablespaces, partitions

• Use data models to – Verify accuracy and completeness of data requirements and business rules with customers – Build standards-driven DDL scripts

• Metadata is stored in XML files

Wednesday, September 7, 11

Oracle SQL Developer Data Modeler

• Multi-level Data Modeling across platforms within one integrated system – – – – – – – –

• • • •

Designing logical Entity Relation Diagrams (ERD) Multi-dimensional modeling User Defined Data Types Building relational schema designs Generating and executing DDL scripts Reverse engineering of existing data structures Import of data models from CA ERwin and Oracle Designer Multi-level relational and physical design environment

Multiple platform support Multiple database support Increases migration productivity Read only visualization of database data models

Wednesday, September 7, 11

Logical Modeling

• Model entities, attributes and relations • Support for – Super type – Sub types

• Transform one logical to many relational and multidimensional models • Support for configurable forward and reverse engineering • Support for different modeling notations

Wednesday, September 7, 11

Relational Modeling

• Model tables, columns and FKs • Create one logical for one or more relational models • Support configurable forward and reverse engineering • Use subviews to work with a subset of tables • Provide different displays of the same model

Wednesday, September 7, 11

Forward and Reverse Engineering (Transformation)

• Each logical model to maps to one or more relational models • Each relational model maps to one logical model • Each relational model maps to one or more physical models • Each physical model maps to one relational model • Engineering options – General – Compare/copy – Synchronization

• Include design glossary and naming standards

Wednesday, September 7, 11

Physical Modeling

• One relational model for many physical models • Add support for – – – –

Tablespaces Users Roles Stored procedures

• Propate properties – Apply properties to many elements at once

• Supports – Oracle9i, Oracle Database 10g and Oracle Database 11g – Microsoft SQL Server 2000 and 2005 – IBM DB2/390 and DB2 LUW

Wednesday, September 7, 11

Modeling DataTypes

• Support for SQL99 (Object Relational Modeling) – Distinct Types – (Predefined) Structured Types – (Predefined) Collection Types

• • • •

Used in logical models Used in relational models Included on import Generated in DDL

Wednesday, September 7, 11

Multi-Dimensional Modeling

• Modeling of Cubes, Dimensions, Levels and Hierarchies, Measures and slices • Start from ROLAP, XMLA or from scratch • Generate Oracle Analytical Workspaces

Wednesday, September 7, 11

Importing Metadata

• Import using direct connections – – – –

Oracle Database Microsoft SQL Server IBM DB2 and UDB Generic JDBC based dictionary • Examples: MySQL, Teradata

– Oracle Designer repository

• File import – Other Modeling tools • CA ERwin • Bachman

– Multi-Dimensional • Cube views • XMLA

Wednesday, September 7, 11

Exporting and Code Generation

• DDL file editor supports – – – –

Design Rules Object selection Drop objects Table scripts

• Standard database DDL scripts – Oracle – IBM DB2 and UDB – Microsoft SQL Server

• Multi-Dimensional Oracle AW, Cube Views and XMLA • CSV export

Wednesday, September 7, 11

Controlling the Design Environment

• Selection of tools available – Domain definition (data types) – Name abbreviation in the relational model (Customer to CUST) – Compare and merge facilities – Design Rules

• Tools Options – – – – –

Naming standards Specify default database Controlling constraints Physical properties Notations (Barker, Bachman, Information Engineering)

Wednesday, September 7, 11

Custom Design Rules and Transformations

• Use scripting engine of choice • Create user defined transformation scripts • Apply multiple scripts or rules • Build up Rule Sets • Create Libraries

Wednesday, September 7, 11

Collaborative Development

• Use Versioning Navigator to connect to Subversion repository • Start by checking in Design using – Data Modeler – External client

• Multiple users check out designs • Pending Changes dialog provides feedback to all users • Commit changes to repository • Manage conflicts

Wednesday, September 7, 11

Integrated Reports

• Integrated reporting supported in addition to repository reports • Generated as XML • Open in MS Word

Wednesday, September 7, 11

Reporting Repository

• Create repository user • Export design to repository – Initial export creates repository – Exports initial version to repository

• SQL Developer support – – – –

Browse repository Import reports Run shipped reports Create your own reports

Wednesday, September 7, 11

Finding More Detail www.oracle.com/sqldeveloper

• SQL Developer Data Modeler on OTN – White papers, Oracle by Example (OBE), online demos, models and scripts – www.oracle.com/technetwork/developer-tools/datamodeler/

• SQL Developer Exchange – Add feature requests: sqldeveloper.oracle.com

• Forums – SQL Developer forums.oracle.com/forums/forum.jspa? forumID= 1317

Wednesday, September 7, 11

Wednesday, September 7, 11