Introduction
Oracle is the most prevalent Relational Database Management System (RDBMS) in the world. Information technology professionals will find Oracle serving the database needs of the enterprise in manufacturing, financial, pharmaceutical, and telecommunications environments all over the globe. The Oracle database runs on every major machine and under every prominent operating system available. Versions of Oracle exist for platforms from modestly equipped portable computers up to powerful database servers accommodating thousands of concurrent users. Oracle databases act as on-line transaction processing engines, decision support systems, data warehouses, and repositories for web-enabled applications. The prevalence and flexibility of Oracle have given rise to groups of information technology professionals—database administrators, application programmers, and database designers—whose skill sets are tailored to leveraging an investment in the Oracle database.
No information technology professional looks forward to frantic migration efforts or to discarding legacy applications. The very strength of Oracle’s installed base can make capitalizing on the promise of new technology difficult. One such technology is object orientation. Object oriented databases are certainly not new; industry-wide acceptance of object databases, however, has not been forthcoming. Oracle8, the next major release Oracle, promises transparent support of legacy applications and object oriented extensions. This long-awaited product will generate a considerable amount of questions among Oracle information technology professionals. The answers to these questions are at the heart of this book.
The first edition of Oracle How-To posed and answered myriad real-world questions confronting anyone who worked with Oracle7. The idea behind this work was simple and appealing to any Oracle practitioners who explained, “I understand what a database does, I know my way around computers in general, and I’m tired of looking through thousands of pages of documentation only to wind up deciphering railroad diagrams. Just show me how to get my Oracle work done.” Oracle8 How-To extends this popular and easy-to-read format to Oracle8 in 17 chapters full of what anyone who wants to understand Oracle8 needs to know. This second edition includes six completely new chapters and a significant number of new additions to topics covered in the first edition.
NEW CHAPTER! Chapter 1, “Selected Database Instance Installation Topics,” describes the process of creating a new Oracle instance, establishing communication with it using Oracle utility programs such as SQL*Plus, Server Manager, and SQL Worksheet. This chapter also addresses what a DBA needs to do to configure a new instance for production use.
Chapter 2, “SQL*Plus,” will show you how to improve your productivity with the tool that provides the most direct access to an Oracle database instance.
Chapter 3, “Database Users,” begins with the simple operation of adding user accounts and subsequently introduces database administration tasks like determining who is connected to the database and how to disconnect user sessions. The How-To’s in this chapter provide a suite of helpful reports about user access privileges, defaults, and profiles.
Chapter 4, “Tables,” covers creation and management of this most elementary database structure. This series of How-To’s also introduces new Oracle8 topics including index-only and partitioned tables.
NEW CHAPTER! Chapter 5, “Indexes,” focuses on index creation and management tasks like detecting and rebuilding unbalanced indexes. New Oracle8 functionality explained in this chapter includes partitioned and bit-mapped indexes.
NEW CHAPTER! Chapter 6, “Constraints,” will be particularly helpful to database designers. This chapter deals with referential integrity and column constraints and includes a How-To summarizing a new Oracle8 construct: deferred constraints.
Chapter 7, “Views,” explains creation, management, and use of database views, including updatable join views and object views.
Chapter 8, “Security,” defines system and object privileges and how to grant them. The important topic of reporting on the current security configuration of the database has special prominence here. This chapter also explains the new password management features of Oracle8.
NEW CHAPTER! Chapter 9, “Space Management,” is a compilation of the basic information every DBA needs at hand to manage the physical side of an Oracle8 database. Performance tuning, covered in Chapter 15, might be more intriguing, but for those who want to make sure their database is available when it should be, this is where to look.
Chapter 10, “PL/SQL,” covers the important procedural extensions of the Oracle8 server. PL/SQL blocks creation, error handling, and stored procedure management are only some of the topics covered in this series of How-To’s.
NEW CHAPTER! Chapter 11, “Querying Data,” is devoted entirely to the important topic of getting data into and out of Oracle8 databases using PL/SQL.
Chapter 12, “Built-In Packages,” explains how to use some of the useful PL/SQL procedures that ship with Oracle8. Dynamic SQL and the DBMS_LOB package, new in Oracle8, are two of the topics discussed here.
Chapter 13, “Triggers,” builds on the previous two chapters by explaining use of PL/SQL to encapsulate business rule information within database objects.
Chapter 14, “SQL Statement Tuning,” shows how and where to start improving the performance of your database. The EXPLAIN PLAN, AUTOTRACE, AND SQL*Trace methods of SQL statement analysis all appear in this chapter’s How-To’s. Tuning strategies covered here include optimizer hints and star query optimization.
Chapter 15, “Database Tuning,” provides a survey of must-know techniques for any IT professional, particularly DBAs, who want to get the most performance out of an Oracle investment. Application of the techniques in this chapter could improve the performance of all of your Oracle applications.
NEW CHAPTER! Chapter 16, “Create an Object Relational Database,” is an in-depth introduction to the object-oriented extensions of Oracle8 including object types, object tables, and nested tables. Oracle professionals need not abandon familiar relational constructs to make use of Oracle8 object extensions; this chapter shows you how.
Chapter 17, “Oracle Web Application Server,” profiles creation and management of Internet and intranet applications within the Oracle WebServer development environment.
Who Will This Book Help?
The information in this book will benefit anyone who works with, or is planning to work with, Oracle8, particularly database administrators and application developers. Much of the material applies to earlier versions of Oracle as well. The authors assume familiarity with basic principles of database technology and program-ming, but the complexity of covered topics ranges from the very straightforward to the very complex. The format lends itself to direct answers to specific questions, but much of the material will contribute to a general knowledge applicable to questions not explicitly included in this book. Regardless of topic complexity, the emphasis is on a practical, problem-solving approach supplemented wherever possible with specific examples of the techniques and the programming code that will get the job done.
Using the Sample Scripts All of the sample scripts used within the book are available on the World Wide Web at http://www.mcp.com/info. To download the scripts, put the ISBN number in the Search field and click the Search button. Windows 95/NT users should download the Windows executable to a temporary directory. Once you’ve downloaded the file to your hard drive, double-click it to run the installer. UNIX users should download either the SOURCE.TAR.Z or SOURCE.TAR.GZ file. Once either of the files is downloaded, uncompress or unzip the file and then untar it. When working with a particular chapter, readers may wish to copy all of that chapter’s scripts into the SQL*Plus working directory. The default location of this directory under Windows NT is \ORANT\BIN. Alternatively, run the scripts by providing the script’s full path name to the SQL*Plus START command. The INSTALL.SQL script from Chapter 1 creates the WAITE user account used throughout the book as the book’s working account. Any account with the DBA role, however, will suffice.