Oracle9i Sample Schemas Release 1 (9.0.1) June 2001 Part No. A90129-01 Oracle9i Sample Schemas, Release 1 (9.0.1) Copyright © 1996, 2001, Oracle Corporation. All rights reserved. Contributors: Alexander Hunold, Diana Lorentz, Neena Kochhar, Lex de Haan, Nancy Greenberg, Nagavalli Pataballa, Den Raphaely, David Austin, Bill Gietz, Hermann Baer, Shelley Higgins, Brajesh Goyal, Shailendra Mishra, Geoff Lee, Susan Mavris The Programs (which include both the software and documentation) contain proprietary information of Oracle Corporation; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited. The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. IftheProgramsaredeliveredtotheU.S.Governmentoranyonelicensingorusingtheprogramsonbehalfof the U.S. Government, the following notice is applicable: Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computersoftware"anduse,duplication,anddisclosureofthePrograms,includingdocumentation,shallbe subjecttothelicensingrestrictionssetforthintheapplicableOraclelicenseagreement.Otherwise,Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19, Commercial ComputerSoftware-RestrictedRights(June,1987).OracleCorporation,500OracleParkway,RedwoodCity, CA 94065. The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs. Oracle, Oracle9i, Oracle Database Configuration Assistant, Oracle Enterprise Manager, Oracle Net, SQL*Plus, and Oracle Real Application Clusters are registered trademarks of Oracle Corporation. LogMiner is a trademark of Oracle Corporation. Other names may be trademarks of their respective owners. Contents Send Us Your Comments.................................................................................................................... v Preface........................................................................................................................................................... vii 1 Installation Using the Database Configuration Assistant................................................................................ 1-2 Manually Installing the Oracle9i Sample Schemas..................................................................... 1-3 Resetting the Sample Schemas...................................................................................................... 1-10 2 Rationale Overall Description............................................................................................................................ 2-2 Human Resources (HR)..................................................................................................................... 2-2 Order Entry (OE)................................................................................................................................. 2-3 Product Media (PM)........................................................................................................................... 2-4 Queued Shipping (QS)...................................................................................................................... 2-4 Sales History (SH)............................................................................................................................... 2-5 3 Diagrams 4 Oracle9i Sample Schema Scripts About the Scripts................................................................................................................................ 4-2 Master Script........................................................................................................................................ 4-2 Human Resources (HR) Scripts........................................................................................................ 4-4 Order Entry (OE) Scripts................................................................................................................. 4-40 iii Product Media (PM) Scripts............................................................................................................ 4-72 Queued Shipping (QS) Scripts....................................................................................................... 4-80 Sales History (SH) Scripts............................................................................................................. 4-120 iv Send Us Your Comments Oracle9i Sample Schemas, Release 1 (9.0.1) Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this document. Your input is an important part of the information used for revision. n Did you find any errors? n Is the information clearly presented? n Do you need more information? If so, where? n Are the examples correct? Do you need more examples? n What features did you like most? If you find any errors or have any other suggestions for improvement, please indicate the document title and part number, and the chapter, section, and page number (if available). You can send com- ments to us at: n E-mail [email protected] n Postal service: Oracle Corporation Server Technologies Curriculum Development 500 Oracle Parkway, Mailstop 4op1090 Redwood Shores, CA 94065 USA If you would like a reply, please give your name, address, telephone number, and (optionally) elec- tronic mail address. If you have problems with the software, please contact your local Oracle Support Services. v Preface Oracle has been using the schemaSCOTT with its two prominent tablesEMP and DEPTtablesforalongtime.WithadvancesinOracle’stechnology,thesetableshave becomeinadequatetoshoweventhemostbasicfeaturesoftheOracledatabaseand other Oracle products. As a result, many other schemas have been created over the years to suit the needs of product documentation, courseware, and software development and application demos. This preface contains these topics: n Audience n About the Sample Schemas n What Are the Customer Benefits? n What are the Oracle9i Sample Schemas Design Principles? n Conventions n Documentation Accessibility vii Audience This book is for all users of the seed database that is installed when you install Oracle. About the Sample Schemas The new Oracle9i Sample Schemas serve the purpose of providing a common platform for examples in Oracle9i and future releases. It is not possible to convert all examples throughout our documentation to this new environment at one time, but all examples will be converted as material is updated. The new Oracle9i Sample Schemas are a set of interlinked schemas. This set of schemas is aimed at providing a layered approach to complexity: n A simple schema (Human Resources,HR) for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos. n A second schema (Order Entry,OE) for dealing with matters of intermediate complexity. A multitude of datatypes is available in this schema. The Online Catalog (OC) subschema is a collection of object-relational database objects built inside theOE schema. n A schema dedicated to multimedia datatypes (Product Media,PM) n A set of schemas gathered under the main schema nameQS (Queued Shipping) to demonstrate Oracle Advanced Queuing capabilities. n A schema designed to allow for demos with larger amounts of data (Sales History,SH). An extension to this schema provides support for advanced analytic processing. What Are the Customer Benefits? n Continuity of context. When encountering the same set of tables everywhere, users, students, and developers spend less time with the schema and more time understanding or explaining the technical concepts. n Usability. Customers can use these schemas in the seed database to run examples that are shown in Oracle documentation and training materials. This first-hand access to examples will facilitate both conceptual understanding and application development. viii n Quality. Through central maintenance and testing of both the creation scripts that build the Oracle9i Sample Schemas and the examples that run against the schemas, the quality of Oracle documentation and training materials will be enhanced. What are the Oracle9i Sample Schemas Design Principles? n Simplicity and Ease of Use. TheHR andOE schemas should not become overly complex by the addition of features, but rather should provide a graduated path from the simple to intermediate levels of database use. n Be fundamental. The base schemas and the extensions should bring to the foreground the functionality that customers typically use. Only the most commonlyuseddatabaseobjectsarebuiltautomaticallyintheschemas,andthe entire set of schemas provides a foundation upon which one can expand to illustrate additional functionality. n Extensibility. The Oracle9i Sample Schemas provide a logical and physical foundation for adding objects to demonstrate functionality beyond the fundamental scope. n Relevance. The Oracle9i Sample Schemas are designed to be applicable to e-business and other significant industry trends (for example, XML). When this goal conflicts with the goal of simplicity, schema extensions are used to showcase the trends in focus. Conventions This section describes the conventions used in the text and code examples of this documentation set. It describes: n Conventions in Text n Conventions in Code Examples Conventions in Text We use various conventions in text to help you more quickly identify special terms. The following table describes those conventions and provides examples of their use. ix Convention Meaning Example Bold Bold typeface indicates terms that are When you specify this clause, you create an defined in the text or terms that appear in index-organized table. a glossary, or both. Italics Italic typeface indicates book titles or Oracle9i Concepts emphasis. Ensure that the recovery catalog and target database donotreside on the same disk. UPPERCASE Uppercase monospace typeface indicates You can specify this clause only for aNUMBER monospace elements supplied by the system. Such column. (fixed-width elements include parameters, privileges, You can back up the database by using the font) datatypes, RMAN keywords, SQL BACKUP command. keywords,SQL*Plusorutilitycommands, packages and methods, as well as Query theTABLE_NAME column in theUSER_ system-suppliedcolumnnames,database TABLES data dictionary view. objects and structures, usernames, and Use theDBMS_STATS.GENERATE_STATS roles. procedure. lowercase Lowercase monospace typeface indicates Entersqlplus to open SQL*Plus. monospace executables, filenames, directory names, The password is specified in theorapwd file. (fixed-width and sample user-supplied elements. Such font) elements include computer and database Back up the datafiles and control files in the names, net service names, and connect /disk1/oracle/dbs directory. identifiers, as well as user-supplied Thedepartment_id,department_name, database objects and structures, column andlocation_id columns are in the names, packages and classes, usernames hr.departments table. and roles, program units, and parameter values. Set theQUERY_REWRITE_ENABLED initialization parameter totrue. Note:Some programmatic elements use a mixture of UPPERCASE and lowercase. Connect asoe user. Enter these elements as shown. TheJRepUtil class implements these methods. lowercase Lowercase monospace italic font You can specify theparallel_clause. monospace represents placeholders or variables. RunUold_release.SQL whereold_ (fixed-width releaserefers to the release you installed font) italic prior to upgrading. Conventions in Code Examples Code examples illustrate SQL, PL/SQL, SQL*Plus, or other command-line statements. They are displayed in a monospace (fixed-width) font and separated from normal text as shown in this example: SELECT username FROM dba_users WHERE username = ’MIGRATE’; x