Oracle 9i: Fundamentals I (Exam Cram 2)

Oracle 9i Fundamentals I Oracle 9i Fundamentals I Introduction\ Welcome to Oracle 9i: Fundamentals 1 Exam Cram 2. You are embarking on an adventure that has been taken by thousands before you. When you find yourself getting discouraged, take heart. If they can do pass the exam, so can you. You do have to understand that preparing for a certification exam is not an undertaking that you can attempt halfheartedly. Beware that preparation isn't easy, and it takes time and requires an investment in both money and effort.\ If you are willing to take the process seriously and do what it takes to obtain the knowledge and experiences necessary, you can take and easily pass the certification tests. This book is designed to make it as easy as possible for you to prepare. But you have to take it the rest of the way.\ Many software products, most operating systems, and nearly all commercially available databases now have a means by which those working with the product can improve their efficiency with the product and their ability to perform their jobs (for their prospective employers, for their current employers, or even just for themselves). Oracle is no exception. Oracle has not just one certification track for database administrators, it has three: Oracle Certified Associate DBA, Oracle Certified Professional DBA, and Oracle Certified Master DBA. Table I.1 gives you the examination and class requirements for each level of the 9i DBA Certification Track. As you can see in Table I.1, this test is a part of each of the tracks.\ Table I.1 Oracle 9i DBA Certification Track\ \ \ \ \ \ \ \ \ \ \ \ Exam Number\ \ \ Exam Title\ \ \ Required Correct Answers\ \ \ Scored Questions\ \ \ Passing %\ \ \ Test Duration\ \ \ \ \ OCA\ \ \ \ \ You need to pass either 1Z0-001 or 1Z0-007 as well as 1Z0-131.\ \ \ \ \ 1Z0-001\ \ \ Introduction to Oracle: SQL and PL/SQL\ \ \ 39\ \ \ 57\ \ \ 68%\ \ \ 2 hours\ \ \ \ \ 1Z0-007\ \ \ Introduction to Oracle9i: SQL\ \ \ 40\ \ \ 57\ \ \ 70%\ \ \ 2 hours\ \ \ \ \ 1Z0-031 Fundamentals I\ \ \ Oracle9i Database:\ \ \ 44\ \ \ 60\ \ \ 73%\ \ \ 1.5 hours\ \ \ \ \ OCP = OCA + These two tests\ \ \ \ \ 1Z0-032\ \ \ Oracle9i Database: Fundamentals II\ \ \ 49\ \ \ 63\ \ \ 77%\ \ \ 1.5 hours\ \ \ \ \ 1Z0-033\ \ \ Oracle9i Database: Performance Tuning\ \ \ 38\ \ \ 59\ \ \ 64%\ \ \ 1.5 hours\ \ \ \ \ 8i to 9i OCP Upgrade\ \ \ \ \ 1Z0-030\ \ \ Oracle9i Database: New Features for Administrators\ \ \ 37\ \ \ 53\ \ \ 70%\ \ \ 1.5 hours\ \ \ \ \ Note - The OCM certification requires the OCP certification plus two advanced DBA courses from Oracle University and a Practicum.\ People may argue that passing tests isn't an adequate test of a database administrator's ability to do his or her job, but it is at least a measure of how well a person can recall critical and pertinent information in a stressful and less than optimum situation. No one can argue that taking a test is less than optimal. No one likes to take tests. At best, they are stressful—knowing that you could fail, knowing that you are being called on to remember things even when you know you are thoroughly prepared.\ It doesn't matter whether it is your first test or your one hundred and first test, understanding the exam, the exam setting, and the testing software that you will be presented with will help you to better concentrate on the material that you are being tested on rather than the environment in which you find yourself.\ What's more, knowing that you have mastered a few of the basic test-taking skills will help you recognize, if not fully anticipate, some of the tricks that inevitably find their way into some of the Oracle Certification test questions.\ How to Prepare for an Exam\ Knowing how to prepare for an exam such as this is half the battle.\ One of the best ways to prepare, and I can't stress this enough, is to get a copy of the database and play with it. You can download the database, although it is in several rather large files, which take considerable time even over a dedicated high-speed connection, or you can buy trial soft- ware from Oracle Store (http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?a=b) and for less than $100 get full versions delivered to your door.\ Install it, break it, fix it, and learn. It is useful to not just memorize information to answer the questions because often the questions are asked in such a way (or the answers are posed in such a way) as to make it exceptionally tricky to answer without having hands-on experience. And the experience of using the commands and actually working with the database and its associated commands and tools will be beneficial long after you have cleared all the exams.\ Documentation\ Get the Oracle documentation. These manuals, although not always an easy or entertaining read, are often the best resource to use when preparing for not only your tests but also for your life as a database administrator.\ http://tahiti.oracle.com and http://otn.oracle.com/documentation/index. html are prime sources for the manuals for any given release of the software. The manuals are available in either PDF format, or as a web page in HTML format.\ Tahiti maintains only database software, application server documentation, and Collaboration suite documentation on its site, whereas all the Oracle products' documentation, back several releases (including the database back through Oracle 7.3), is available through Oracle Technology Network (OTN). OTN requires you to sign up for a free membership to access its information (and many of the manuals through Tahiti are linked from the OTN site, so it is a worthwhile investment of 10 minutes), but that free membership also entitles you to a 20% discount on testing fees for the certification tests and access to a wealth of informative articles and access to downloadable software.\ Metalink\ Another resource at your disposal, if you are fortunate enough to be in an organization that has an Oracle support contract, is Metalink (http:// metalink.oracle.com). On Metalink you can access technical documents and information on different aspects of database administration (architecture, administration, security, storage, and corruption just to name a few). It is an invaluable resource when you are learning about the Oracle database and is even more useful when you are troubleshooting issues when you are working as a DBA.\ The manuals are also available on CD when you buy the software, even a trial set of the software, from Oracle Store.\ Exam Prep Materials\ There are many commercially available certification preparation materials. Many offer study guides (sometimes free, usually fee based) and test banks of questions similar to those found on the test, and often scaled-down versions of their test banks free of charge for download or online access. A simple online search will yield several dozen such organizations, including the following that I have used:\ \ http://www.cramsession.com/\ http://www.examnotes.net\ http://www.whizlabs.com/ocp/oracle-dba.html\ http://www.selftestsoftware.com/dept.asp?dept%5Fid=6000\ This is the software recommended on the Oracle Certification web site.\ \ http://www.oracle.com/education/certification/index.html?content.html\ This offers a free demo version as well as the purchasable version.\ \ \ Be careful what company you choose. Make sure that you are going to get what you have ordered and paid for. There are companies that charge you for software, offering lofty promises, and never deliver. As with anything, make sure that you are buying from a reputable company.\ The Exam Cram 2 Book\ By investing in this book, you have accessed another means by which you can quickly and efficiently work toward passing your certification. The Exam Cram and the Exam Cram 2 series have been a great help to countless certification candidates, not only in certification preparation but also as a concise reference to maintain in your library. This is a book that you will reach for to answer nagging questions that you know but can't quickly call to memory.\ Classroom Training\ If you have the money to invest, I highly recommend taking Oracle University offered courses, not simply for the information necessary for passing the exams but also for the experience. Although not everyone has $2000 to $3000 to invest in a single weeklong class, there is a lot to be learned in the classes. You learn not only from the instructors but also from others in the class and from the experience of really being able to break someone else's database in a controlled setting. You often don't know what you don't know until you are faced with either a question that has been asked by someone else in a class, or with a new situation with the database that you had never considered before.\ Exam Format\ The exams are all laid out similarly to each other. They have a set number of scored questions and a set time limit in which to answer them. After you finish the exam, you are (unless you are for some reason taking a beta exam where the score isn't returned for as long as three to four months) provided with your score at the end of the exam and told at that point whether you have passed or failed.\ Beta exams are exams released several months before the "official" exams are ready. These exams have questions similar to the ones that will be on the official exam when it is released; however, there are more questions, and the time allotted for taking the exam is extended. For example, as of June 2004, the Oracle 10g upgrade beta exam has been announced. You have three hours to complete the exam, and you may have to wait up to 10 weeks for your score. Only the questions that make it into the official exam are scored, but you have no way of knowing what those question are. For the inconvenience, you only have to pay a fraction of the normal fee for taking the exam.\ If you pass, you are given a passing certificate (your official certificate will be mailed to you later, along with a plastic card the size of a credit card for your wallet) that the testing center embosses with its stamp. If you fail, do not throw away the print out. It gives you directions on when you can retake the test (typically after 30 days) and tells you where you need to apply more study effort and which types of questions you missed, if not the actual questions that you missed. Use this paper as a study aid to direct your future study sessions.\ Layout and Design of the Tests\ The tests are primarily multiple choice, although I have taken a few tests that had a couple of completion questions on them. There are two types of questions using the multiple-choice layout: One has a single answer, and one has multiple answers.\ The single-answer question has a radio button as the means to select the answer (see Table I.2). It lets you select exactly one answer, and if you select a second, it automatically unselects your original choice.\ Multiple-answer questions, themselves broken down into two distinct categories, are answered by means of a check box. The first kind of multiple-answer question tells you, explicitly, how many different answers you are expected to have. You will have no fewer and no more than the specified number of answers. The question will have that number in the question (for example, choose two). If you attempt to provide more than the specified number of answers, it tells you that you can only give the specific number of answers requested, and if you want to answer something differently, you must unselect one that you have already selected. The other kind simply tells you to select all the answers that apply. This is my least favorite kind of question because you have to know when you are finished. I often find that I second guess myself and go back to make sure that I have gotten all the ones that I really want—and that I really want all the ones that I have selected.\ Table I.2 Question Selection Options\ \ \ \ \ \ \ \ Example\ \ \ Description\ \ \ \ \ m\ \ \ Radio button\ \ \ \ \ q\ \ \ Check box\ \ \ \ Finally, some of the questions require you to examine an exhibit to answer the given question. These exhibits may be a picture, an extended piece of code, or a multiline command that requires you to look carefully and to go back and double-check what they are asking. Occasionally, the exhibit spans more than one question. You can resize your windows on the monitor screen in such a way as to have both the question and the answers (scrollbars included) on the screen at the same time, or you can switch between the different full-sized windows to answer the relevant questions.\ Testing Situation\ When you arrive at the Sylvan Prometric Testing Center where you have scheduled your test, you will need to sign in with the testing coordinator. Be sure to have two forms of identification with you, at least one of which is a photo ID.\ After you have signed in, the coordinator has verified your identity, and your specific time for testing has arrived, you will be asked to leave any books, bags, or other items brought with you in a designated area. You will then be escorted to a closed room, often with closed circuit TV cameras—not that this adds to the stress of the testing situation or anything.\ In the room there will be, typically, from one to a dozen computers, separated from each other by dividers designed not as a sound barrier, but rather as a means to keep a tester at one workstation from viewing the happenings at another tester's workstation.\ You will either be furnished with a pen or pencil and a blank sheet of paper that you will be required to turn back in at the end of the testing session or with a dry erase marker and a laminated piece of paper also to be turned in post test. On this sheet, you can write down as much information as you can recall from your study sessions, as much of the memorized information as you can, as many notes as you can remember or as will fit on the page. You can refer to this page at any time during the test, so make the best use of it you can. You can also make notes during the test if one question reminds you of something that might be a help on another question.\ At least one wall of this room will consist of a plate glass window through which testing coordinators can monitor the testers to ensure that no one is talking to each other in the testing room. This is also the purpose of the closed circuit TV cameras.\ Your testing coordinator will launch your preloaded exam, and you will be permitted to start the exam as soon as you are seated at the workstation. You will sign in with your testing ID number, and after affirming that you are ready, the test will begin. You will have 90 minutes from this point in which to complete the test. The test itself will provide you with a countdown timer so that you know how much time you have remaining for the exam and will also tell you on which question number you currently are working. There will typically be several unscored randomly selected questions on each exam, so you are likely to have more than 60 questions on the test that you take. These unscored questions are designed to let Oracle determine good questions for future tests. You will have no way to determine which questions will be scored and which will be unscored, so do your best on all questions.\ Tip - Remember that the passing score varies with each exam. The passing score for test number 1Z0-031 is 44 out of 60 scored, or 73%.\ Question Handling\ All the Oracle certification exams are computer generated and are, typically, multiple choice, although I have seen a few completion answers. Although many people look forward to multiple choice, or multiple guess as we used to call them, these questions are constructed to fully assess your knowledge of the facts as well as to evaluate different circumstances.\ Read over the test once, completely, answering only those questions whose right answer screams at you. After this initial pass through the test, you will know what the questions are and which questions you will have the most trouble with. On the next pass, or the next couple of passes, you can tackle those questions that make you stop and think, leaving the really difficult ones to the very end. This tactic will be particularly helpful in knowing exactly how many difficult questions you have left after you answered the ones whose answers were apparent to you. This way you can better get a feel for how you are doing on the test as a whole and make better decisions on how much time to dedicate to those really difficult questions.\ Oracle has thought through the test design process (timed multiple choice), and the testing software is designed in such a way as to facilitate the timed multiple choice approach. At the bottom of every question is a check box next to the word "Mark." Checking this box allows you to quickly return to the marked question later.\ Make good use of your ability to mark questions that you want to quickly and easily return to later. Don't mark every question. There will be many questions that you will be able to answer quickly with an assurance of the answer. Don't second guess yourself on these; going back and rethinking them will often result in your doubting your first instincts and changing the answers, only to realize after the test that you probably really didn't want to make that change. Not only will you likely change at least one answer that was right to something that was wrong, but you also will be wasting precious time. You are only allotted a finite amount of time, and you can't afford to waste that time going back over questions that you were nearly certain about the first time. You may find yourself at the end of the test with questions left and no more time.\ Tip - Make sure that you read every word of every question completely. Do not get impatient and simply skim the question. You are likely to miss an important piece of information that could mean the difference between a right and a wrong answer.\ The exams are designed in such a way as to challenge you with most questions having one or two obviously wrong answers and at least two that are nearly alike, and you have to read carefully to make the appropriate choice.\ I cannot stress this enough. Read carefully and deliberately to avoid needless mistakes.\ Understanding the Question\ It is important that you not only read the questions carefully, but also that you make sure that you understand what the questions are really asking. It sometimes helps to draw out the question on the paper you are given, or to rewrite the answers in ways that you may remember from this book. The following list of suggestions may be helpful when you are working on understanding the questions that you are presented with.\ As you read each question, try to rephrase that question in your own words. If you can do this, you will likely find choosing the right answer much easier.\ After you have read the question the first time, reread every word again to make sure that you really understand what it is asking. This is particularly true if you have gone back to this question after having read the rest of the questions in the test. It is often the case that rereading the question after having had your attention on other questions makes the answer more obvious.\ If you have returned to the same question more than a couple of times, make sure that you understand what it is about the question you don't understand. Rearranging the information so that you have narrowed down what you are missing may make the question make more sense this time.\ If you find that you are approaching the end of your allotted time (keep an eye on that timer as you go) and you still have a few questions that you have left unanswered, it is better to go guess at an answer than to leave the question blank. They aren't going to not grade an unanswered question wrong (blank is equally as wrong as wrong is), and you have a statistical chance at guessing correctly.\ Remember, with these questions as with any multiple-choice question, there are often one or two questions that you can eliminate immediately as being obviously incorrect. This process of elimination should leave you with the correct answer or answers, and maybe one or two other answers, depending on the question format, from which to make your ultimate choice. Eliminate these and concentrate on those that are left.\ Obviously, wrong answers will likely have commands, tables, or views that are not only incorrect but also nonexistent.\ Personal Readiness\ Before you take this exam, before you take any of the Oracle exams, or in fact before you take any exam in life, it is important that you are confident that you are ready for the exam.\ Before you read this book, take the self-assessment test included with this book to find out how much you already know. Compare your score on the self-assessment test with the score required for passing the certification test to determine how ready you really are for the test. Taking this test also allows you to determine those places where you are lacking in your knowledge and where you can best apply your efforts in studying for the real thing.\ Take the test periodically during your studying to make sure that you are grasping the concepts in such a way that will allow you to answer the questions on the real test to the best of your ability and to give you the assurance that you are making the progress that you want to be making.\ After you have read through the Exam Cram book and the supplementary materials at the end of every chapter and have taken the practice tests, you will have a fairly good idea of when you will be ready to take the official exam. Although you could practice until you have just achieved a passing score on the practice tests (73% to 75%), I would highly recommend (the voice of experience, here) waiting until you have achieved scores of at least 80% to 85% on the practice tests to make sure that you have a comfort factor to fall back on in the testing situation. Your stress level will be higher in the real test than it is sitting in an easy chair in the comfort of your home.\ Physical Readiness\ Physical readiness is every bit as important as mental readiness, perhaps more so. Make sure that you get enough sleep the night before your exam. You will be mentally sharper if you have had sufficient rest, and you will be sure that you won't fall asleep during the exam. Cramming all night the night before, trying to memorize as much information as you can at the last minute will do you much less good than one or two more hours of sleep will.\ Don't go to the test center hungry; eat before you go. It would be counterproductive to overeat (it will take blood away from your brain and divert it to your stomach), but don't go with your stomach growling; it will distract you and any other testers in the room. Take with you a drink of water if it is permitted in your testing center. (The one where I take my tests provides paper cups and filtered water for exam takers; adequate hydration will make you more alert, as well.) Finally, overstudying can also be detrimental to your testing results. People often do better if they study until they feel comfortable and then take some time away from studying before the exam to take a walk, sit in the park, or just relax. Taking time to relax will likely lower your stress level, allow the knowledge that you have already gained to sink into your subconscious, and allow you to do better on the exam.\ Now, let's look at the best way to use this book. The following section gives you some advice on how I would use this book.\ Using the Book\ And now, we are on our way.\ Each chapter in this book follows a regular structure and provides graphical clues so that you can make note of important or particularly useful information.\ Caution - This is what an alert looks like. Alerts stress particularly important concepts, terms, software, or activities that will most likely appear in one or more test questions.\ Keep in mind, however, that not all information that will definitely be on the exam can be put into an alert; otherwise, the entire book would be an alert. Anything and everything in this book is likely to appear in a question on your exam.\ Tip - This is how a tip will be formatted. Stay alert for tips. They give you testing hints and suggestions that will make your testing experience and future career as a DBA more successful.\ Caution - This icon flags the presence of a potentially misleading question, often bordering on an outright trick question. They are calculated to trap you if you don't read them carefully and have a good understanding and working knowledge of the concepts that you are dealing with. Although there are not many of these in the Oracle certification exams, they do occasionally make an appearance on the tests, and knowing that they might be coming will help you be better prepared.\ The topics in this book are designed to build on each other. Some of the information in later chapters makes more sense if you have already read the earlier chapters. Because the information is reused from one chapter to another as a foundation on which to build, for the first pass through the book, I would suggest reading it front to back. If after the first pass you still need more work in one or more areas, and this will likely be the case, you can then go back and concentrate on those areas.\ Opening Hotlists\ Every chapter opens with a set of hotlists: "Terms You'll Need to Understand" and "Concepts You'll Need to Master." They let you know what to expect in the given chapter, what topics are covered, and what terms you need to be comfortable with by the time you have mastered the chapter.\ Terms\ Every chapter has its own vocabulary, grammar, set of terms, and syntaxes that you will need to master to ensure a successful test-taking experience.\ Tools\ Some chapters have a set of new tools that you must become comfortable with to master the chapter. It is important that you familiarize yourself with these tools for a successful testing experience.\ Techniques\ Every chapter has its own set of techniques, those processes that make up the meat of the test. Make note of the syntaxes used in the examples and work through them on your own. These may be as simple as budgeting your time and making the best use of the resources that you have, to the trickier aspects of deciding which of Oracle's many features to use in any given situation.\ Other Tools in the Book\ This book has several other tools that will help you prepare for your exam. The use of these tools will help you to better prepare and to feel more confident in your preparation.\ Practice Questions\ At the end of every chapter are several questions designed to make sure that you understand the concepts the chapter covered. Immediately following the questions is the correct answer along with an explanation of why it is correct and the incorrect answers along with explanations of why they are incorrect. Check yourself after you have read the chapter, and after a few days, go back and check yourself again. In this way, you can make doubly sure that you understand the material as you are covering it.\ Practice Exams\ Two practice exams are at the end of the book. They are designed to be similar to the actual exams. Time yourself taking them to get a feeling for how long it takes you to answer them.\ I have used this type of book in preparation for certification exams before, and I have found it useful to not mark the practice exams as I take them but either to copy the questions and mark up the copies or to just write down the answers on another piece of paper. This way I don't let the marks on the exam influence my decisions the next time I go over the questions.\ The first time, take your time. Read the question. If you can answer the question right off, great! If not, go back and check on the information and make sure that you understand what is being covered by the question.\ After you understand the questions, time yourself taking the exam. This gives you a feel for how you will react during the actual exam.\ Answer Keys\ Answer keys are another great resource included in this book. Not only do they give you the correct and incorrect answers, but better, they give you the reasons that the answers are right or wrong. Some of the questions are designed to have several nearly correct answers. The answer keys explain the reasons that the incorrect answers are wrong.\ Cram Sheet\ The exam cram sheet is designed as a mobile study tool. It is perforated so that you can easily remove it from the front of the book. It contains key points in a concise format that allows you to quickly recall and clue yourself on points that will be on the exam. These sheets have been the reason that several people have gotten one or two extra questions right—they recalled having seen the point on the cram sheet. Take the sheet to your exam appointment and give it a quick glance through right before your exam to refresh your memory.\ The Author\ If you want to use me as another resource, feel free to email me. I can be contacted at oracle_cram@yahoo.com.\ © Copyright Pearson Education. All rights reserved.

Ch. 1Oracle architecture components1Ch. 2Getting started with the Oracle Server33Ch. 3Creating a database59Ch. 4Managing an instance83Ch. 5Data dictionary content and usage111Ch. 6Storage structure and relationships129Ch. 7Globalization support147Ch. 8Maintaining control files171Ch. 9Maintaining redo log files193Ch. 10Managing tablespaces and data files213Ch. 11Managing undo data243Ch. 12Managing tables259Ch. 13Managing indexes289Ch. 14Maintaining data integrity311Ch. 15Managing users329Ch. 16Managing password security and resources341Ch. 17Managing privileges363Ch. 18Managing roles381Ch. 19Practice exam 1397Ch. 20Exam 1 answers415Ch. 21Practice exam 2427Ch. 22Exam 2 answers439