Introduction

There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credentials in the database world. You have made the right decision to pursue your Oracle certification, because achieving it will give you a distinct advantage in this highly competitive market.

Most of you should already be familiar with Oracle and need no introduction to the Oracle database world. Here's some information for those who aren't. Oracle, founded in 1977, sold the first commercial relational database and is now the world's leading database company and second-largest independent software company, with revenues of more than $10 billion, serving more than 145 countries.

Oracle databases are the de facto standard for large Internet sites, and Oracle advertisers are boastful but honest when they proclaim, "The Internet Runs on Oracle." Almost all big Internet sites run Oracle databases. Oracle's penetration of the database market runs deep and is not limited to dot-com implementations. Enterprise resource planning (ERP) application suites, data warehouses, and custom applications at many companies rely on Oracle. The demand for DBA resources remains higher than others during weak economic times.

This book is intended to help you pass the Oracle Database 10g: Administration I exam, which will establish your credentials as an Oracle Database Administrator. The OCA certification is a prerequisite to obtaining the more comprehensive Oracle Certified Professional (OCP) certification and is the first step toward obtaining an Oracle Certified Master (OCM) certification. Using this book and a practice database, you can acquire the necessary skills to pass the 1Z0-042 Oracle Database 10g: Administration I exam.

Why Become Oracle Certified?

The number one reason to become an OCA or OCP is to gain more visibility and greater access to the industry's most challenging opportunities. Oracle certification is the best way to demonstrate your knowledge and skills in Oracle database systems.

Certification is proof of your knowledge and shows that you have the skills required to support Oracle core products. The Oracle certification program can help a company identify proven performers who have demonstrated their skills and who can support the company's investment in Oracle technology. It demonstrates that you have a solid understanding of your job role and the Oracle products used in that role.

OCPs are among the best paid in the IT industry. Salary surveys consistently show the OCP certification to yield higher salaries than other certifications, including Microsoft, Novell, and Cisco.

So whether you are beginning your career, changing your career, or looking to secure your position as a DBA, this book is for you!

Oracle Certifications

Oracle certifications follow a track that is oriented toward a job role. There are database administration, application developer, and web application server administrator tracks. Within each track, Oracle has a multitiered certification program.

Within the administration track, there are three tiers:

  • The first tier is the Oracle 10g Certified Associate (OCA). To obtain OCA certification, you must pass the 1Z0-042 Oracle Database 10g: Administration I exam in a proctored setting.

  • The second tier is the Oracle 10g Certified Professional (OCP), which builds on and requires OCA certification. To obtain OCP certification, you must attend an approved Oracle University hands-on class and pass the 1Z0-043 Oracle Database 10g: Administration II exam in a proctored setting.

  • The third and highest tier is the Oracle 10g Certified Master (OCM), which builds on and requires OCP certification. To obtain OCM certification, you must attend advanced-level classes and take a two-day, hands-on practical exam.

The material in this book addresses only the Administration I exam. Other Sybex books— which can be found at www.sybex.com—can help students new to the DBA world prepare for the OCP exam Oracle Database 10g: Administration II exam (1Z0-043). You can also get information on the Oracle upgrade exam, the Oracle Database 10g: New Features for Administrators exam (1Z0-040).

NOTE

See the Oracle website at www.oracle.com/education/certification for the latest information on all of Oracle's certification paths along with Oracle's training resources.

Oracle DBA Certification

The role of the DBA has become a key to success in today's highly complex database systems. The best DBAs work behind the scenes, but are in the spotlight when critical issues arise. They plan, create, maintain, and ensure that the database is available for the business. They are always watching the database for performance issues and to prevent unscheduled downtime. The DBA's job requires broad understanding of the architecture of Oracle database and expertise in solving problems.

Because this book focuses on the DBA track, we will take a closer look at the tiers of the DBA track.

Oracle Database 10g Administrator Certified Associate

The Oracle 10g Administrator Certified Associate (OCA) certification is a streamlined, entrylevel certification for the database administration track and is required to advance toward the more senior certification tiers. This certification requires you to pass one exam that demonstrates your knowledge of Oracle basics:

  • 1Z0-042 Oracle Database 10g: Administration I

Oracle Database 10g Administrator Certified Professional

The OCP tier of the database administration track challenges you to demonstrate your enhanced experience and knowledge of Oracle technologies. The Oracle 10g Administrator Certified Professional (OCP) certification requires achievement of the OCA certification, attendance at one or more approved Oracle University classes, and successful completion of the following exam:

  • 1Z0-043 Oracle Database 10g: Administration II

The approved courses for OCP candidates include the following:

  • Oracle Database 10g: Administration I

  • Oracle Database 10g: Administration II

  • Oracle Database 10g: Introduction to SQL

  • Oracle Database 10g: New Features for Administrators

  • Oracle Database 10g: Program with PL/SQL

If you already have your OCP in 9i or earlier and have elected to take the upgrade path, you are not required to take the Oracle University class to obtain your OCP for Oracle 10g.

NOTE

Verify this list against the Oracle education website (www.oracle.com/education) as it can change without any notice.

Oracle Database 10g Certified Master

The Oracle Database 10g Administration Certified Master (OCM) is the highest level of certification that Oracle offers. To become a certified master, you must first obtain OCP certification, then complete advanced-level classes at an Oracle Education facility, and finally pass a hands-on, two-day exam at an Oracle Education facility. The classes and practicum exam are offered only at an Oracle Education facility and may require travel.

NOTE

More details on the required coursework will be available in late 2004.

Oracle 10g Upgrade Paths

Existing Oracle professionals can upgrade their certification in several ways:

  • An Oracle9i OCP can upgrade to 10g certification by passing the 1Z0-040 Oracle Database 10g: New Features for Administrators exam.

  • An Oracle8i OCP can upgrade directly to 10g by passing the 1Z0-045 Oracle Data–base 10g: New Features for Oracle8i OCP exam.

  • Oracle7.3 and Oracle8 DBAs must first upgrade to an Oracle9i certification with the 1Z0-035 Oracle9i DBA: New Features for Oracle7.3 and Oracle8 OCP exam and then upgrade the 9i certification to 10g with the 1Z0-040 Oracle Database 10g: New Features for Administrators exam.

Oracle Database 10g Administrator Special Accreditations

New to the Oracle certification program are the Oracle Database 10g Administrator Special Accreditation programs. These accreditations formally recognize the specialized knowledge of OCPs in particular database administration areas such as high availability, security, and 10g Grid Control. OCPs who pass one of these special accreditation exams receive a certificate that formally recognizes their special competency.

Oracle Database 10g DBA Assessment

Oracle also provides an optional (and free) prerequisite to all the proctored exams, which is the Oracle Database 10g DBA Assessment online exam:

  • 1Z0-041 Oracle Database 10g: DBA Assessment

This exam evaluates your proficiency with basic administration and management of an Oracle 10g database, and upon passing this online exam, you receive a certificate of completion from Oracle University. Although anybody can take this exam, it is designed for those new to Oracle and is an excellent measurement of how familiar you are with the new Oracle 10g database.


Oracle Exam Requirements

The Oracle Database 10g: Administration I exam covers several core subject areas. As with many typical multiple-choice exams, you can take advantage of several tips to maximize your score on the exam.

Skills Required for the Oracle Database 10g: Administration I Exam

To pass the Oracle 10g Administration I exam, you need to master the following subject areas in Oracle 10g:

Installing Oracle Database 10g Software

Identify system requirements.
Use Optimal Flexible Architecture (OFA).
Install software with Oracle Universal Installer.
Identify and configure commonly used environment variables.

Creating an Oracle Database

Explain the Oracle database architecture.
Explain the instance architecture.
Use the management framework.
Use DBCA (Database Configuration Assistant) to create a database.
Use DBCA to configure a database.
Use DBCA to drop (or delete) a database.
Use DBCA to manage templates.

Database Interfaces

Use SQL*Plus and iSQL * Plus to access an Oracle 10g database.
Use SQL*Plus and iSQL * Plus to describe the logical structure of tables.
Use SQL to query, manipulate, and define data using SELECT, UPDATE/INSERT/DELETE, and CREATE/ALTER/DROP statements.
Identify common database interfaces.
Describe a database transaction.

Controlling the Database

Start and stop iSQL*Plus.
Start and stop Enterprise Manager (EM) Database Control.
Start and stop the Oracle Listener.
Start up and shut down Oracle Database 10g.
Describe startup and shutdown options for Oracle Database 10g.
Handle parameter files.
Locate and view the Database alert log.

Storage Structures

Define the purpose of tablespaces and datafiles.
Create tablespaces.
Manage tablespaces (alter, drop, generate DDL, take offline, put on line, add data files, make read-only/read-write).
Obtain tablespace information from EM and the data dictionary views.
Drop tablespaces.
Describe the default tablespaces.

Administering Users

Create and manage database user accounts.
Create and manage roles.
Grant and revoke privileges.
Control resource usage by users.

Managing Schema Objects

Create and modify tables.
Define constraints.
View the attributes of a table.
View the contents of a table.
Create indexes and views.
Name database objects.
Select appropriate datatypes.
Create and use sequences.

Managing Data

Manipulate data through SQL using INSERT, UPDATE, and DELETE.
Use Data Pump to export data.
Use Data Pump to import data.
Load data with SQL*Loader.
Create directory objects.

PL/SQL

Identify PL/SQL (Procedural Language SQL) objects.
Describe triggers and triggering events.
Identify configuration options that affect PL/SQL performance.

Oracle Database Security

Apply the principle of least privilege.
Manage default user accounts.
Implement standard password security features.
Audit database activity.
Register for security updates.

Oracle Net Services

Use Database Control to create additional listeners.
Use Database Control to create Oracle Net service aliases.
Use Database Control to configure connect time failover.
Use Listener features.
Use the Oracle Net Manager to configure client and middle-tier connections.
Use TNSPING to test Oracle Net connectivity.
Describe Oracle Net Services.
Describe Oracle Net names resolution methods.

Oracle Shared Servers

Identify when to use Oracle Shared Servers.
Configure Oracle Shared Servers.
Monitor Shared Servers.
Describe the Shared Server architecture.

Performance Monitoring

Troubleshoot invalid and unusable objects.
Gather optimizer statistics.
View performance metrics.
React to performance issues.

Proactive Maintenance

Set warning and critical alert thresholds.
Collect and use baseline metrics.
Use tuning and diagnostic advisors.
Use the Automatic Database Diagnostic Monitor (ADDM).
Manage the Automatic Workload Repository.
Describe server-generated alerts.

Undo Management

Monitor and administer undo.
Configure undo retention.
Guarantee undo retention.
Use the Undo Advisor.
Describe the relationship between undo and transactions.
Size the undo tablespace.

Monitoring and Resolving Lock Conflicts

Detect and resolve lock conflicts.
Manage deadlocks.
Describe the relationship between transactions and locks.
Explain lock modes within Oracle Database 10g.

Backup and Recovery Concepts

Describe the basics of database backup, restore, and recovery.
Describe the types of failure that can occur in an Oracle 10g database.
Describe ways to tune instance recovery.
Identify the importance of checkpoints, redo log files, and archived log files.
Configure ARCHIVELOG mode.
Configure a database for recoverability.

Database Backups

Create consistent database backups.
Back up your database without shutting it down.
Create incremental backups.
Automate database backups.
Monitor the Flash Recovery area.
Describe the difference between image copies and backup sets.
Describe the different types of database backups.
Back up a control file to trace.
Manage backups.

Database Recovery

Recover from loss of a control file.
Recover from loss of a redo log file.
Recover from loss of a system-critical datafile.
Recover from loss of a non–system-critical datafile.

Tips for Taking the OCA Exam

Use the following tips to help you prepare for and pass the exam:

  • The exam contains about 55–80 questions to be completed in 90 minutes. Answer the questions you know first so that you do not run out of time.

  • At first glance, the answer choices to many questions look identical. Read the questions carefully. Do not jump to conclusions. Make sure that you clearly understand exactly what each question asks.

  • Some questions are scenario-based. Some scenarios contain nonessential information and exhibits. You need to be able to identify what's important and what's not important.

  • Do not leave any questions unanswered. There is no negative scoring. After selecting an answer, you can mark a difficult question or one that you're unsure of and come back to it later.

  • When answering questions that you are not sure about, use a process of elimination to get rid of the obviously incorrect answers first. Doing this greatly improves your odds if you need to make an educated guess.

  • If you're not sure of your answer, mark it for review, and then look for other questions that might help you eliminate any incorrect answers. At the end of the test, you can go back and review the questions that you marked for review.

NOTE

Be familiar with the exam objectives, which are included in the front of this book as a perforated tear-out card. You can also find them at www.oracle.com/education/certification/objectives/42.html. In addition, if you would like information on recommended classes and passing scores, visit www.oracle.com/education/certification/news/beta_042.html.

Where Do You Take the Certification Exam?

The Oracle Database 10g certification exams are available at any of the more than 900 Thomson Prometric Authorized Testing Centers around the world. For the location of a testing center near you, call 1-800-891-3926. Outside the United States and Canada, contact your local Thomson Prometric Registration Center.

To register for a proctored Oracle Certified Associate exam:

  • Determine the number of the exam you want to take. For the OCA exam, it is 1Z0-042.

  • Register with Thomson Prometric online at www.prometric.com or, in North America, by calling 1-800-891-EXAM (800-891-3926). At this point, you will be asked to pay in advance for the exam. At the time of this writing, the exams are $125 each and must be taken within one year of payment.

  • When you schedule the exam, you'll get instructions regarding all appointment and cancellation procedures, the ID requirements, and information about the testing-center location.

You can schedule exams up to six weeks in advance or as soon as one working day before the day you want to take it. If something comes up and you need to cancel or reschedule your exam appointment, contact Thomson Prometric at least 24 hours or one business day in advance.

What Does This Book Cover?

This book covers everything you need to pass the Oracle Database10g Administration I exam. Each chapter begins with a list of exam objectives.

Chapter 1 Discusses some of the first activities you will need to perform. You will learn how to install the Oracle database software, about the architecture of both an Oracle database and instance, and how to use some common database interfaces as well as how to use SQL to define, manipulate, and access data in your database.

Chapter 2 Discusses creating and controlling the Oracle database environment. You will learn how to start and stop the database, EM Database Control, and iSQL*Plus.

Chapter 3 Discusses storage structures and schema objects. You will learn how to create and manage physical database structures such as tablespaces, datafiles, segments, extents, and blocks. You will also learn about logical database structures such as tables, indexes, views, and sequences.

Chapter 4 Discusses Oracle Net services. You will learn how to create and control database listeners, Net service names, and connect time failover.

Chapter 5 Discusses Oracle Shared Servers. You will learn about the Shared Server architecture, when to use Shared Servers, and how to configure and monitor Shared Servers.

Chapter 6 Discusses administering users and database security. You will learn how to create and manage user accounts, roles, privileges, password controls, and auditing.

Chapter 7 Discusses manipulating data and administering PL/SQL programs. You will learn how to work with data using SQL, Data Pump, SQL*Loader, and triggers and how to tune the database for PL/SQL performance.

Chapter 8 Discusses consistency and concurrency. You will learn how to configure and administer your undo tablespace. You will also learn about Oracle locking and how to detect and resolve locking conflicts.

Chapter 9 Discusses performance monitoring and proactive maintenance. You will learn how to administer invalid and unusable objects, gather optimizer statistics, work with performance metrics, set alert thresholds, and use the tuning and diagnostic advisors.

Chapter 10 Discusses backing up your Oracle 10g database. You will learn about Oracle backup concepts, how to create and manage consistent, online, and incremental backups. You will also learn how to monitor the Flash Recovery area.

Chapter 11 Discusses failures and how to recover your Oracle10g database from them. You will learn about the various kinds of failures that can occur with an Oracle10g database and how to recover from the loss of control files, redo logs, and datafiles.

Each chapter ends with a list of exam essentials, which gives you a highlight of the chapter, with an emphasis on the topics that you need to be extra familiar with for the exam. The chapter concludes with 20 review questions that are specifically designed to help you retain the knowledge presented. To really nail down your skills, read and answer each question carefully.

How to Use This Book

This book can provide a solid foundation for the serious effort of preparing for the Oracle 10g OCA exam. To best benefit from this book, use the following study method:

  1. Take the Assessment Test immediately following this introduction. (The answers are at the end of the test.) Carefully read over the explanations for any questions you get wrong, and note which chapters the material comes from. This information should help you plan your study strategy.

  2. Study each chapter carefully, making sure that you fully understand the information and the test objectives listed at the beginning of each chapter. Pay extra close attention to any chapter related to questions that you missed in the Assessment Test.

  3. Complete all hands-on exercises in the chapter, referring to the chapter so that you understand the reason for each step you take. If you do not have an Oracle database available, be sure to study the examples carefully.

  4. Answer the review questions related to that chapter. (The answers appear at the end of each chapter, after the "Review Questions" section.) Note the questions that confuse or trick you, and study those sections of the book again.

  5. Take the two Bonus Exams that are included on the accompanying CD. This will give you a complete overview of what you can expect to see on the real test.

  6. Remember to use the products on the CD included with this book. The electronic flashcards and the Sybex Test Engine exam preparation software have been specifically designed to help you study for and pass your exam.

To learn all the material covered in this book, you'll need to apply yourself regularly and with discipline. Try to set aside the same time period every day to study, and select a comfortable and quiet place to do so. If you work hard, you will be surprised at how quickly you learn this material. All the best!

What's on the CD?

We have worked hard to provide some really great tools to help you with your certification process. Load all the following tools on your workstation when you're studying for the test.

The Sybex Test Engine Preparation Software

This test-preparation software prepares you to pass the 1Z0-042 Oracle Database 10g Administration I exam. In this test, you will find all the questions from the book, plus two bonus exams that appear exclusively on the CD. You can take the Assessment Test, test yourself by chapter, or take the practice exams. The test engine will run on either a Microsoft Windows or a Linux platform.

Here is a sample screen from the Sybex Test Engine:



Electronic Flashcards for PC and Palm Devices

You can also test yourself with the flashcards included on the CD. The flashcards are designed to test your understanding of the fundamental concepts covered in the exam. Here is what the Sybex Flashcards interface looks like:



OCA: Oracle 10g Administration I Study Guide in PDF

Many people like the convenience of being able to carry their study guide on a CD, which is why we included the book in PDF format. This will be extremely helpful to readers who fly or commute on a bus or train and prefer an e-book, as well as to readers who find it more comfortable reading from their computer. We've also included a copy of Adobe Acrobat Reader on the CD.

About the Authors

Chip Dawes is an Oracle Certified Professional with more than 15 years' experience as a DBA. He lives, works, and plays in the Chicagoland area with his wife, Mary, children Zachary and Charlie, and dog Rex. Chip works for D&D Technologies, a Chicago-based consulting firm. To contact Chip, you can e-mail him at [email protected].

Matthew Weishan, OCP, is a senior specialist for EDS in Madison, Wisconsin. To contact Matt, you can e-mail him at [email protected].

Joseph C. Johnson is an Oracle Certified Professional with more than 10 years of experience managing mission-critical Oracle databases. Joe is a Senior Database Administrator with Lands' End in Dodgeville, Wisconsin. You can e-mail him at [email protected].

Bob Bryla, OCP, currently works as a Data Analyst and Oracle DBA for Lands' End. To contact Bob, you can e-mail him at [email protected].

Assessment Test

  1. Which of the following components is not part of an Oracle instance?

    1. System Global Area

    2. Server Process

    3. Database Writer

    4. System Monitor

  2. Your organization has purchased an application that uses an Oracle database as the repository for application data. Your job is to install and configure the Oracle Database 10g software on the server. Which of the following should you do first?

    1. Create mount points and directories using the OFA model.

    2. Mount the Oracle CD and start the Oracle Universal Installer.

    3. Review the installation guides and release notes to familiarize yourself with the install process.

    4. Work with the Unix system administrator to configure kernel parameters.

  3. All the following are examples of DML commands except which item?

    1. INSERT

    2. CREATE

    3. UPDATE

    4. DELETE

  4. Assuming the ON DELETE CASCADE option was not used to define the constraint, what is the potential impact of a foreign key constraint on a DELETE statement?

    1. The foreign key constraint can prevent the delete if deleting the row violates the relationship defined in the foreign key.

    2. The foreign key constraint ensures that the user has the correct privileges on a table before the row is deleted.

    3. The foreign key is ignored if the delete removes only one row.

    4. Foreign key has no impact on DELETE statements.

  5. You are a database administrator with databases in New York, Hong Kong, and London. You want to administer all your databases from a central console using Grid Control. Which of the following commands do you issue when using Grid Control?

    1. emctl start grid

    2. emctl start dbconsole

    3. emctl start agent

    4. isqlplusctl start

  6. The lead DBA has requested that you set up a database that requires minimal disk management for a large data warehouse that is being implemented. He wants you to use a DBCA with and utilize one of the predefined templates. Which of the following combinations addresses this request?

    1. Data Warehouse template with raw devices

    2. Transactional template with file system disk management

    3. Data Warehouse template with raw disk management

    4. Data Warehouse template with the Automated Storage Management (ASM) disk option

  7. You have created a database using DBCA and not saved the template definition. You now want to copy the database and all the data using the DBCA. Which of the following is the first step?

    1. Create a new empty database using scripts, and import the data into the new database using the DBCA.

    2. Use DBCA template management, and create a database template from an existing database with structure as well as data.

    3. Choose the template of the previously created database created from the templates list and create the database.

    4. Copy the datafiles from the existing database to a new location, and then use the DBCA to copy the database.

  8. You are in the middle of an emergency drill at your organization. There is little time to notify database users, and you need to stop the Oracle database processes as quickly as possible. Which of the following would you perform?

    1. SHUTDOWN ABORT

    2. SHUTDOWN TRANSACTIONAL

    3. SHUTDOWN

    4. SHUTDOWN IMMEDIATE

    5. None of the above

  9. Where would you look to see if a tablespace is offline?

    1. dba_tablespaces

    2. v$tablespace

    3. v$database

    4. dba_datafile_status

  10. Which of the following objects share the same namespace and therefore cannot have the same name?

    1. Tables and sequences

    2. Tables and indexes

    3. Tables and tablespaces

    4. Tables and constraints

  11. Which of the following is not a valid column name?

    1. 1ST_ID

    2. CUST#

    3. ADDRESS1

    4. EXCEPTION

  12. Oracle Advanced Security provides all the following except:

    1. Database profiles

    2. Data encryption

    3. Checksumming

    4. Biometrics option

  13. You have just issued the command lsnrctl stop LISTENER. Which of the following statements are not true? (Choose all that apply.)

    1. New connections to the default listener will not succeed.

    2. Existing client connections will not be affected.

    3. Only new dedicated connections will fail

    4. Existing shared server connections will be dropped.

  14. You issue the following command from the database server: sqlplus scott/tiger and receive the following error message: ORA-01034: Oracle not available. What is the state of the Oracle database:

    1. The database is open but in restricted mode.

    2. The instance is started but is in MOUNT mode.

    3. The database and the instance are not started.

    4. The instance is started in NOMOUNT mode.

  15. You connect to the database using the command sqlplus scott/[email protected]:1522/orcl. To which database are you connecting?

    2. tiger

    3. orcl

    4. scott

    5. None of the above

  16. You are administering an Oracle database using Shared Server. The LARGE_POOL_SIZE is 50MB. You issue the command ALTER SYSTEM SET LARGE_POOL_SIZE = 100M SCOPE=MEMORY. You then shut down and restart the database. What will the LARGE_POOL_SIZE be?

    1. 50MB

    2. 100MB

    3. The default LARGE_POOL_SIZE

    4. The LARGE_POOL_SIZE as you have set it in the Oracle SPFILE that you are using

  17. You have decided to implement connection pooling and set a timeout limit of 20 minutes for idle connections. You also want to support 500 concurrent connections with a maximum of 1500 sessions per dispatcher. Which of the following commands will accomplish this?

    1. Dispatchers = "(PRO=TCP)(DIS=5)(POO=ON)(TICK=20)(CONN=500)(SESS=1500)"

    2. Dispatchers = "(PRO=TCP)(DIS=5)(POO=ON)(TICK=2)(CONN=500)(SESS=1500)"

    3. Dispatchers = "(PRO=TCP)(DIS=5)(POO=ON)(TICK=20)(CONN=1500)(SESS=500)"

    4. Dispatchers = "(PRO=TCP)(DIS=5)(POOLING=ON)(TICK=20)(CONN=500)(SESS=1500)"

  18. You issue the command lsnrctl services. What pieces of information can you see regarding shared server connections for each dispatcher listed? (Choose all that apply.)

    1. Established connections

    2. Refused connections

    3. Idle connections

    4. Current connections

    5. All the above

  19. What status would you expect to see when querying V$DISPATCHER for a dispatcher that is not currently servicing any client requests?

    1. IDLE

    2. WAIT

    3. SLEEP

    4. READY

    5. None of the above

  20. Which of the following statements sets the password for user simon to alakazaam?

    1. alter user simon password alakazaam;

    2. update user simon set password=alakazaam;

    3. alter user simon identified by alakazaam;

    4. set password=alakazaam for simon;

  21. Which privilege allows the grantee to place the database in NOARCHIVELOG mode?

    1. sysdba

    2. root

    3. dba

    4. operator

  22. Which of the following statements removes user brent together with all his schema objects from the database?

    1. drop user brent;

    2. delete from dba_users where username='BRENT';

    3. alter system remove user brent cascade;

    4. drop user brent cascade;

  23. Which of the following parameters directs Data Pump export to capture the table definitions for, but not the contents of the tables owned by user sacagawea?

    1. owner= sacagawea rows=no

    2. schemas= sacagawea content=metadata_only

    3. owner= sacagawea content=metadata_only

    4. tablespaces= sacagawea data=no

  24. Which of the following is not a valid Data Pump export mode?

    1. JAVA

    2. SCHEMA

    3. TABLE

    4. TABLESPACE

  25. After updating a table, what must you execute to make the changes permanent?

    1. COMMIT

    2. SAVEPOINT

    3. Nothing. When the updates complete, the changes are permanent.

    4. SAVE WORK

  26. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?

    1. Temporary segment

    2. Redo log buffer

    3. Undo buffer

    4. Undo segment

  27. Which of the following will not implicitly begin a transaction?

    1. INSERT

    2. UPDATE

    3. DELETE

    4. SELECT FOR UPDATE

    5. None of the above; they all implicitly begin a transaction.

  28. Select the invalid statements from the following regarding undo segment management. (Choose all that apply.)

    1. ALTER SYSTEM SET UNDO_TABLESPACE = ROLLBACK;

    2. ALTER DATABASE SET UNDO_TABLESPACE = UNDOTBS;

    3. ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO;

    4. ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL;

  29. The following table shows two concurrent transactions. Which statement about the result returned in Session 1 at 8:30 is true? Choose the best answer.

    Session 1TimeSession 2
    select sum(order_amt) from orders where order_date > trunc(sysdate);8:01 
     8:09insert into orders (cust_num, order_date, order_amt) values (19581963, sysdate, 576.12);
     8:10commit;
    select statement above reaches data block where insert from session 2 resides8:15 
    select statement completes and results returned to the user8:30 

    1. The results include the changes committed by session 2 at 8:10.

    2. The results include the changes committed by session 2 at 8:10 if the two sessions were connected as the same user.

    3. The results of the query in session 1 do not include the changes committed by session 2 at 8:10.

    4. Session 1 instead returns a "Snapshot too old" error message at 8:30; no results are returned to the user, and the query must be resubmitted.

  30. The Automatic Workload Repository (AWR) is primarily populated with performance statistics by which Oracle 10g background process?

    1. MMNL

    2. QMN1

    3. MMON

    4. MMAN

  31. Which of the following advisors is used to determine if the database read-consistency mechanisms are properly configured?

    1. Undo Management Advisor

    2. SQL Access Advisor

    3. SQL Tuning Advisor

    4. Memory Advisor

  32. You've decided to monitor your databases for CPU utilization based on baseline metrics. If you specify that you want a warning alert sent whenever CPU utilization is greater than 50 percent of the baseline and the high value for this metric is 300, when will the alert be sent?

    1. Whenever CPU utilization is 250 or less

    2. Whenever the CPU utilization is 450 or higher

    3. Whenever the CPU utilization is 300 or higher

    4. Whenever the CPU utilization is 225 or higher

  33. Every evening you use SQL*Loader to perform direct-path loads of data into your company's data warehouse. Last night the load of the SALES table failed because the tablespace where the SALES table is stored ran out of space. As a result, the indexes on the SALES table are in an unusable state. Which of the following can you use to fix this problem?

    1. Drop and re-create the affected indexes.

    2. Use the ALTER INDEX ... REBUILD command to rebuild the index.

    3. Use EM Database Control to rebuild the index.

    4. Any of the above will return the index to a usable state.

  34. Which backup options are available for databases in NOARCHIVELOG mode? (Choose all that apply.)

    1. Online, incremental

    2. Offline, incremental

    3. Online, full, whole database

    4. Offline, full, whole database

  35. According to Oracle, how many copies of the control file should you maintain?

    1. 1, since Oracle automatically multiplexes control files

    2. 2

    3. One copy on each Oracle-managed disk device

    4. 3

  36. Identify the types of backups in the following list that RMAN performs. (Choose all that apply.)

    1. Backups to flat files

    2. Image copies

    3. Data Pump export dumps

    4. Backup sets

  37. Archived log files can be written to as many as _______ locations. (Choose the best answer.)

    1. Ten, but they all have to be local, or they all have to be remote.

    2. Ten, with a maximum of five local destinations and five remote destinations

    3. Ten total

    4. Five total

    5. Twenty, a maximum of ten local destinations, and ten remote destinations

  38. If a user inadvertently drops a table, this is considered what type of failure?

    1. Instance

    2. User error

    3. Statement

    4. User process

  39. If the database instance fails, but will not shut down, you must use which command(s) before instance recovery can be performed? (Choose the best answer.)

    1. STARTUP NOMOUNT

    2. STARTUP MOUNT

    3. SHUTDOWN IMMEDIATE; STARTUP FORCE

    4. SHUTDOWN ABORT; STARTUP

  40. Which of the following is not a valid database state?

    1. OPEN

    2. SHUTDOWN

    3. STARTUP

    4. MOUNT

    5. NOMOUNT

Answers to Assessment Test

  1. B. The Server Process that is started on the server on behalf of each user connection is not considered part of the instance. An instance is defined as the System Global Area and all the Oracle background processes. For more information, see Chapter 1.

  2. C. Review all relevant documentation before starting the Oracle installation. The documentation will help you determine whether your hardware and operating system meet the minimum specifications required for the installation. For more information, see Chapter 1.

  3. B. The CREATE statement is an example of a DDL statement, not a DML statement. DROP and ALTER are also examples of DDL commands. For more information, see Chapter 1.

  4. A. A foreign key constraint prevents the deletion of a record if other records in the same table or other tables still refer to that record. If the ON DELETE CASCADE option is used when the constraint is defined, deleting the primary key record also automatically deletes the associated foreign key records. For more information, see Chapter 1.

  5. C. When you are using the Grid Control, start the agent on each managed target machine. The emctl start agent command initiates the agent process. For more information, see Chapter 2.

  6. D. You use the Data Warehouse template with the ASM disk option to get the requested combination. For more information, see Chapter 2.

  7. B. First, make a template definition of the existing database using the DBCA tool. You can then create the database from this definition. For more information, see Chapter 2.

  8. A. Given the circumstances, the quickest way to shut down the Oracle processes is a SHUTDOWN ABORT. This is not the preferred shutdown method and requires instance recovery upon startup. For more information, see Chapter 2.

  9. A. The dba_tablespaces and v$tablespace views contain information about tablespaces. The dba_tablespaces view includes the STATUS column, which indicates whether the tablespace is online or offline. For more information, see Chapter 3.

  10. A. Tables share a namespace with views, sequences, private synonyms, procedures, functions, packages, materialized views, and user-defined types. Objects sharing a namespace cannot have the same name. For more information, see Chapter 3.

  11. A. Column names cannot begin with a digit. They must begin with a letter and can contain letters, numbers, and the characters _, $, or # (underscore, dollar sign, or pound sign). Although a poor practice, PL/SQL keywords, such as EXCEPTION, can be used as a column name. For more information, see Chapter 3.

  12. A. The Oracle Advanced Security feature provides additional database security options such as encryption, checksumming, and user authentication via options such as the biometrics. This feature is not necessary to use database profiles. For more information, see Chapter 4.

  13. C, D. When you stop a listener, you affect only new client connections and not existing client connections, so C and D are false. For more information, see Chapter 4.

  14. C. Whenever you receive an ORA-01034, both the database and the instance are not started. For more information, see Chapter 4.

  15. C. The database is the parameter supplied after the port designation. Therefore, you connect to the orcl database. For more information, see Chapter 4.

  16. D. The LARGE_POOL_SIZE setting is determined by the setting in the Oracle SPFILE. Oracle always reads this file on startup if you are using it. The SCOPE=MEMORY parameter means that only the currently running instance is affected by the change. For more information, see Chapter 5.

  17. B. You would set the PRO, DIS, POO, TICK, CONN, and SESS attributes of the DISPATCHERS parameter accordingly. Remember that the TICK measurement is in 10-minute increments. For more information, see Chapter 5.

  18. A, B, D. You can see established, refused, and current connections for each dispatcher when you issue this command. You can also see the maximum number of connections allowed per dispatcher and the state of each dispatcher. For more information, see Chapter 5.

  19. B. The WAIT status indicates that the dispatcher is waiting to process a client request. For more information, see Chapter 5.

  20. C. You use the ALTER USER statement to change a password. The keywords IDENTIFIED BY tell the database to assign a new password to the user. For more information, see Chapter 6.

  21. A. The privilege SYSDBA is the most powerful system privilege; it allows the grantee to start up and shut down the database as well as enable ARCHIVELOG mode or NOARCHIVELOG mode. For more information, see Chapter 6.

  22. D. A DROP USER statement removes an account from the database. The keyword CASCADE tells the database to also drop all the schema objects owned by the user. For more information, see Chapter 6.

  23. B. The schemas parameter tells Data Pump export which object owners to include in the extract. The content parameter tells Data Pump what to export: DATA_ONLY, METADATA_ONLY, or ALL. The METADATA_ONLY value tells Data Pump to capture the object definitions but not the stored data values. For more information, see Chapter 7.

  24. A. Data Pump modes include FULL, SCHEMA, TABLE, and TABLESPACE, but not JAVA. For more information, see Chapter 7.

  25. A. After changing data with an INSERT, UPDATE, or DELETE statement, you must execute a COMMIT to make the changes permanent. A SAVEPOINT establishes an intermediate place holder in the data changes that you can optionally ROLLBACK to prior to a COMMIT. There is no SAVE statement in an Oracle 10g database. For more information, see Chapter 7.

  26. D. Before any DML operation is marked as complete, the undo information (the before image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data. For more information, see Chapter 8.

  27. E. If a transaction is not currently in progress, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement implicitly begins a transaction. For more information, see Chapter 8.

  28. B, C, D. Choice A is the only valid statement; an undo tablespace can have any name that follows the Oracle standard naming conventions for identifiers. Choice B is incorrect because undo tablespaces are not managed using ALTER DATABASE. Choices C and D are incorrect because the parameter UNDO_MANAGEMENT cannot be changed while the instance is running. For more information, see Chapter 8.

  29. C. Read consistency ensures that the data visible to each session does not change when the statement is executing. For more information, see Chapter 8.

  30. C. The Memory Monitor (MMON) process gathers performance statistics from the SGA (System Global Area) and stores them in the AWR. MMNL (Memory Monitor Light) also does some AWR-related statistics gathering, but not to the extent that MMON does. QMN1 is the process that monitors Oracle advanced queuing features. MMON is the process that dynamically manages the sizes of each SGA component when directed to make changes by the ADDM (Automatic Database Diagnostic Monitoring). For more information, see Chapter 9.

  31. A. You can use the Undo Management Advisor to monitor and manage the undo segments to ensure maximum levels of read consistency and minimize occurrences of ORA-01555: Snapshot Too Old error messages. For more information, see Chapter 9.

  32. B. The alert threshold will be defined as 300 * ( 1 + 50/100), or 450 percent. For more information, see Chapter 9.

  33. D. Dropping or rebuilding an index changes the index from an unstable to a usable state. For more information, see Chapter 9.

  34. D. If the database is in NOARCHIVELOG mode, only offline, full, whole database backups can be performed. As a result, any database failure loses committed transactions since the last backup. For more information, see Chapter 10.

  35. D. Although you should have a minimum of two copies of the control file to guard against loss of one of them, Oracle strongly recommends three copies. For more information, see Chapter 10.

  36. B, D. RMAN can make image copies of datafiles or place backups into backup sets. For more information, see Chapter 10.

  37. C. Archived redo log files can be written to as many as ten different destinations, with any combination of local and remote locations. For more information, see Chapter 10.

  38. B. Either dropping a table or inserting the wrong data into a table is considered a user error. For more information, see Chapter 11.

  39. D. Instance recovery is performed automatically when ever the database instance is terminated without a NORMAL or IMMEDIATE shutdown. To force an abnormal termination of the instance, you must use SHUTDOWN ABORT and then you can use STARTUP to initiate automatic instance recovery. STARTUP NOMOUNT and STARTUP MOUNT will not start up an instance unless it is shut down first. STARTUP FORCE does not need a SHUTDOWN IMMEDIATE command first; it first performs a SHUTDOWN ABORT and then attempts a normal STARTUP. For more information, see Chapter 11.

  40. C. STARTUP is not a valid state, but the command used to start the database. For more information, see Chapter 11.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset