oracle applications using ado.net and c# volume 1 · 2009-12-16 · oracle applications using...

Object Innovations Course 443

Student Guide

Oracle Applications Using ADO.NET and C#

Volume 1

Oracle Applications Using ADO.NET and C# This course is printed in two volumes. Each volume corresponds to a shorter Object Innovations course, which can be ordered separately. Volume 1 – Course 173: Oracle Fundamentals Volume 2 – Course 413C2: ADO.NET Using Oracle and C# Student Guide Information in this document is subject to change without notice. Companies, names and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Object Innovations. Product and company names mentioned herein are the trademarks or registered trademarks of their respective owners. Copyright ©2004 Object Innovations, Inc. All rights reserved. Object Innovations 877-558-7246 www.ObjectInnovations.com Printed in the United States of America.

Object Innovations Course 173

Michael Simpson

Student Guide Revision 1.0

Oracle Fundamentals

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. ii All Rights Reserved

Oracle Fundamentals Rev. 1.0 Student Guide Information in this document is subject to change without notice. Companies, names and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Object Innovations. Product and company names mentioned herein are the trademarks or registered trademarks of their respective owners. Copyright ©2004 Object Innovations, Inc. All rights reserved. Object Innovations, Inc. 877-558-7246 www.objectinnovations.com Printed in the United States of America.

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. iii All Rights Reserved

Table of Contents (Overview) Chapter 1 Introduction to Oracle9i Chapter 2 Relational Database Basics and Design Chapter 3 Programming SQL Statements Chapter 4 Implementing Oracle Objects Chapter 5 Indexes and Storage Types Chapter 6 Transactions and Locks within Oracle9i Chapter 7 Oracle PL/SQL and User-Defined Data Types Chapter 8 Oracle and XML Integration Appendix A Acme Computer Case Study Appendix B Learning Resources

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. v All Rights Reserved

Table of Contents (Detailed)

Chapter 1 Introduction to Oracle9i........................................................................... 1 Oracle RDBMS Terminology ............................................................................................3 Oracle’s Physical Database Structures ..............................................................................4 Oracle’s Logical Database Structures................................................................................5 Oracle Segments ................................................................................................................6 Oracle Tablespaces ............................................................................................................7 Oracle Tablespace Example...............................................................................................8 Oracle Datafiles .................................................................................................................9 Oracle Redo Log Files .....................................................................................................10 Oracle Enterprise Manager ..............................................................................................12 Benefits of Oracle Enterprise Manager ...........................................................................13 OEM Architecture............................................................................................................15 Two-Tiered OEM.............................................................................................................17 Tier One: OEM Console ..................................................................................................18 OEM Console...................................................................................................................19 Tier Two: Oracle Management Server ............................................................................20 Multiple Management Servers.........................................................................................21 Tier Three: Managed Nodes ............................................................................................22 Intelligent Agents.............................................................................................................23 OEM Common Services ..................................................................................................24 Oracle Database Objects ..................................................................................................27 Oracle Table Functionality ..............................................................................................28 Relating Information Between Tables .............................................................................30 Using Table Aliases in SELECT Statements...................................................................31 Oracle Procedural Functionality ......................................................................................32 Oracle View Functionality...............................................................................................33 Oracle Synonym Functionality ........................................................................................34 Oracle Type Functionality ...............................................................................................35 Oracle Object Functionality.............................................................................................36 Oracle Information Storage .............................................................................................37 Using SQL*PLUS............................................................................................................38 Lab 1 ................................................................................................................................39 Summary ..........................................................................................................................40

Chapter 2 Relational Database Basics and Design ................................................ 43 Relational Database Concepts .........................................................................................45 High-Level Database Types: OLAP Databases..............................................................48 High-level Database Types: OLTP Databases.................................................................49 High-Level Database Types: OLAP vs. OLTP................................................................50 Functions of a Relational Database .................................................................................51 Making the Conceptual Move..........................................................................................52

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. vi All Rights Reserved

Structuring Tables Using Keys ........................................................................................54 Enforcing Relationships Using Foreign Keys .................................................................55 Relating Foreign Keys and Primary Keys .......................................................................56 Second Normal Form.......................................................................................................59 Third Normal Form..........................................................................................................60 Deriving a Design from a Scenario..................................................................................61 Going to First Normal Form ............................................................................................62 Going to Second Normal Form........................................................................................64 Going to Third Normal Form...........................................................................................66 Lab 2 ................................................................................................................................68 Summary ..........................................................................................................................69

Chapter 3 Programming SQL Statements.............................................................. 73 Oracle SQL Language......................................................................................................75 Database Object Permissions...........................................................................................76 Object Privileges..............................................................................................................77 System Privileges.............................................................................................................79 Roles: Groups of Privileges .............................................................................................80 Using Roles to Manage User Permissions.......................................................................81 Data Definition Language (DDL) Statements: CREATE …...........................................82 Data Definition Language (DDL) Statements: DROP …...............................................86 Data Definition Language (DDL) Statements: ALTER … .............................................87 Data Modification (DML) Statements: INSERT …........................................................88 Retrieving Information from Tables: SELECT … ..........................................................90 Displaying Information from Tables: SELECT ….........................................................91 Displaying Information from Tables: Customizing Output with Column Headers.........92 Sample SELECT Query...................................................................................................94 Using the WHERE Keyword to Select Specific Rows of Data .......................................95 Using Relational Operators in the WHERE Clause.........................................................96 Using Pattern Matching in the WHERE Clause ..............................................................97 Checking for Known and Unknown Quantities in the Database .....................................99 Looking for Multiple Values in the Database: IN and BETWEEN ..............................100 Creating Complex Where Clauses .................................................................................101 Sorting Output from a SELECT Query .........................................................................102 Sub-Sorting Output from a SQL Query ........................................................................103 Sorting on Non-Displayed Columns..............................................................................104 Reversing Order of Sorting Output...............................................................................105 Removing Data From a Table: DELETE.......................................................................106 Example of Using DELETE with ROLLBACK............................................................107 Modifying Existing Data in a Table: UPDATE.............................................................108 Example of Modifying Existing Data in a Table ...........................................................109 Using Data Control Language (DCL) to Control Transactions in a Database ..............110 Committing Changes .....................................................................................................111 Rolling Back Changes ...................................................................................................112 Organizing Information into Groups .............................................................................113 Using the GROUP BY Clause .......................................................................................114

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. vii All Rights Reserved

Aggregating Functions...................................................................................................116 Using Aggregate Functions with GROUP BY ..............................................................117 Using Non-Aggregated Columns with a GROUP BY...................................................122 Joining Tables Using ANSI Standard Syntax................................................................123 Joining Tables Using Oracle Syntax..............................................................................126 Lab 3 ..............................................................................................................................127 Summary ........................................................................................................................128

Chapter 4 Implementing Oracle Objects.............................................................. 133 Creating a Database .......................................................................................................135 Oracle’s Physical Database Structures ..........................................................................136 Oracle’s Logical Database Structures............................................................................138 Manually Creating a Database.......................................................................................139 Sample CREATE DATABASE Command ...................................................................141 Explanation of CREATE DATABASE Command .......................................................142 Using Control Files ........................................................................................................144 Guidelines for Creating Control Files............................................................................145 Oracle Redo Log Files ...................................................................................................147 Creating New Tablespaces.............................................................................................148 Review of CREATE TABLE.........................................................................................150 Constraints That May be Added to a Table ...................................................................151 PRIMARY KEY Constraint ..........................................................................................152 Naming the Constraints .................................................................................................153 UNIQUE Constraint.......................................................................................................154 Using the UNIQUE Constraint in Table Creation .........................................................155 Specifying the Location of a Table................................................................................156 Creating Stored Procedures and Functions....................................................................157 Creating and Executing a Procedure..............................................................................158 Example of Creating and Executing a Procedure ..........................................................159 Using Data Dictionary Views to See Information About Procedures ...........................160 Using the USER_SOURCE View .................................................................................161 Using Data Dictionary Views to Find Information about Tables..................................162 Using Triggers ...............................................................................................................163 Example of Creating and Using a Trigger .....................................................................164 Testing the Trigger.........................................................................................................165 Viewing the Contents of Triggers..................................................................................166 Lab 4 ..............................................................................................................................167 Summary ........................................................................................................................168

Chapter 5 Indexes and Storage Types................................................................... 177 Overview of Indexes ......................................................................................................179 Creating Indexes ............................................................................................................181 Creating Unique and Non-Unique Indexes....................................................................182 Building a Composite Index ..........................................................................................183 Using Indexes to Speed Data Retrieval .........................................................................184 Physical Storage of Indexes...........................................................................................185

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. viii All Rights Reserved

Creating an Index-Organized Table...............................................................................186 Hidden Indexes ..............................................................................................................187 Creating a PRIMARY KEY Constraint Using an Explicit Index..................................188 Tracking Usage of an Index...........................................................................................189 Example of Tracking Usage of an Index .......................................................................190 Lab 5 ..............................................................................................................................191 Summary ........................................................................................................................192

Chapter 6 Transactions and Locks within Oracle9i ............................................ 195 Oracle Transactions .......................................................................................................197 Transaction Management Overview..............................................................................198 Marking your Place within a Transaction: SAVEPOINT ............................................199 Rolling Back Uncommitted Transactions: ROLLBACK .............................................200 Creating Transactions ....................................................................................................201 Example of Creating and Using Transactions ...............................................................202 Implicit Transactions .....................................................................................................203 Locks..............................................................................................................................204 Oracle Transactions and Database Concurrency ...........................................................205 Explicit (Manual) Data Locking ....................................................................................206 Explicit (Manual) Locking at the Table Level...............................................................207 Table Locking Modes ....................................................................................................208 Implicit Row Locking ....................................................................................................209 Implicit Row Locking using the FOR UPDATE OF Clause.........................................210 Serializable Isolation Level ...........................................................................................211 Modifying the Isolation Level .......................................................................................214 Choosing an Isolation Level ..........................................................................................215 Recovery from a Serialization Error ..............................................................................216 Isolation Levels and Internal Locking ...........................................................................217 Lab 6 ..............................................................................................................................218 Summary ........................................................................................................................219

Chapter 7 Oracle PL/SQL and User-Defined Data Types .................................. 223 Oracle Programming Objects.........................................................................................225 Benefits of PL/SQL........................................................................................................226 Structure of a PL/SQL Block.........................................................................................228 DECLARE Section ........................................................................................................229 PL/SQL Data types ........................................................................................................230 PL/SQL Control Structures............................................................................................231 PL/SQL Conditional Statements: IF ..............................................................................232 PL/SQL Conditional Statements: CASE .......................................................................233 PL/SQL Iterative Statements: LOOP............................................................................234 PL/SQL Iterative Statements: LOOP; EXIT; RETURN ...............................................235 PL/SQL Iterative Statements: EXIT-WHEN.................................................................236 PL/SQL Iterative Statements: WHILE … LOOP..........................................................237 PL/SQL Iterative Statements: FOR … LOOP:..............................................................238 PL/SQL Sequential Control: GOTO and NULL ...........................................................239

Rev. 1.0 Copyright ©2004 Object Innovations, Inc. ix All Rights Reserved

PL/SQL Error Handling: Exceptions.............................................................................240 Exception Handling Block.............................................................................................243 Complete PL/SQL Block ...............................................................................................244 Managing Cursors..........................................................................................................245 Overview of Explicit Cursors ........................................................................................246 Declaring a Cursor .........................................................................................................247 Declaring Cursors ..........................................................................................................248 Simplifying Cursor Coding with a FOR Loop...............................................................249 Instantiating User-Defined Data Types .........................................................................252 Creating and Using a User-Defined Data Type ............................................................253 Lab 7 ..............................................................................................................................254 Summary ........................................................................................................................255

Chapter 8 Oracle and XML Integration............................................................... 261 Introduction to XML......................................................................................................263 Benefits of XML............................................................................................................265 Benefits of XML............................................................................................................266 Selecting Elements from an XML Document................................................................267 Transforming an XML Document .................................................................................268 Sample XSLT Stylesheet ...............................................................................................269 Structure of XSL Stylesheet ..........................................................................................271 Oracle Utilities and Technologies for Use with XML...................................................272 Creating a Well-Formed XML Document.....................................................................273 Rules for Well-Formatted XML Documents .................................................................274 Document Type Definition (DTD) ................................................................................277 Sample DTD ..................................................................................................................278 Storing XML Datagrams................................................................................................279 XML Extensions to Oracle ............................................................................................280 Generating XML from a Table's Contents: DBMS_XMLGEN Package......................281 Generating XML from Existing Table Data ..................................................................282 Summary of DBMS_XMLGEN Methods .....................................................................284 Combining XSQL, XSLT and XML .............................................................................285 Sample Combination XSQL File ...................................................................................286 Retrieving Data: MODEL.XSQL ..................................................................................287 Formatting the Data into XML: MODEL.XSL .............................................................288 Lab 8 ..............................................................................................................................290 Summary ........................................................................................................................291

Appendix A Acme Computer Case Study................................................................ 295

Appendix B Learning Resources .............................................................................. 305

Oracle Fundamentals OraFund Chapter 1

Rev. 1.0 Copyright © 2004 Object Innovations, Inc. 1 All Rights Reserved

Chapter 1

Introduction to Oracle9i



Introduction to Oracle9i

Objectives

After completing this unit you will be able to:

• Explain the Oracle9i relational database management system (RDBMS) at a high level.

• Explain the Oracle Enterprise Manager (OEM) and its purpose in accessing data in Oracle.

• Describe Oracle’s database objects and provide a high-level overview of their functionality.

• Describe how data are stored in an Oracle database.



Oracle RDBMS Terminology

• The Oracle RDBMS system is a collection of data (a database) and a set of processes that operate on that data (an instance).

− In most situations a single instance operates on a single database.

• The database stores and retrieves data; the instance allows many different users and many different programs to simultaneously access and manipulate the data in the database.

− Users may access the data using Oracle’s pre-written client software, or they may use driver software, such as ADO.NET for Oracle or ODBC for Oracle drivers, to access Oracle information from other programs.

• In this section we survey the data structures and system processes necessary for successful operation of the RDBMS. We will go into more detail on the structures and processes in a subsequent chapter.



Oracle’s Physical Database Structures

• Oracle databases consist of the following:

− One (or more) datafiles;

− One (or more) controlfiles;

− Two (or more) redo log files.

• Oracle uses datafiles to store its physical information.

− Data are read from datafiles as necessary during normal operation, modified, and are written back to datafiles as needed.

• Oracle uses controlfiles to internally track the physical structure of the database.

− Control files contain records to track the name of the database, the names and locations of datafiles and redo log files, and the date and time of database creation/modification.

• Oracle uses redo log files to track changes made to information in the datafiles.

− If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.



Oracle’s Logical Database Structures

• Oracle’s major logical database structures are data blocks, extents, segments, schema objects, and tablespaces.

− At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the initialization parameter DB_BLOCK_SIZE. Databases allocate, use and free space in terms of Oracle data blocks.

− The next level of logical database space is an extent. An

extent is a specific number of contiguous data blocks, obtained in a single allocation, that are used to store a specific type of information.

− Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a certain logical structure (data segment, index segment, temporary segment, undo segment).

− Database schema objects (such as tables, views, indexes and procedures) are made up of segments. A “schema” is a collection of database objects owned by a single user.

− A tablespace allows you to logically group database schemas together (for example, all of the end-users or all of the program developers) for administrative and management purposes.



Oracle Segments

• Each table’s data are stored in one or more table segments.

− Table segments are optimized for frequent reads and writes.

• Each index’ data are stored in one or more index segments.

− Index segments are optimized for frequent reading and relatively infrequent writing.

• Data that are only needed for a short time (for instance, information being sorted) will be stored in temporary segments.

− Oracle will automatically discard the temporary segments and their data after use.

• Rollback segments or undo segments store the “before” image of data that Oracle is manipulating during a transaction.

− Once the transaction is completed, the information in the rollback segment or undo segment is discarded.



Oracle Tablespaces

• Every database is divided into one or more logical storage units called tablespaces, which group related logical structures (tables, indexes, views, procedures) together.

− For example, tablespaces commonly group together all application objects to simplify some administrative operations.

− Each tablespace is made up of one or more datafiles. Datafiles, in turn, contain schemas. Schemas contain database objects (tables, indexes, views, procedures).

− Each datafile belongs to one and only one tablespace. However, tablespaces may span multiple datafiles, and schemas may span multiple tablespaces. (Schemas are conceptually easier to administer if they do not cross tablespaces, however.)



Oracle Tablespace Example

• One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.

− The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

− The combined storage capacity of a database's tablespaces is the total storage capacity of the database (6 Mb).



Oracle Datafiles

• Oracle’s datafiles are physical files on disk. They are used to store the logical objects, such as tables, views, procedures and indexes, which are commonly directly used by end-users and administrators.

− Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle. For example, assume that a user wants to access some data in a table of a database. If the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.

− Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once. A background process, called the database writer (DBWn) process, determines when data may be most efficiently written to disk.



Oracle Redo Log Files

• Every Oracle database has a set of two or more redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records).

− The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

− To protect against a failure involving the redo log itself, Oracle lets you maintain continuously updated copies of the redo log on different disks. (This is known as multiplexing the redo logs.)

− The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles.

− For example, if an unexpected power outage terminates database operation, then data in memory cannot be written to the datafiles, and the data is lost. However, lost data can be recovered when the database is opened, after power is restored. By applying the information in the most recent redo log files to the datafiles, Oracle restores the database to the time at which the power failure occurred.



Oracle Control Files

• Oracle’s control file identifies the datafiles and redo log files that must be opened for database operation to proceed.

− If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.

− Oracle will not start up if it cannot find a valid control file, or if the data files or redo log files mentioned in the control file are corrupt or unavailable. Therefore, Oracle recommends that there be at least two copies of the control file maintained for each database.

− Oracle lets you specify the path to the controlfiles in an initialization file. Oracle will keep the copies of the controlfile continuously updated and in synchronization with the database.



Oracle Enterprise Manager

• Oracle Enterprise Manager provides an integrated set of standard database administration applications to help automate and simplify the common daily tasks of administrators.

− Oracle Enterprise Manager combines Oracle's system management Console, common services, and integrated platform graphical tools.

− These supplemental applications focus on specific areas of database administration, helping administrators with their daily and routine tasks of managing databases and other services and keeping them operational.



Benefits of Oracle Enterprise Manager

• You can manage and administer all your distributed systems, databases, tools, and users from a single, central location.

− The OEM console provides an easy to use interface that can simplify tasks for the system administrator.

• A single repository consolidates and simplifies management by multiple administrators.

− In many enterprises, teams of database administrators must cooperate to manage numerous systems, often remotely. With Oracle Enterprise Manager, database management tasks can be organized and distributed in multi-administrator enterprises, and system data can be safely shared among administrators.

• The three-tiered architecture of full OEM provides scalability and “fail-over” benefits.

− Multiple OEM Management Servers can share a single repository, so if one Management Server fails, clients can re-connect to another Management Server and seamlessly continue work.

− You can add more OEM Management Servers to the middle tier to relieve an overloaded Management Server or respond to an increasing number of managed nodes or targets.



Benefits of Oracle Enterprise Manager (Cont’d)

• Oracle Enterprise Manager's flexible architecture allows new products and additional modules to be written and added to the suite.

− Oracle Enterprise Manager's (OEM) use of open standards (CORBA, IIOP, etc.) makes it possible to integrate third-party tools into the Console.

• Automated administration helps database administrators manage increasing workloads.

− OEM offers automated, "lights-out" task management and proactive target monitoring for events.

• Autonomous Intelligent Agents can perform tasks and store results if network connectivity goes down.

− Localized tasks are dependable because all jobs are processed by the Intelligent Agents on the individual managed nodes. Because jobs are executed locally at the managed nodes, they can be completed even when a crucial part of the network is down.

• OEM supports all necessary database administration functionality.

− The OEM console makes it easy for database administrators to perform fundamental, day-to-day tasks such as creating users, performing backups, adding tablespaces or datafiles, managing database objects, and changing instance parameters.



OEM Architecture

• The full-scale implementation of Oracle Enterprise Manager uses a “three-tiered” architecture to organize, manage, and separate the collection, presentation and processing of information.

− The “top,” or presentation, tier consists of the OEM Console and other graphically oriented tools.

− The “middle,” or data processing, tier uses a Management Server provide a scalable environment for processing tasks, and a database repository to provide a similarly scalable environment for storing information needed by those tasks. A repository is a set of database tables that must be located in a supported Oracle database accessible to the Oracle Management Server.

− The “bottom,” or data gathering, tier consists of intelligent “agents” running on each individual machine and executing tasks on behalf of the Management Server.



OEM Architecture (Cont’d)



Two-Tiered OEM

• Enterprise Manager is also available in standalone mode, a two-tier framework that connects directly to the databases.

− The Console launched standalone allows a single person to use one or more applications without requiring an Oracle Management Server or Intelligent Agent.

− Use the standalone Console if you want to perform basic administrative tasks that do not require the job, event, or group system.



Tier One: OEM Console

• The Oracle Enterprise Manager Console provides menus, toolbars, and drawers allowing access to Oracle tools.

− The Console uses a master/detail configuration to provide an integrated, consistent, and efficient way of managing your enterprise environment.

− When an object in the Navigator (master) is selected, the appropriate interface is displayed on the right-hand side of the Console (the detail pane).

− Information displayed on the right-hand side of the Console can be a multi-column list, property sheet, or overview page.



OEM Console



Tier Two: Oracle Management Server

• The second tier of the Oracle Enterprise Manager framework is comprised of one or more Oracle Management Servers, along with a supporting information repository.

− The Management Server is the core of the Enterprise Manager framework.

− Oracle Management Servers provide administrative user accounts, process management functions such as jobs and events, and manages the flow of information between the Console (first tier) and the nodes with Intelligent Agents (third tier).

− The Intelligent Agents are responsible for the localized execution of tasks and the on-going monitoring of databases and other targets on the managed nodes.

− Also, the Administrator can reconnect to a Management Server from within the Console should the Management Server to which the Console was originally connected become unavailable whether due to scheduled maintenance on the node or a power outage in the area where the Management Server resides.

− The Oracle Management Server uses a repository to store all system data, application data, information about the state of managed nodes, and information about any system management packs.



Multiple Management Servers

• If necessary, a large enterprise can use more than one Oracle Management Server in the middle tier.

− When multiple Oracle Management Servers are used, they share and balance the workload, ensuring high performance and scalability.



Tier Three: Managed Nodes

• The third tier of the Oracle Enterprise Manager framework is comprised of managed nodes, which contain databases and other managed targets.

− Residing on each node is an Oracle Intelligent Agent, which communicates with the Oracle Management Server(s) and performs tasks sent by consoles and client applications.



Intelligent Agents

• The Oracle Intelligent Agent monitors databases and other targets in the node for registered events (potential problem occurrences) and scheduled jobs (activities), both sent by the client via the middle-tier Oracle Management Server(s).

− Intelligent Agents function independently of the databases and other targets they support, and also function independently of the Console, management applications, and Oracle Management Servers. By running independently from other components, Intelligent Agents can perform such tasks as starting up and shutting down a database and staying operational if another part of the system is down.

− Intelligent Agents support Simple Network Management Protocol (SNMP), enabling third-party applications to communicate with the Intelligent Agent and be managed along with Oracle targets.

− The Intelligent Agent handles the collection of system performance data (for example, file I/O or CPU usage data) for Capacity Planner and Performance Manager, which are data-analysis applications in the optional Oracle Diagnostics Pack, Oracle Management Pack for Oracle Applications, and Oracle Management Pack for SAP R/3. You do not have to configure a separate data collection service for this purpose.



OEM Common Services

• OEM’s Discovery Service can automatically detect outside targets.

− When you launch the Console with a Management Server connection, you can perform service discovery through a Discovery Wizard. This wizard is used to identify nodes in your environment that you want to manage with Enterprise Manager.

• OEM’s Job System lets you automate repetitive tasks.

− OEM’s Job System enables the automation of standard and repetitive administrative tasks, such as executing a SQL script or executing an operating system command. With the Job System, you can create and manage jobs, share jobs with other administrators, schedule execution of jobs, and view information about the jobs. Jobs can be scheduled on a single node or multiple nodes in the network, provided that the node has an Intelligent Agent running on it.



OEM Common Services (Cont’d)

• OEM’s Event System lets you monitor and respond to user-definable events on the operating system as well as within Oracle.

− The Event System allows the Oracle environment to be monitored for operational occurrences and borderline conditions such as loss of service, lack of storage, and resource problems such as high CPU usage.

− In Oracle9i, you can also specify custom operating system scripts as new event tests that will be included as part of the Enterprise Manager events system. Creating such User-Defined Events expands the breadth and flexibility of the Events System by allowing you to write and incorporate custom scripts that are particular to your environment.

• OEM’s Notification Service lets you selectively choose which administrator(s) will be notified of which event(s), and in which manner(s).

− Administrators can be notified of the status of jobs and events by Console alert, e-mail, or page. When scheduling a job or registering an event, you may set up notification procedures and choose which administrators to have notified of job completion or failure or event occurrence. Notification filters allow each administrator to specify when to send an email notification as opposed to a page notification as a result of a job or event status change.



OEM Common Services (Cont’d)

• OEM’s Security System allows Super Administrators to create and customize views for other Administrators.

− Security parameters in Oracle Enterprise Manager are defined for targets, objects, and administrators.

− A Super Administrator creates and defines the access levels of all other Enterprise Manager administrators.

− A Super Administrator can access any object and control its security parameters, including objects owned by other administrators.

− A Super Administrator may also create a customized view for an administrator … letting her see only the databases she’s responsible for administering, for instance.

• OEM’s Reporting System permits quick and easy generation and publication of detailed reports on different aspects of the system.

− OEM’s Reporting System, available only in the full (three-tiered) version of OEM, provides flexible reporting functionality to administrators, permitting quick and easy access to information about the status, configuration, and performance of all monitored systems.



Oracle Database Objects

• Oracle stores most of the information to be manipulated in tables.

− Each row of a table contains a logically consistent unit of information (for example, customer id number, name, address, and phone number). Specific pieces of data are stored in columns within each row.

• Oracle uses views to provide customized windows to look at particular columns of tables.

− For instance you could create a view that shows only the computer components that are between 1/4 and 1/2 inch in width and that were created between January 1st and January 15th, 2003.

• Oracle uses procedures and functions to perform structured logical programming and manipulation of data.

− For instance, you could create a procedure that updates a tracking table whenever one of your preferred customers places an order.

• Oracle allows you to define types to logically represent information that doesn’t fit into a row-and-column structure.

− For instance, you could define an address data type to contain a street number, street address, city, state and zip code, and use that address data type to define a column in a customer table.



Oracle Table Functionality

• Tables provide a row-and-column method for storing and organizing information. Oracle uses queries (requests) to add, delete and modify information in tables.

− Information is added to a table through an INSERT statement, one row at a time.

− Information is removed from a table through a DELETE statement. (Caution – one DELETE statement may remove multiple rows of data.)

− Information is modified in a table through an UPDATE statement. (Caution – one UPDATE statement may modify multiple rows of data.).

− Information is retrieved from a table through a SELECT statement. One SELECT statement may retrieve multiple rows of data.

− SELECT statements never change the information in a table, but DELETE, UPDATE and INSERT statements can. (Those three statements are sometimes called action queries for that reason.)



Oracle Table Functionality (Cont’d)

− The following provides an illustration of a sample PART table:

Part PartId CompId Price PartSize Description QtyOnHand RestockQty RestockDate

1001 1 $50.00 1.8 99 1002 1 $70.00 2.2 50 1003 1 $100.00 2.8 50 1004 1 $150.00 3.2 50 1005 2 $20.00 64 99 1006 2 $50.00 128 100 1007 2 $125.00 256 100 1008 2 $300.00 512 100 1009 3 $100.00 10 99 1010 3 $150.00 20 100 1011 3 $200.00 40 100 1012 3 $300.00 80 100 1013 4 $75.00 10/100 Ethernet 100

• An Oracle SELECT statement may use a WHERE clause to logically join (relate) information in one table to information in another table.

− Oracle’s ability to join information among tables in this manner is why it is known as a relational database management system.

• Oracle does not use pointers or links to join information between tables.

− The joins are based solely upon whether a value in one column of a table matches a value in another column of another table.



Relating Information Between Tables

• Oracle uses a WHERE clause in a SELECT statement to relate two tables.

− The relation is based strictly upon matching the values in the columns of the WHERE clause:

select model.modelname, system.price from model , system where model.modelid = system.modelid;

− The columns to be matched do not have to have the same name:

select model.modelname, system.price from model, system where model.modelid = system.id;

− The columns are displayed in the order in which they were listed in the SELECT clause.



Using Table Aliases in SELECT Statements

• You can define a table alias (short name) in the FROM clause of a SELECT statement.

− Table aliases keep you from having to type out the table name all the time.

• You define a table alias by putting the alias after the name of the table in the FROM clause.

− A table alias is only good for a single SELECT statement.

• Once you've defined the table alias in the FROM clause, you have to use the table alias everyplace in the SELECT statement where you would normally use the table name:

select m.modelname, s.price from model m, system s where m.modelid = s.modelid;



Oracle Procedural Functionality

• Oracle’s procedures and functions allow you to perform user-defined calculations and other tasks against information in the database.

− Functions and procedures are almost identical. Functions return values, but procedures do not.

− A function is created using the CREATE FUNCTION statement. A procedure is created using the CREATE PROCEDURE statement.

− Each line of a function or procedure created by an end user is stored in the end user’s USER_SOURCE view. (DBAs can look at the ALL_SOURCE view to see all of the functions and procedures in the database.)

• When the database is started, the procedures and functions are read from the ALL_SOURCE view and are compiled into a ready-to-run format.

− The compiled procedures are stored in an area of Oracle RAM known as the System Global Area (SGA).

• Oracle allows you to permit (GRANT) or deny (REVOKE) the privilege of executing a procedure or function.

− By default, a function or procedure may only be executed by its creator.



Oracle View Functionality

• A view provides a customized window to look at the contents of one or more tables.

− The result of the view is not stored in the database itself (unless you create a specialized, advanced view – called a materialized view – which is beyond the scope of this course.)

• Views are created using the CREATE VIEW AS SELECT … statement and are removed using the DROP VIEW statement.

− The source (SELECT statement) of an end-users view may be found in the USER_VIEWS view.

− DBAs can look at the ALL_VIEWS view to see the source of every view in the database.

• You may SELECT from a view as if it were a table.

− Some views may not be INSERTed into, DELETEd from or UPDATEd without writing specialized procedures, called instead-of triggers.

• You may join a view to another view or to a table.



Oracle Synonym Functionality

• An Oracle synonym provides a nickname or alias for an existing Oracle object.

− For example, if you consistently had to access a view called EMPLOYEESFROMNEWDEPARTMENTS, you could create a synonym for that view called NEWEMP.

− Oracle would then treat SELECT … FROM NEWEMP exactly the same as SELECT … FROM EMPLOYEESFROMNEWDEPARTMENTS.

• Synonyms are generally available only to the user that created them.

− A public synonym is available to every user on the system. One must have the CREATE ANY SYNONYM privilege to create a public synonym. One would then use the CREATE PUBLIC SYNONYM statement to create a public synonym.

− Synonyms are created using the CREATE SYNONYM (private) or CREATE PUBLIC SYNONYM statement

− All synonyms, both public and private, are removed using the DROP SYNONYM statement.



Oracle Type Functionality

• Oracle types let you define your own record-like structures for more accurate representations of information.

− For example, if every customer should have an address consisting of a city, state and a zip code, you could define an address data type and use that in constructing your tables.

− Types are created using the CREATE TYPE statement and removed using the DROP TYPE statement.



Oracle Object Functionality

• Oracle objects allow you to represent data structures and entities that don’t conform to a neat row-and-column arrangement.

− An object may consist of a mixture of built-in Oracle data types and user-defined data types.

− An example object might be a customer object with a name (built-in VARCHAR data type) and an address (user-defined address data type).

− Objects are created using the CREATE OBJECT statement and are removed using the DROP OBJECT statement.



Oracle Information Storage

• Each row of data in a table is stored in a table segment.

− Segments, in turn are made up of extents, which in turn are made up of data blocks.

• Oracle attempts to keep information in a row as contiguous as possible.

− If a row of data is updated, however, and the updated row would not fit into a data block, Oracle will allocate one or more additional data blocks as necessary to hold the updated row. This is known as row chaining.

• If the information in a column is smaller than 256 characters / bytes, it will be stored directly in the row.

− If the information won’t fit, the first 256 characters/bytes are stored directly in the row, and an additional table segment is allocated to store the remaining data.



Using SQL*PLUS

• Oracle provides an interactive query tool called SQL*PLUS (or SQLPLUS).

− We will use it in this course extensive to run queries.

− You can run queries stored in a file by using the start command.

• Here is a sample, using the built in scott/tiger account.



Lab 1

Setting Up and Querying the Acme Computer Tables

Oracle operates on a concept of multiple users being able to access the same database simultaneously. Each user must have a valid username and password to log into Oracle, and must also have been granted the CREATE SESSION privilege in order to start an end-user session and issue commands against the database. Each user's collection of Oracle objects (tables, views, synonyms, procedures and so forth) is termed the user's schema.

In this lab, you will set up a schema containing the Acme Computer tables under a user called acme, with a password of computer. You will then practice using SELECT statements to view and relate information in the tables.

Detailed instructions are contained in the Lab 1 write-up at the end of the chapter.

Suggested time: 45 minutes



Summary

• The Oracle9i relational database management system (RDBMS) provides a powerful, fast means for storing, retrieving and manipulating structured data.

• The Oracle Enterprise Manager (OEM) provides a graphically oriented, one-stop console for querying and administering Oracle databases.

• Oracle uses row-and-column-formatted tables to store much of its information. Oracle uses views to provide customized windows into that information, and procedures and functions to assist in manipulating that information.

• Oracle uses the industry-standard SQL (Structured Query Language) to make queries (requests) against a database.



Lab 1

Setting Up and Querying the Acme Computer Tables

Introduction Oracle operates on a concept of multiple users being able to access the same database simultaneously. Each user must have a valid username and password to log into Oracle, and must also have been granted the CREATE SESSION privilege in order to start an end-user session and issue commands against the database. Each user's collection of Oracle objects (tables, views, synonyms, procedures and so forth) is termed the user's schema. In this lab, you will set up a schema containing the Acme Computer tables under a user called acme, with a password of computer. You will then practice using SELECT statements to view and relate information in the tables. Suggested Time: 45 minutes Root Directory: OIC\OraFund Directories: Labs\Lab1 (save your queries here) Databases (scripts for setting up database) Database: AcmeComputer Instructions 1. Install the Oracle database on your system, if needed, using the Oracle Installer.

Make a note of what the SYSTEM password is. (If the Oracle database has already been installed, simply find out what the SYSTEM password is and go directly to Step Two.

2. Connect to your Oracle database, using the username of SYSTEM and the password that you identified in Step One. For example, if the password is manager, issue the following command: connect system/manager;

3. Execute the script called AcmeTables.SQL by typing the following command: start c:\oic\orafund\databases\acmetables.sql This will create a user called acme in the database, with a password of computer; connect (log in) to the acme account, and start an Oracle user session; and create the following fifteen tables in the schema (account) of user acme: COMPONENT; COMPONENTCONFIGURATION, CONFIGURATION, CUSTOMER, CUSTOMERLOGIN, CUSTOMERUS, MODEL, ORDERPARTDETAILS,



ORDERS, ORDERSYSTEMDETAILS, PART, PARTCONFIGURATION, STATUSCODE, SYSTEM and SYSTEMDETAILS.

4. Execute the script called AcmeData.SQL, by typing the command start c:\oic\orafund\databases\acmedata.sql. This will populate the above tables with data.

5. The view called USER_TABLES contains information on the tables created in your schema. Specifically, the TABLE_NAME column of the USER_TABLES view contains the name of the tables.

a. Use the following command to verify that all fifteen of the above tables exist in your schema: SELECT table_name FROM user_tables;

b. Use the following command to display the same information, but using the ORDER BY clause to sort the information by table name: SELECT table_name FROM user_tables ORDER BY table_name;

6. The SYSTEM table contains information on the systems that were ordered and the price of those systems. The MODEL table contains information on the names of the models that are available. Each table has a MODELID column, so we can relate, or join, the two tables by searching for rows with common values.

7. Display the model and system price of each computer in the system, using Oracle syntax, using the command SELECT m.modelname, s.price from model m, system s WHERE (m.modelid = s.modelid);

8. Display the model and system price of each computer in the system using ANSI syntax, using the command SELECT m.modelname, s.price FROM model m JOIN system s ON (m.modelid = s.modelid);

Since the names of the joining columns are the same, we can take a bit of a shortcut while still retaining ANSI syntax. Display the model and system price of each computer in the system using the command SELECT m.modelname, s.price FROM model m JOIN system s USING (modelid); You should have seen the same information from all three queries. 9. Display all of the information in the SYSTEM table using the command

SELECT * FROM system; 10. Display all of the information in the MODEL table using the command

SELECT * FROM model;



Chapter 5

Indexes and Storage Types



Indexes and Storage Types

Objectives


• Explain how Oracle uses indexes.

• Use the CREATE INDEX command to create indexes.

• Understand the difference between clustered and non-clustered indexes.



Overview of Indexes

• Indexes are optional structures associated with tables and clusters.

− You can create indexes on one or more columns of a table to speed SQL statement execution on that table.

− An Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used.

• Oracle automatically maintains and uses indexes after they are created.

− Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

− Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table.

• The absence or presence of an index does not require a change in the wording of any SQL statement.

− An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.



Overview of Indexes, continued

• Indexes are logically and physically independent of the data in the associated table.

− You can create or drop an index at any time without affecting the base tables or other indexes. If you drop an index, all applications continue to work. However, access of previously indexed data can be slower.

• Indexes, as independent structures, require storage space.

− By default, indexes are created in the same tablespace as their associated table. You may use an explicit TABLESPACE <tablespacename> clause to store an index in a separate tablespace.

− Since indexes tend to be updated less frequently, and read more often, than tables, you can improve the performance of an index by storing it in a tablespace that is optimized for indexes.

− The USER_INDEXES view lets you see information about the indexes you create, such as their names and their associated tablespaces:

select index_name, table_owner, table_name, tablespace_name from user indexes order by index_name, table_name, tablespace_name;



Creating Indexes

• You would use the CREATE INDEX statement to create an index on a given table.

− You will need to specify the name of the table and the names of the column(s) on which the index is based:

CREATE INDEX basepriceindex ON model(baseprice);

− If the index is to be placed in a separate tablespace from its associated table, you would use the TABLESPACE clause to specify where the index is to be stored:

CREATE INDEX basepriceindex ON model(baseprice) TABLESPACE indextablespace;

• You would use the DROP INDEX statement to remove an index from a given table:

DROP INDEX basepriceindex;



Creating Unique and Non-Unique Indexes

• Oracle can use a unique index to implicitly enforce uniqueness on a column.

− If you use the UNIQUE qualifier (or the PRIMARY KEY qualifier) on a column, Oracle creates and manages its own internal index to enforce uniqueness on the column.

• You can also add uniqueness to an index that you create.

− You can use the UNIQUE qualifier in a CREATE INDEX statement to explicitly enforce uniqueness on a column:

CREATE UNIQUE INDEX bpuniqueindex ON model(baseprice);

− If you do not explicitly use the UNIQUE qualifier on an index that you create, the index will be a non-unique index.



Building a Composite Index

• Oracle supports composite (multiple–column) indexes:

create index namepriceindex on model (modelname, baseprice);

If you put the UNIQUE qualifier on a composite index, then the combination of columns is what has to be unique:

create unique index uniquenameprice on model (modelname, baseprice) tablespace indx;



Using Indexes to Speed Data Retrieval

• Indexes can speed data retrieval for large tables.

− If a table has more than about 100 rows, it is quicker for Oracle to use the index to find a row of data and go directly to that row than it is for Oracle to scan all of the rows of data in the table (this is known as a full table scan).

• Oracle can use an index to speed data retrieval IF there is an index on the column in the WHERE clause.

− If we have the BASEPRICE index mentioned earlier, then a query of the form select modelname from model where baseprice between 400 and 600; will use the index.

− However, the nameprice or uniquenameprice indexes would NOT be used, because the column in the WHERE clause (baseprice) was NOT the first column specified in the index creation statement.

• The USER_IND_COLUMNS view can be used to see which columns are in which positions in an index:

select table_name, index_name, column_name, column_position from user_ind_columns order by table_name, index_name, column_name, column_position;



Physical Storage of Indexes

• Traditional Oracle indexes are separate objects, stored physically separately from tables.

− The order of data in an Oracle table doesn’t necessarily match the order of items in the index.

− Since traditional indexes are physically separate structures, a standard Oracle index behaves much like a SQL Server non-clustered index.

• Oracle supports a special type of table, called an index-organized table (IOT), in which the table’s data are actually stored in the index.

− Every index-organized table MUST have a PRIMARY KEY. Rows of data in an index-organized table are stored in primary key order, just as the primary key and row identifier (rowid) are stored in a traditional index.

− Index-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key.

− Once Oracle finds the key value in the index-organized table, it doesn’t have to go to another object to get the data in the row. This improves performance by eliminating a data block access.

− An index-organized table is the Oracle analog of a SQL Server clustered index. In both cases, the index and table are essentially merged into one object.



Creating an Index-Organized Table

• Use the ORGANIZATION INDEX qualifier in the CREATE TABLE statement.

− The default table organization is HEAP, meaning that the table’s data are entered in the order of the INSERT statements (that is, the table is NOT sorted internally in any way).

− When you specify ORGANIZATION INDEX, Oracle keeps the index – and, thereby, the data in the table – sorted by primary key value.

• Creating a heap-organized table (default behavior): CREATE TABLE model (modelid NUMBER PRIMARY KEY, modelname VARCHAR2(10), baseprice NUMBER) ORGANIZATION HEAP;

• Creating an index-organized table: CREATE TABLE model (modelid NUMBER PRIMARY KEY, modelname VARCHAR2(10), baseprice NUMBER) ORGANIZATION INDEX;



Hidden Indexes

• Oracle creates its own indexes for PRIMARY KEY and UNIQUE columns.

− Oracle uses these indexes to enforce the requirements in these columns that every value be different.

• The USING INDEX clause may be added to an out-of-line PRIMARY KEY or UNIQUE constraint to explicitly name the hidden index on the column and specify the tablespace in which the index will be stored.

− If no tablespace is mentioned, than the index is stored in the same tablespace as the table.

− Oracle recommends that tables and indexes be kept in separate tablespaces. Tables benefit from relatively small block sizes (more efficient insertion of data) and indexes benefit from relatively large block sizes (more efficient reads of information), and the only way to accomplish this is through separate tablespaces.



Creating a PRIMARY KEY Constraint Using an Explicit Index

• Here is an example of creating a table with an explicitly-indexed PRIMARY KEY:

CREATE TABLE model (modelid NUMBER, modelname VARCHAR2(10), baseprice NUMBER, PRIMARY KEY (modelid) USING INDEX (CREATE INDEX model_pk_index ON model(modelid) tablespace indx) ) tablespace users;

• Here is an example of altering a table after creation to add an explicitly-indexed PRIMARY KEY:

ALTER TABLE model ADD CONSTRAINT model_pk PRIMARY KEY (modelid) USING INDEX (CREATE INDEX model_pk_index ON model (modelid) tablespace indx ) ;



Tracking Usage of an Index

• We noted earlier that Oracle will use an index to speed up its SELECTs if the index is built upon the column used in the WHERE clause.

− If the index is a composite (multiple-column) index, then Oracle will use the index if the first column of the index is the one that is mentioned in the WHERE clause.

• Oracle can use the MONITORING USAGE clause of ALTER INDEX to track access to the index.

− You should turn on monitoring of the index before running a query that you expect to use the index. Use the statement ALTER INDEX <indexname> MONITORING USAGE to do so.

− The V$OBJECT_USAGE view then tracks whether the index has been used (specifically, the USED column.)

− Both SELECT queries that reference the indexed column in the WHERE clause and ORDER BY clauses that reference the indexed column will be monitored.

− After your tests, you should disable monitoring using the ALTER INDEX <indexname> NOMONITORING USAGE statement.

• If an index isn't being used by a reasonable percentage of your queries, you may consider DROPping the index.



Example of Tracking Usage of an Index

• Monitoring the index MODEL_PK_INDEX: SELECT used FROM V$object_usage WHERE index_name = 'MODEL_PK_INDEX'; no rows returned ALTER INDEX model_pk_index MONITORING USAGE; Index altered. SELECT baseprice FROM model WHERE modelid = 1; BASEPRICE ---------- 400 SELECT used FROM V$object_usage WHERE index_name = 'MODEL_PK_INDEX'; USE --- YES

ALTER INDEX model_pk_index NOMONITORING USAGE; Index altered.



Lab 5

Working with Indexes

In this lab, you will review indexes created against the Acme Computer tables, and create additional indexes of your own to improve performance and consistency.





Summary

• Oracle uses indexes to speed retrieval of data from tables.

• Oracle automatically builds and maintains hidden indexes on PRIMARY KEY and UNIQUE columns.

• Oracle allows you to create a special kind of table, called an index-organized table, which stores its data in the rows of the index.



Lab 5

Implementing Oracle Objects

Introduction In this lab, you will review indexes created against the Acme Computer tables, and create additional indexes of your own to improve performance and consistency. Part 1. Adding Indexes We said earlier in the lesson that primary key constraints were a way to enforce logic in the database. We will practice this by adding a primary key constraint to a table, and in addition, name the index that will be used to enforce that constraint. We will then test the usage of that index.

1. Create a primary key constraint, called CUSTOMER_ID_PK, on the CUSTOMERID column of the CUSTOMER table. Name the index associated with that constraint CUSTOMER_ID_INDEX. Your statement should look like this:

ALTER TABLE customer ADD CONSTRAINT customer_id_pk PRIMARY KEY (customerid) USING INDEX (CREATE INDEX customer_id_index ON customer (customerid));

Part 2. Monitoring Indexes Now, let's see how to monitor usage of this index. Since we built the index on the CUSTOMERID column of the CUSTOMER table, SELECT queries against the CUSTOMER table that use CUSTOMERID in the WHERE clause should be noted in V$OBJECT_USAGE.

2. Verify that the USED column of V$OBJECT_USAGE does not show anything for the index:

SELECT used FROM v$object_usage WHERE lower(index_name) = 'customer_id_index';

3. Begin monitoring of the index:

ALTER INDEX customer_id_index MONITORING USAGE;

4. Display the MONITORING and USED columns of the view. Verify that monitoring is on, and the index has NOT been used:

SELECT monitoring, used FROM v$object_usage WHERE lower(index_name)='customer_id_index';



5. Perform a SELECT statement that does not sort information:

SELECT * from customer;

6. Look at the MONITORING and USED columns again. What are their values? Why was the index not used?

7. Perform a SELECT statement that does sort information:

SELECT * from customer ORDER BY customerid;

8. Look at the MONITORING and USED columns again. What are their values? Why was the index used?


9. Turn off monitoring of the index:

ALTER INDEX customer_id_index NOMONITORING USAGE;

10. Verify that the MONITORING column for the index says 'NO':


We will now test the monitoring of the index when a WHERE clause is used.

11. Turn monitoring back on for the index. Verify that the index is being monitored, and that it has not been used.

12. Perform a SELECT statement that does not use the CUSTOMERID column in the WHERE clause:

select customerid, lastname from customer order by firstname;

13. Query the USED column of V$OBJECT_USAGE for the index. Was it used? Why – or why not?

14. Perform a SELECT statement that uses the CUSTOMERID column in the WHERE clause:

select lastname, firstname from customer where customerid < 3 order by lastname desc;

15. Query the USED column of V$OBJECT_USAGE for the index. Was it used? Why – or why not?



Chapter 8

Oracle and XML Integration



Oracle and XML Integration

Objectives


• Explain what Extensible Markup Language (XML) is.

• Use a Document Type Definition (DTD) to define the contents of a valid XML document.

• Use XSL (Extensible Stylesheet Language) to transform XML into another desired output format.

• Write simple XML documents.

• Store XML information in an Oracle database.

• Retrieve XML information from an Oracle database.



Introduction to XML

• Extensible Markup Language (XML) is a specification to use text-based documents to present structured information in a hierarchical (tree-structured) and unambiguous manner.

− XML documents use a text with tags format that looks much like HTML.

− The XML standard (syntax for encoding information) is available on the Internet.

• XML allows you to create your own tags to structure your information.

− You may also re-use an existing set of tag specifications.

− For example, if a CPU cannot exist outside of a computer, you could have tags that look like this:

<computer-list> <computer custid="7"> <cpu speed="2000">Pentium</cpu> <model>Gamer</model> </computer> </computer-list>



Introduction to XML (Cont’d)

• An XML vocabulary is a set of XML tags designed to encode data of a particular kind.

• A Document Type Definition (DTD) is a text file that defines which XML tags are legal in your vocabulary.

− The DTD standard (syntax for how to define a DTD) is also available on the Internet.



Benefits of XML

• You can edit XML using a text editor or by using more sophisticated XML editors.

− XML files are just text files (with a suffix of .xml), so they can be edited in much the same manner as HTML files.

− A graphically oriented XML editor can present your XML information in a hierarchical, structured environment.

• You may send and receive XML documents using standard Internet protocols, such as FTP, SMTP or HTTP.

− An XML document has an HTTP content type of 'text/xml', just as HTML documents have a content type of 'text/html.' Therefore, browsers can pass XML information back and forth with ease.

− Similarly, XML documents can be uploaded to, or downloaded from, machines using FTP, or sent as contents of (or attachments to) email using SMTP.

− XML documents that contain structured information, such as the CPU-inside-computer information you just saw, are known as XML datagrams.

Benefits of XML

• You may generate XML using server-side programs.

− XML provides a powerful means to access and use information that might have been stored in a previously inaccessible format.

− Since Oracle can use the DBMS_OUTPUT.PUT_LINE() routine to write out text, you can have Oracle generate XML datagrams containing information retrieved from the database. Just print out the information retrieved from your SELECT statement and surround it with appropriate XML tags!

− Those XML datagrams can then be used by any program that knows how to read and interpret XML, such as a Web browser, cell phone or PDA.

• XML code is generally human-readable, but you can add comments to improve clarity and readability.

− Comments begin with the string  (two dashes followed by a right chevron).

− Comments may go anywhere after the XML declaration except inside attribute values and inside the boundaries of a tag. Multiple-line comments are OK.



Selecting Elements from an XML Document

• The XML Path Language (XPath) is a standard for selecting items of value from an XML document.

− XPath queries select information from the logical tree-structured data model of an XML document's information set (or infoset), not from the raw text representation.

• XML uses a hierarchical, URL-like notation to access items of value.

− For example, to access the type of CPU used, you could use the following syntax:

/computer/cpu

− To access the speed of the CPU, you could prefix the attribute with an "at sign" (@) :

/computer/cpu/@speed



Transforming an XML Document

• The XSLT (XML Stylesheet Language for Transformations) is a specification for how to transform a source XML document's information set into a result information set of a different structure.

− An XSL stylesheet contains a series of tags beginning with either <xsl> or </xsl>; these tags control XSL processing.

− Other tags found in the document are output literally.

− An XSLT processor uses the source XML document and an XSL stylesheet to transform data from one DTD to another.

Sample XSLT Stylesheet

• Here is a sample XSLT stylesheet to operate on the XML document we saw earlier:

<?xml version='1.0' encoding='ISO-8859-1'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XML/TRANSFORM">  <xsl:output indent='yes' /> <xsl:template match='/'> <computer-order custid='7'>  <xsl:for-each select='/[computer/@custid='7']'> <cpu-order> <computer-model> <xsl:value-of select='cpu'/></computer-model> <computer-speed> <xsl:value-of select='@speed'/></computer-speed> </cpu-order> </xsl:for-each> </computer-order> </xsl:template> </xsl:stylesheet>



Sample XSLT Stylesheet (Cont’d)

• This stylesheet transforms the previous XML into the following:

<computer-order custid='7'> <cpu-order> <computer-model>Gamer</computer-model> <computer-speed>2000</computer-speed> </cpu-order> </computer-order>



Structure of XSL Stylesheet

• The first five lines of the stylesheet (down to and including <xsl:template> ) are canned, “boilerplate” text.

− The xmlns parameter designates an XML name space – a collection, or grouping, of names. You may have the same variable or attribute name in different namespaces without conflict. Internet URLs provide a convenient means of declaring namespaces, since the hostnames are (almost) guaranteed to be unique.

• The <xsl:for-each> is used to loop over each row of data coming back from the query.

− Here, we are explicitly querying for the computer whose associated customer id is 7.

− More generally, this line will appear as <xsl:for-each select="ROWSET/ROW"> if you are retrieving table data using one of the XML functions to follow.

• Within each row of the <xsl:for-each>, the <xsl:value-of> tag is used to select a value in the current row.

− The values get enclosed in appropriate XML tags – look at the <computer-model> and<computer-speed> tags.

• The last two lines are also canned, boilerplate text. Other tags are output literally.



Oracle Utilities and Technologies for Use with XML

XML Parser Parse, construct and

validate XML documents XPath Engine Searches in-memory XML

documents XSLT Processor Uses XSLT to transform XML

documents XML SQL Utility Produces XML from SQL and

inserts XML into tables

• Oracle's XML Parser lets you work with the data in an XML datagram.

− You can use the parser to parse documents into their "infoset"(information stored within them,) manipulate their contents, and return the modified infoset in XML format.

• Oracle's XSLT Processor lets you transform XML documents.

− The Processor lets you turn XML into other XML, HTML, or text of another structure.

− Both the Oracle XML Parser and the Oracle XSLT processor use the Oracle XPath engine to help select interesting subsets of information from an XML infoset.



Creating a Well-Formed XML Document

• A well-formed XML document is one that conforms to a parsing and writing standard.

− Since XML tags are essentially defined through a DTD, a series of rules had to be developed and enforced to ensure consistent processing and validation across XML documents.

− We will discuss those rules in the following pages.

Rules for Well-Formatted XML Documents

• Every document must begin with an XML declaration.

− The XML declaration begins with <? and ends with ?>.

− A simple XML declaration looks like this: <?xml version="1.0"?>

− NO spaces, carriage returns, or any other text may come before the <?

• Your document must have only ONE top-level document element.

− The first element in an XML document – the document element – identifies the kind of document being enclosed.

• XML is case sensitive, so you must match opening and closing tags.

− For example, this doesn't work (the C is not consistently capitalized):

<Cpu-speed>2000</cpu-speed>

• Use comments freely, except inside attribute values and between the < and > brackets of a tag.

− For example, this won't work:

<cpu-speed  >2000</cpu-speed>

Rules for Well-Formatted XML Documents (Cont’d)

• Element and attribute names must start with a letter.

You cannot include spaces in an element or attribute name, but you can use underscores and hyphens.

• If an element uses attributes, put them in the opening tag of the element.


<cpu>  </cpu speed="2000">

• An attribute's quotes (single or double) must match.


<cpu speed='2000">



Rules for Well-Formatted XML Documents (Cont’d)

• Use the HTML literals < and & when you need a left chevron ("less-than sign" or an ampersand.

− For example, this is how you would represent an ampersand in a company name:

<company>Johnson & Johnson</company>;

• Use "empty element" syntax when possible.

− A tag that does not contain other nested elements or text may be written as follows:

<tag />



Document Type Definition (DTD)

• A document type definition (DTD) defines all of the valid element names that are part of a particular XML vocabulary.

− You may think of a DTD as a dictionary of what is syntactically permissible in your XML document.

− It specifies the valid combinations of elements that are allowed and what attributes each element is supposed to have.

• A document type definition contains elements and attributes of those elements.

− Elements are listed between <!ELEMENT and > tags.

− Attributes of the element are listed between <!ATTLIST and > tags.

− Elements whose data are just "raw data" are listed with the #PCDATA (parsed character data) specification.

− Elements whose data might possibly contain tags for further expansion are listed with the CDATA (character data) specification.

− The #REQUIRED tag is used to indicate an attribute that is required … for example, the customer id on this order or the speed of the CPU.

− Remember – all of the above strings (ELEMENT; ATTLIST; PCDATA; CDATA; REQUIRED) must be in UPPER CASE!



Sample DTD

• Here is a sample DTD for the computer model XML code we saw earlier: <!ELEMENT computer-list (computer) >

<!ELEMENT computer (cpu, model) >

<!ATTLIST computer custid CDATA #REQUIRED >

<!ELEMENT cpu (#PCDATA )>

<!ATTLIST cpu speed CDATA #REQUIRED >

<!ELEMENT model (#PCDATA)>

• XML entities are used to represent boilerplate text.

− Although we aren't using it here, if we wanted to have some canned text in the document, we could have used the <!ENTITY tag to define the canned text, and then used it later in the DTD or XML:

<!ENTITY theModel "Gamer" > <model>&theModel;</model>



Storing XML Datagrams

• You can store XML datagrams in one of several ways.

− You can store an XML document's information content broken down into the columns of one or more tables.

− You can store the entire XML document intact in a single column in the database, with an ID value that plays the role of the document ID.

− You can blend the two approaches, storing some of the pure data (cpu speed, baseprice, and so forth) in their own columns, and store longer, more descriptive text in a table column.

− Oracle's XML SQL Utility (oraxsql or xsql, depending upon installation) can be used to transform XSQL pages to XML.

− An XSQL page, properly set up, can connect to an Oracle database, cause a query to be executed, and return the results of the query as XML.



XML Extensions to Oracle

• Oracle9i Release Two supports several extensions that comply with the SQL/XML ANSI standard.

− XMLElement takes an element name, an optional collection of attributes for the element, and arguments that make up the element's content and returns an instance of type XMLType.

− XMLForest converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.



Generating XML from a Table's Contents: DBMS_XMLGEN Package

• The DBMS_XMLGEN packages helps in generating XML content directly from a table.

− The DBMS_XMLGEN package helps convert the results of a SQL query to a canonical XML format.

− The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB.

• The package uses a context handle to obtain the SQL results for processing into XML.

− A context handle may be thought of as a read-only cursor (buffer) holding the results of the SQL query.

− Once the context handle has been obtained, the results of the query can be converted to XML.

− Methods are available to control the maximum number of rows processed, determine how many rows are processed at one time, and change the <ROW> and <ROWSET> tags, if needed.

− Once the conversion finishes, you can close the context handle and free the resources that you used.



Generating XML from Existing Table Data

• Here is a template example of using the DBMS_XMLGEN methods.

− We are creating a procedure so that we can easily re-execute the commands to generate XML from a table.

− We are inserting the XML result into the temp_clob_tab table so that we can review it later.

CREATE TABLE temp_clob_tab(result CLOB); CREATE OR REPLACE PROCEDURE MakeXMLFromModel AS BEGIN DECLARE qryCtx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN -- get an XML context handle qryCtx := dbms_xmlgen.newContext('SELECT * from model'); -- set the row header to be COMPUTER DBMS_XMLGEN.setRowTag(qryCtx, 'COMPUTER'); -- now get the result result := DBMS_XMLGEN.getXML(qryCtx); -- store it for future use INSERT INTO temp_clob_tab VALUES(result); --close context DBMS_XMLGEN.closeContext(qryCtx); END; END; /



Generating XML from Existing Table Data (Cont’d)

− We can test our code as follows:

truncate table temp_clob_tab; -- make sure it is empty

execute MakeXMLFromModel(); -- generate the XML

set long 10000; -- so the XML string is not truncated

select * from temp_clob_tab; -- view results



Summary of DBMS_XMLGEN Methods

• Here are the methods of the DBMS_XMLGEN package:

NewContext() Creates a new context handle. SetRowTag() Sets the name of the element enclosing each

row of the result. (The default tag is ROW.) SetRowSetTag() Sets the name of the element enclosing the

entire result. (The default tag is ROWSET.) GetXML() Gets the XML document. GetNumRowsProcessed()

Gets the number of SQL rows that were processed in the last call to getXML.

SetMaxRows() Set the maximum number of rows to be processed each time. (Default is all rows.)

SetSkipRows() Set the number of rows to skip each time before generating XML. (Default is zero – that is, don't skip any rows.)

SetConvertSpecialChars()

Sets whether special characters such as $, which are non-XML characters, should be converted or not to their escaped representation. The default is to perform the conversion.

Convert() Convert XML to escaped or non-escaped version.

RestartQuery() Restart your query from the beginning. CloseContext() Close the context handle and free all XML

resources.



Combining XSQL, XSLT and XML

• An XSQL file can perform a query against a database, and use an XSLT stylesheet to transform its output into XML or HTML.

− The XSQL file would start with an <xml-stylesheet> tag, to indicate the stylesheet being used to transform the data.

− The stylesheet will transform the <ROWSET>/<ROW> results obtained from an XSQL query into XML (or even HTML, if you so desire.)

− But how do we get the XSQL query to work? Easy – we write an XSQL file that queries the database. It will return its results in <ROWSET>/<ROW> format.

Sample Combination XSQL File

• Here is an example of such an XSQL file: <?xml version="1.0"?>  <xml-stylesheet type="text/xsl" href="model.xsl" ?> <xsql:include-xsql href="model.xsql" xmlns="urn:oracle/xsql" />.

− It's a very small file; most of the work is being done by model.xsql (retrieving the data) and model.xsl (formatting the data).

− Let's review the model.xsql file, and take a closer look at model.xsl.

Retrieving Data: MODEL.XSQL

• The query-retrieval XSQL looks like this: <?xml version="1.0" ?>  <xsql-query connection="MyConnection" xmlns:xsql="urn:oracle-xsql"> select * from model; </xsql-query>

• The <xsql-query> and </xsql-query> tags enclose the body of the SQL query.

− The connection attribute (MyConnection) maps to a connection parameter in XSQLConfig.xml. (You can duplicate the existing "demo" connection in the XSQLConfig.XML file, which logs into username scott, password tiger, and then use the original as a template.)

− The XML namespace attribute is also a piece of boilerplate for XSQL files.

Formatting the Data into XML: MODEL.XSL

• Review of XSL Stylesheet structure: <?xml version='1.0' encoding='ISO-8859-1'?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XML/TRANSFORM">  <xsl:output indent='yes' /> <xsl:template match='/'>  <xsl:for-each select> <!—Inner XML element tag goes here --> <xsl:value-of select> <!—iterate over values returned from query --> <xsl:value-of select>  </xsl:for-each>  </xsl:template> </xsl:stylesheet>

• You can use this as a template for constructing future XSL stylesheets for transformation to XML.



Formatting the Data into HTML: MODEL-HTML.XSL

• Here is how the stylesheet might look if it were translating information directly to HTML:

<html xsl:version='1.0' xmlns:xsl="http://www.w3.org/1999/XML/TRANSFORM"> <head><title>YOUR TITLE HERE</title></head> <body><center><table border="0"> <tr><td>Model Name</td> <td>Model ID</td> <td>Base Price</td> </tr> <xsl:for-each select="ROWSET/ROW"> <tr> <td><xsl:value-of select="MODELNAME"></td> <td><xsl:value-of select="MODELID"></td> <td><xsl:value-of select="BASEPRICE"></td> </tr> </xsl:for-each> </table> </center> </body> </html>

• Note that the stylesheet looks like plain old HTML with the exception of the <xsl:for-each> and <xsl:value-of> tags.

− In fact, if you already have HTML that looks like what you want, just make a copy of the HTML file, put in the tags that you need to construct a table containing the retrieved information, rename the file to .XSL, and use it as the XML stylesheet for a .XSQL file.



Lab 8

Querying the Database Using XML

In this lab, you will create and test a user-defined procedure to add new items to the MODEL table of the Acme Computer database.





Summary

• XML is an emerging, platform-independent, tag-based standard for developing languages for information exchange.

• XML vocabularies are sets of valid XML tags.

• An XSL (Extensible Stylesheet Language) document can transform XML across vocabularies, or can transform XML into HTML.

• An XSQL (XML for Structured Query Language) document can connect to and query Oracle databases, then transform the results using an XSL stylesheet.

• Oracle supports methods in the DBMS_XMLGEN package to store retrieve and format XML data.



Lab 8

Querying the Database Using XML

Introduction In this lab, you will create and test a user-defined procedure to add new items to the MODEL table of the Acme Computer database. Suggested Time: 45 minutes Part 1. Setting Up the Configuration 1. The file XSQLCONFIG.XML contains the configuration for a browser to establish a

connection to your Oracle database. Find that file on your system (usually under the XDK\ADMIN directory), and open it in a text editor.

2. Find the following code in the file:

<connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> 3. Copy the code and then paste the copy immediately under the original, so it looks like

this:

<connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> <connection name="demo"> <username>scott</username> <password>tiger</password> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl> <driver>oracle.jdbc.driver.OracleDriver</driver> </connection> 4. Change the second demo to read acme, change the second scott to read acme and

change the second tiger to read computer.

Part 2. Setting up the Files 5. The courseware discussed three files: model.xsql, model.xml and model.xsl. To

review the structure:



− You would open the file model.xsql in the browser.

− The file would query the database using model.xml.

− The file would transform the output into XML or HTML using model.xsl. 6. Create the three files, as shown in the lecture. Copy those three files into your

system's xsql directory.

Part 3. Executing the Procedure Test your example by opening the model.xsql file in your browser. You should see all of the entries from your model table, formatted into clean XML. This information can, in turn, be interpreted by any XML-compliant browser, or further transformed for output to another device.