ORACLE8i- P1

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

59
lượt xem 4
download

ORACLE8i- P1

Mô tả tài liệu

Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P1: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: ORACLE8i- P1

Please purchase PDF Split-Merge on www.very
Using Your Sybex Electronic Book To realize the full potential of this Sybex electronic book, you must have Adobe Acrobat Reader with Search installed on your computer. To find out if you have the correct version of Acrobat Reader, click on the Edit menu—Search should be an option within this menu file. If Search is not an option in the Edit menu, please exit this application and install Adobe Acrobat Reader with Search from this CD (double- click on rp500enu.exe in the Adobe folder). Navigation Navigate throught the book by clicking on the headings that appear in the left panel; the corresponding page from the book displays in the right panel. Search To search, click the Search Query button on the toolbar or choose Edit >Search > Query to open the Search window. In the Adobe Acrobat Search dialog’s text field, type the text you want to find and click Search. Use the Search Next button (Control+U) and Search Previous button (Control+Y) to go to other matches in the book. The Search command also has powerful tools for limiting and expanding the definition of the term you are searching for. Refer to Acrobat's online Help (Help > Plug-In Help > Using Acrobat Search) for more information. Click here to begin using Please purchase PDF Split-Merge onyour SybexElect ronic Book! watermark. www.verypdf.com to remove this www.sybex.com
MASTERING ™ ORACLE8i ™ Robert G. Freeman Mark D. Blomberg San Francisco • London Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Associate Publisher: Richard Mills TRADEMARKS: SYBEX has attempted throughout this book to distinguish proprietary trademarks from Acquisitions and Developmental Editor: Christine descriptive terms by following the capitalization style McGeever used by the manufacturer. Editors: Carol Henry, Marilyn Smith The author and publisher have made their best efforts Production Editor: Leslie E. H. Light to prepare this book, and the content is based upon Technical Editor: Ashok Hanumanth final release software whenever possible. Portions of the manuscript may be based upon pre-release versions Book Designer: Patrick Dintino, Catalin Dulfu, supplied by software manufacturer(s). The author and Franz Baumhackl the publisher make no representation or warranties of Graphic Illustrator: Tony Jonick any kind with regard to the completeness or accuracy of the contents herein and accept no liability of any Electronic Publishing Specialist: Adrian Woolhouse kind including but not limited to performance, mer- Proofreaders: Nanette Duffy, Emily Hsuan, chantability, fitness for any particular purpose, or any David Nash, Laurie O’Connell, Yariv Rabinovich, losses or damages of any kind caused or alleged to be Nancy Riddiough caused directly or indirectly from this book. Indexer: Nancy Guenther Manufactured in the United States of America CD Coordinator: Christine Detlefs 10 9 8 7 6 5 4 3 2 1 CD Technician: Kevin Ly Cover Designer: Design Site Cover Illustrator: Sergie Loobkoff, Design Site Copyright © 2002 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of this publication may be stored in a retrieval system, transmitted, or reproduced in any way, includ- ing but not limited to photocopy, photograph, mag- netic, or other record, without the prior agreement and written permission of the publisher. Library of Congress Card Number: 2001091742 ISBN: 0-7821-2929-3 SYBEX and the SYBEX logo are either registered trade- marks or trademarks of SYBEX Inc. in the United States and/or other countries. Mastering is a trademark of SYBEX Inc. Screen reproductions produced with SnagIt. SnagIt is a registered trademark of TechSmith Corporation. Screen reproductions produced with FullShot 99. FullShot 99 © 1991-1999 Inbit Incorporated. All rights reserved. FullShot is a trademark of Inbit Incorporated. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Software License Agreement: Terms and The Software is not available from SYBEX in any other Conditions form or media than that enclosed herein or posted to www.sybex.com. If you discover a defect in the media The media and/or any online materials accompanying during this warranty period, you may obtain a replace- this book that are available now or in the future con- ment of identical format at no charge by sending the tain programs and/or text files (the “Software”) to be defective media, postage prepaid, with proof of pur- used in connection with the book. SYBEX hereby chase to: grants to you a license to use the Software, subject to the terms that follow. Your purchase, acceptance, or SYBEX Inc. use of the Software will constitute your acceptance of Product Support Department such terms. 1151 Marina Village Parkway Alameda, CA 94501 The Software compilation is the property of SYBEX Web: http://www.sybex.com unless otherwise indicated and is protected by copy- right to SYBEX or other copyright owner(s) as indi- After the 90-day period, you can obtain replacement cated in the media files (the “Owner(s)”). You are media of identical format by sending us the defective hereby granted a single-user license to use the Software disk, proof of purchase, and a check or money order for your personal, noncommercial use only. You may for $10, payable to SYBEX. not reproduce, sell, distribute, publish, circulate, or Disclaimer commercially exploit the Software, or any portion thereof, without the written consent of SYBEX and the SYBEX makes no warranty or representation, either specific copyright owner(s) of any component soft- expressed or implied, with respect to the Software or ware included on this media. its contents, quality, performance, merchantability, or fitness for a particular purpose. In no event will In the event that the Software or components include SYBEX, its distributors, or dealers be liable to you or specific license requirements or end-user agreements, any other party for direct, indirect, special, incidental, statements of condition, disclaimers, limitations or consequential, or other damages arising out of the use warranties (“End-User License”), those End-User of or inability to use the Software or its contents even Licenses supersede the terms and conditions herein as if advised of the possibility of such damage. In the to that particular Software component. Your purchase, event that the Software includes an online update fea- acceptance, or use of the Software will constitute your ture, SYBEX further disclaims any obligation to pro- acceptance of such End-User Licenses. vide this feature for any specific duration other than By purchase, use or acceptance of the Software you fur- the initial posting. ther agree to comply with all export laws and regula- The exclusion of implied warranties is not permitted tions of the United States as such laws and regulations by some states. Therefore, the above exclusion may may exist from time to time. not apply to you. This warranty provides you with spe- Software Support cific legal rights; there may be other rights that you may have that vary from state to state. The pricing of Components of the supplemental Software and any the book with the Software by SYBEX reflects the allo- offers associated with them may be supported by the cation of risk and limitations on liability contained in specific Owner(s) of that material, but they are not this agreement of Terms and Conditions. supported by SYBEX. Information regarding any avail- able support may be obtained from the Owner(s) using Shareware Distribution the information provided in the appropriate read.me This Software may contain various programs that are files or listed elsewhere on the media. distributed as shareware. Copyright laws apply to both Should the manufacturer(s) or other Owner(s) cease to shareware and ordinary commercial software, and the offer support or decline to honor any offer, SYBEX copyright Owner(s) retains all rights. If you try a share- bears no responsibility. This notice concerning support ware program and continue using it, you are expected for the Software is provided for your information only. to register it. Individual programs differ on details of SYBEX is not the agent or principal of the Owner(s), trial periods, registration, and payment. Please observe and SYBEX is in no way responsible for providing any the requirements stated in appropriate files. support for the Software, nor is it liable or responsible Copy Protection for any support provided, or not provided, by the Owner(s). The Software in whole or in part may or may not be copy-protected or encrypted. However, in all cases, Warranty reselling or redistributing these files without autho- SYBEX warrants the enclosed media to be free of physi- rization is expressly forbidden except as specifically cal defects for a period of ninety (90) days after purchase. provided for by the Owner(s) therein. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
While this book was being written, the cowardly attacks on the World Trade Center, the Pentagon, and in Pennsylvania took place. Thus, this book is dedicated to all those who died there and in other terrorist tragedies throughout the world. This is dedicated to all the children who lost parents, the wives who lost husbands, and the husbands who lost wives. It is dedicated to the grieving families and friends and to the hope of peace, but also to the hope of justice for those who feel that terror and destruction are reasonable alternatives to peace and dialogue. As always, this work is dedicated to my family—my father and my mother, my brother and sisters, and most of all, my wife and children, who sacrifice their time with me in the writing endeavors that I take on. —Robert G. Freeman Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
ACKNOWLEDGMENTS T here are numerous individuals who deserve recognition, and if we could put them all on the front cover, we would. Thanks to Richard Basile and Shagun Tyagi, whose names do not appear on the front cover but who contributed to the original content of this book. Thanks to my co-author Mark Blomberg, who really worked hard to make this happen. Thanks, of course, to those who helped make this book what it is. To my friend Jeff Kellum, who put me in contact with everyone else—including Richard Mills, our sup- portive associate publisher. To the wonderful editing staff of this book, Leslie Light, Marilyn Smith, and Carol Henry. Many thanks, as well, to technical editor Ashok Hanumanth. In addition, thanks to Christine McGeever, acquisitions and develop- ment editor, who helped get the book off the ground. The CD team and Dan Mum- mert did a fine job of assembling the CD material. To the production staff, including talented compositor Adrian Woolhouse, thanks for making the book look great. Of course, there are countless people who helped and didn’t even know it. Special thanks to Steve Adams, Tim Stippler, Charles Pack, Mike Ault, Pete Sharman, John B., KG, and numerous other awesome people in the Oracle community! Special thanks to the folks I work with: Nancy Von Dolteren, Yang Jiang, Don Mongeon, Bob Just, Bill Barker, Wendy Hausler, John King, Bill Sullivan, Gunjan Nath, Richard McClain, Nirupam Majumdar, and Maritza Gonzalez. You are all great! Thanks to those who provided moral support (and allowed me to not hold boards for board breaking while I was writing this!): Mrs. Skutnik, Mr. Alfaro, and all those at Master Clark’s Karate America in Jacksonville. Thanks to our many friends, who always support us. Finally, thanks to my wife and my five kids, who came into my work area about once a week just to refresh their memories of what I look like. Robert Freeman Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
I would like to take this opportunity to thank the staff at Sybex for all their hard work through the long months of this project. The staff at Sybex were helpful, resourceful, and most of all, knowledgeable. I would also like to thank my wife and family for being tolerant of my absence and not making the appropriate time to spend with them. So thank you to my wife Ann, and daughters Rebecca and Dana. Finally, I would like to thank Robert Freeman for bringing me into this venture and giving me the opportunity to realize one of my personal goals. To all involved, a truly grateful THANK YOU. Mark D. Blomberg Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
INTRODUCTION W “ e want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, infor- mation is king, and the speedy, accurate, and reliable retrieval of this information is paramount. And if you wish to store and retrieve information quickly, Oracle’s flagship data- base product is the way to go. Oracle Corporation currently “owns” the major share of the large database market, which is one sign of the superiority of Oracle’s product. The Oracle product is robust in features and very fast—but it can also be complicated and expensive to run. As much as Oracle wishes to market its database product as being easy to manage and install, the truth is that an experienced DBA is generally needed for anything other than the most elemental installation and operation. We hear often from aspiring or beginning DBAs wondering how they can join the ranks of Oracle DBAs. Experience is undeniably one of the primary requirements for DBA excellence. Yet, the need to acquire that experience also stands in the way of the junior DBA’s advancement. This experience requirement has made senior DBAs some- what scarce, and it’s also a principal reason for the good salaries offered to truly good DBAs. In other words, you can’t just pick up a book, go through the motions, and become a great DBA. The bottom line for employers is that it’s best to start out with the best and brightest. (“You can pay me now, or you can pay me later.”) If you’re investing millions in equip- ment and software, then you want to invest in talent, as well, at the beginning. Once you have a stable, well-designed system up and running, then you can bring on the juniors to maintain it. Getting one’s foot in the DBA door is difficult. What you can do, however, is learn from each book you study. Realize that just knowing the commands and how the processes work is not enough to become a good DBA. In addition, you need to know about backup and recovery. You need to know about tuning the database and tuning the SQL running in the database. You need to know how to ferret statistics out of the database and how to interpret them. That’s what mastering Oracle database administration, and this book, are about. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
xxviii INTRODUCTION This text goes beyond basic administration tasks, though it covers those as well. Within these pages you will find nuggets of our experience that might help you. We hope that you will also take away from this book the fact that administration of an Oracle database is a multifaceted job. Finally, remember that the best approach to management of Oracle is a proactive one. Don’t sit and wait for problems to occur. Is This Book for You? We assume that the reader has fundamental knowledge of an Oracle database. If you are a beginning DBA with little or no understanding of Oracle, you should carefully read Chapter 1 before going further. In addition, we strongly suggest that you read a selec- tion of the following books; these are in order from basic to advanced skills coverage: • Oracle DBA 101 by Marlene L. Theriault, et al • The Sybex Oracle Certified Professional (OCP) series of certification study guides, including • OCP: Oracle8i DBA SQL and PL/SQL Study Guide, 0-7821-2682-0 (Sybex, 2000) • OCP: Oracle8i DBA Architecture & Administration and Backup & Recovery Study Guide, 0-7821-2683-9 (Sybex, 2001) • OCP: Oracle8i DBA Performance Tuning and Network Administration Study Guide, 0-7821-2684-7 (Sybex, 2000) Though this set of books is designed primarily for those working toward the Oracle OCP exams, the study guides really are a good introduction to the Oracle product. • Oracle8i Networking 101 by Marlene L. Theriault • Oracle8i Administration and Management by Michael R. Ault If you are a beginning DBA and do not have direct access to the Oracle product, you can download a copy of Oracle from Technet (www.technet.com), the Oracle web- site that contains demonstration copies of Oracle software. Technet also contains code samples and Oracle documentation. Following are some other beneficial websites that you might use in your quest to master the Oracle database product: www.revealnet.com This website provides information on the product offerings of RevealNet Labs (Quest Software), which develops state-of-the-art tech- nical knowledge bases and development/administration tools for Oracle, DB2, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
INTRODUCTION xxix and SQL Server. The site also includes the Oracle DBA Pipelines, a forum for Oracle database administrators to meet and share ideas and ask for help. The syn- tax diagrams throughout this book and in Appendix E (on the CD) were produced using the RevealNet Knowledge Base for Oracle Administration. www.oracle.com This is the main page of Oracle Corporation’s website. Here you can find information on Oracle and download certain products. education.oracle.com This is Oracle Corporation’s education site, which offers information on Oracle training courses and becoming an Oracle Certified Professional. metalink.oracle.com Metalink is the online website for Oracle users. If you are an Oracle support customer, you will have access to this site. Use it to search out help with existing Oracle bugs, to find Oracle documents, and to access Oracle forums on various subjects. www.ixora.com.au The Ixora site provides a great wealth of Unix-related Oracle internal knowledge, as well as scripts that you can use to monitor the health of your database. What You Need to Know As you pick up this book, understand that we have not devoted a great number of its pages to examining every fundamental detail of the Oracle database environment. You should already be comfortable with putting together SQL statements, and you should know what SQL*Plus is. Although the book as a whole is not designed for beginners, beginning DBAs will find Chapter 1 to be a quick primer that may well be enough to help them on the way to mastering Oracle8i database administration. If you are a junior DBA, this book is right up your alley. You’ll be comfortable already with what’s involved in starting and stopping the database and working with simple queries. You’ll have some understanding of what the data dictionary is, and perhaps even be familiar with parts of it. It’s our hope that you’ll eat this book up and that it will give you the knowledge you need to become a truly great DBA. If you’re already a master DBA, we hope this text will be a trusted reference, and perhaps provide insight into some aspects of database administration that you want to improve. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
xxx INTRODUCTION Conventions Used in This Book One of the themes you’ll find in this book is consistency. Within these pages we follow a set of terminology standards that are in fairly common use in the Oracle community. We suggest that you adopt these standards in your daily operations, as well. Within the text, Oracle keywords are in uppercase (SELECT, INSERT, FROM, V$PARAMETER, PARTITION BY HASH). Table and column names are in uppercase, as well, to distinguish them from the surrounding text (the EMP table; the EMP NO column). In addition, the following elements appear in this book: N OTE Notes like this will appear from time to time. Generally when we want add a comment that pertains to a particular section, we will do so in a note. TI P Tips like this are used to highlight particularly important procedures or processes. WARN I NG Warnings are used in the text to keep you from destroying something inadvertently. When a warning appears, make sure you read it carefully. We also use warn- ings to point out bugs in the Oracle8i product that we know about. How to Use This Book You may find it easiest to read the chapters in order, but it’s not essential. One of our goals was to make it possible for you to pick the text up and read any individual chap- ter without having to study several other chapters first. We have grouped similar chapters logically, but otherwise (with a few exceptions) the chapters do not particu- larly “build” upon one another. To make it easier for you to move among the chap- ters, we have included plenty of cross-references throughout the book. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
INTRODUCTION xxxi Of course, you’ll get the most benefit from this book by putting the material to real use. Take the examples and the generic routines we’ve provided and expand on them. Add and subtract from them and incorporate them into your own database manage- ment activities. Experiment and enjoy! What’s On the CD The CD is chock full of goodies! First, you’ll find a complete electronic edition of the book. All nine appendices, which are not in the printed book, are on the CD. You’ll find the Quick Reference appendices especially helpful. They’re all in easy-to-search .PDF format. We’ve also provided some trial versions of products that can help you better man- age your database. Note that these demos require that you purchase a license for con- tinued use; please read the provided supporting documentation and respect the rights of the vendors who were kind enough to provide these files. All of the following are from RevealNet Labs (Quest Software), www.revealnet.com: • The Formatter Plus tool, which will do a quick syntax check of your SQL. It works on code that has no Oracle syntax errors and is compilable or executable on Oracle, but it is more permissive than the PL/SQL compiler or SQL*Plus environment. • The RevealNet Knowledge Base for Oracle Administration, to help you find solu- tions for your questions about Oracle database administration. • The Active PL/SQL Knowledge Base combines a comprehensive PL/SQL refer- ence with an extensive PL/SQL Code Library. It offers a valuable source of tech- nical expertise and a substantial library of procedures and functions. Also included on the CD is the Oracle8i table, index, and SGA sizing spreadsheet discussed in Chapter 3 of the book, compliments of Mike Ault and TUSC. NOTE For late-breaking information about the CD, including additional files and utili- ties, see README.TXT in the root directory of the CD. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
xxxii INTRODUCTION Come Visit Us We hope that you will share with us your feelings about this book. Let us know what has helped you, and what you might like to see in the next incarnation of this work. You might well disagree with some things stated here—being a DBA is somewhat like being an artist, and not everyone agrees on what makes a work of art. We hope that you will communicate with us and let us know what you liked and what you didn’t. We have set up a website, www.masteringoracle.com, where you’ll find a link to e-mail Robert. There are also some links to the book’s errata and other nifty things! Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
PA R T I Oracle Essentials LEARN TO: • Install Oracle • Migrate to Oracle • Create an Oracle database • Use the Oracle data dictionary Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
CHAPTER 1 Elements of Oracle Database Management F E AT U R I N G : What is a DBA? 4 Introducing Oracle8i 5 Oracle internals 13 The physilogical Oracle 17 Fundamental Oracle principles 19 Environment settings 21 The Oracle SQL interface tools 22 Using Oracle SQL 29 Oracle PL/SQL 39 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
W elcome to Mastering Oracle8i. We hope you find this book useful in your DBA endeavors. We’ve made every effort to fit as much priceless information into this book as we could find, and hope you refer to it daily. This book was written as a reference for the working Oracle DBA. We assume most of you already know Oracle well and want to explore Oracle8i in particular. And if you’re new to Oracle, start right here. The pace might seem fast and furious, but you’ll manage quite well. Here in Chapter 1, we offer a broad view of Oracle and SQL fundamentals. We’ll begin with a refresher on the DBA’s role. Then we’ll review some database theory and Oracle history. Following that is a discussion of Oracle internals, the building blocks you must master for successful database administration. Next up is a discussion of SQL*Plus and other elements of the Oracle database interface. In addition, we will briefly discuss the tools used to access the database, fundamental concepts of the SQL language, and how to use SQL in the database. All of these introductory subjects are, of course, covered in depth in later chapters. What Is a DBA? There seems to be an air of mystery surrounding database administrators and data- base management. But really, being a DBA boils down to just a few basic qualities: • Willingness to work long, sometimes hard hours • Ability to ferret out the answers • Determination to be the best • The drive to succeed • Knowledge of database administration • Experience in database administration New DBAs can make up for a lack of the last two qualities by concentrating on the first four. As you gain knowledge and experience, you’ll find that you don’t need to work so many long, hard hours quite as often as when you first started. You’ll make fewer mistakes, and you’ll know where to look first for the answers—no ferrets required! We aren’t trying to snowball you, though. There will always be users who call you at midnight, needing your help to recover a database. But you’ll be up to the task. With knowledge and experience comes a better DBA who can truly become a “lazy DBA.” (In Chapter 15, which covers performance monitoring, you’ll find a definition of a “lazy DBA”—and it’s not someone who just sits around.) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
INTRODUCING ORACLE8i 5 PA R T I Robert’s Rants: Stick to Your Guns As DBA, you’re very much the heart of the database. If you fail, your database fails. Do Oracle Essentials not be complacent with your database just because it’s performing well. Do not ease up on vigilance against risks to your systems, including policies and procedures that are not in the best interest of your database. Granted, there is a time to fight and a time to lay low, but this is your job. The life of your database is your job. Yes, I’m on a soapbox; this is my chance to rant. I’ve met many a DBA who rolled over and gave up because a project manager ordered them to do this or do that. But don’t forget that you are the DBA. Go on out and look at the top job-hunting web sites, and count the number of DBA jobs out there. If you’re a DBA, you’re in high demand, so you have the freedom to say no and stand by it. I’ve had more than a few encounters with a manager trying to bully me into doing something I just wasn’t going to do. I’ve simply told them to go fly a kite. I might even have mentioned something about my black belt in karate, if they were particularly unpleasant. Of course, please do make sure you can back up your point of view with some good solid facts. In the end, when you’re proven right, you and your database will be better for it, and so will the community using that database. The point is, if you know you’re correct, don’t back down for a second. Compromise is fine, if it doesn’t compromise the integrity of the database in your charge (or your own integrity). Someone may demand your head, but it’s a sure bet that most of your company’s management knows the value of your position—and if they don’t, they will soon find out. Of course, if you end up getting fired, I didn’t say this and I’m not here. Introducing Oracle8i In this section we will briefly discuss some relational database theory and some Oracle history. We will take an overview of Oracle8i, the latest and greatest version of Oracle, and see how the Oracle database is both relational and object oriented. TI P Already an expert on relational theory? Already know that ACID means something else besides what you used to melt things in chemistry class? You can skip this section altogether; go ahead and move on to “Internals.” Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
6 CHAPTER 1 • ELEMENTS OF ORACLE DATABASE MANAGEMENT Oracle is a relational database geared to meet the information processing require- ments of business. Its features include support for all of the following: • A large number of users and high-concurrency activity. • Read consistency among transactions, ensuring that other users will not see your changes until your changes are committed. Neither will your changes affect other users’ queries that started before your changes were committed. • A high degree of scalability. • Multiple recovery methods, including recovery to point of failure and recovery to a specific point in time. • High availability using several methodologies, including standby database and replication methodologies. Relational Theory—Briefly In a paper presented in June 1970, “A Relational Model of Data for Large Shared Data Banks,” Dr. E. F. Codd introduced the relational database model we still use today. Of course, Codd and others have added to this theory. In particular, Dr. Peter Chen, pro- fessor of computer science at Louisiana State University, originated the Entity Rela- tionship (ER) model. He described it in a 1976 paper entitled “The Entity Relationship Model - Toward a Unified View of Data.” Within the relational database, data is generally stored in two-dimensional tables. Contained within the tables are sets of rows and columns. A row represents a record in the table, and one or more columns represent the details of that record.When dis- cussing relational theory, the acronym ACID is used to represent the four fundamen- tals of effective relational databases. Oracle8i provides robust support for all these relational fundamentals. A Atomicity. Each transaction is a separate and distinct entity. It happens in whole or not at all. C Consistency. Each database change is partitioned from other transactions. Results from another transaction won’t mysteriously pop up in the middle of another transaction. One transaction moves the database from one consistent state to the next. I Isolation. A transaction is not available to other users until it has been commit- ted. If you add a department and then add an employee to that department in the same transaction, someone else cannot see the new department added until you have committed the transaction, which takes place after you have added the employee record. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
INTRODUCING ORACLE8i 7 D Durability. This fundamental implies that when a transaction is complete, its PA R T presence in the database will be preserved and will not be lost. Changes, once I committed, can be recovered. A relational database also demonstrates the principles of structures, operations, and integrity that Codd defined. Structures are tables, indexes, and views (for example). Oracle Essentials They are manipulated with operations. Operations are defined methods of allowing the user access to the database and of manipulating that database. Database opera- tions adhere to integrity rules that govern the database. Integrity rules define what operations can be executed and when; all operations must follow these rules. Normalization When you’re creating a database for an online transaction processing system (OLTP), there are generally two concerns: the speed and efficiency of transactions, and the overall storage space required by the database. One means of achieving the best results is normalization—the process of organizing and refining database tables to pro- vide accurate, unambiguous results when the tables are accessed. For larger, data ware- housing systems, the goals are generally the same, but the means to meet these goals tend to differ. As a database is normalized, overall storage requirements are often reduced because there is less data redundancy. Normalization can also, however, have a negative impact on performance (a statement that causes an argument in many camps). That said, the benefits of normalization usually outweigh any potential loss of perfor- mance. In fact, normalization can improve performance because, overall, less data is processed. Typically, we start with a normalized database. Often, however, the cost of per- forming the joins to retrieve data is higher than the additional disk space required to store the data in a denormalized state. Based on this and other performance factors, we may choose to denormalize the database—tables and materialized views are cre- ated to store data from various tables in denormalized format. Although denormaliza- tion should not be the first line of response to query performance problems, it can yield good results. In one case, we denormalized eight tables used in a recurring eight- table join and got runtimes down from several hours to just seconds. Indeed, just about any large data warehousing design comprises denormalized data stores. Databases are typically described as being in third normal form. (You can actually normalize your database up to sixth normal form, but this is rarely done.) Here are the steps of normalizing an object in a database: First Normal Remove all repeating groups. Second Normal Link all entities to a primary key. Third Normal Link nonkey entities by keys, supported with foreign keys. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

CÓ THỂ BẠN MUỐN DOWNLOAD