Review Questions

381
5. You create a view based on the EMPLOYEES table using the following SQL.
CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;
You modify the table to add a column named EMP_SSN. What do you need to do to have this
new column appear in the view?
A. Nothing. Since the view definition is selecting all columns, the new column will appear
in the view automatically.
B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
C. Re-create the view using CREATE OR REPLACE VIEW.
D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.
6. Which is a valid status of a constraint created on a view?
A. DISABLE VALIDATE
B. DISABLE NOVALIDATE
C. ENABLE NOVALIDATE
D. All of the above
7. The SALARY column of the EMPLOYEE table is defined as NUMBER(8,2), and the COMMIS-
SION_PCT column is defined as NUMBER(2,2). A view is created with the following code:
CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES;
What is the datatype of the COMMISSION column in the view?
A. NUMBER (8,2)
B. NUMBER (10,2)
C. NUMBER
D. FLOAT
8. Which clause in the SELECT statement is not supported in a view definition subquery?
A. GROUP BY
B. HAVING

WHERE ROWNUM <= 5;
D. SELECT EMP_NAME, SALARY
(SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC)
WHERE ROWNUM = 5;
95127c07.indd 382 2/17/09 12:33:21 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Review Questions

383
10. The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4) PRIMARY KEY
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (6,2)
DEPT_ID VARCHAR2 (2)
A view is defined using the following SQL:
CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = ‘HR’;
Which INSERT statement will succeed through the view?
A. INSERT INTO EMP_IN_DEPT10 VALUES (1000, ‘JOHN’,1500,’HR’);
B. INSERT INTO EMP_IN_DEPT10 VALUES (1001, NULL,1700,’AM’);
C. INSERT INTO EMP_IN_DEPT10 VALUES (1002, ‘BILL’,2500,’AC’);
D. All of the above
11. To be able to modify a join view, the view definition should not contain which of the fol-
lowing in the top-level query? (Choose all that apply.)
A. A DISTINCT operator
B. An ORDER BY clause
C. Aggregate functions such as SUM, AVG, and COUNT

SELECT key_seq.currval FROM dual;
ERROR at line 1:
ORA-08002: sequence KEY_SEQ.CURRVAL is not yet defined
Which of the following statements does the developer need to run to fix this condition?
A. create sequence key_seq;
B. create synonym key_seq;
C. select key_seq.nextval from dual;
D. grant create sequence to public;
16. Bitmapped indexes are best suited to which type of environment?
A. High-cardinality columns
B. Online transaction processing (OLTP) applications
C. Full-table scan access
D. Low- to medium-cardinality columns
17. Which clauses in a SELECT statement can an index be used for? (Choose all that apply.)
A. SELECT
B. FROM
C. WHERE
D. HAVING
95127c07.indd 384 2/17/09 12:33:21 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Review Questions

385
18. You need to generate artificial keys for each row inserted into the PRODUCTS table. You
want the first row to use a sequence value of 1000, and you want to make sure that no
sequence value is skipped. Which of the following statements will meet these requirements?
A. CREATE SEQUENCE product_key2
START WITH 1000
INCREMENT BY 1
NOCACHE;

2. A. You can perform an INSERT, UPDATE, or DELETE operation on the columns involving
only one base table at a time. There are also some restrictions on the DML operations you
perform on a join view.
3. D. Since the view definition includes a DISTINCT clause, only queries are allowed on the
view.
4. B, E. The OR REPLACE option in the CREATE VIEW statement is used to modify the defini-
tion of the view. The FORCE option can be used to create the view with errors. The ALTER
VIEW statement is used to compile a view or to add or modify constraints on the view.
5. C. When you modify the base table, the view becomes invalid. Oracle will recompile the
view the first time it is accessed. Recompiling the view will make it valid, but the new col-
umn will not be available in the view. This is because when you create the view using *,
Oracle expands the column names and stores the column names in the dictionary.
6. B. Since the constraints on the view are not enforced by Oracle, the only valid status of a
constraint can be DISABLE NOVALIDATE. You must specify this status when creating con-
straints on a view.
7. C. When numeric operations are performed using numeric datatypes in the view definition,
the resulting column will be a floating datatype, which is NUMBER without any precision
or scale.
8. D. The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE
clause locks the rows, so it is not allowed.
9. C. You can find the top five salaries using an inline view with the ORDER BY clause. The
Oracle 11g Optimizer understands the top-n rows query. Option B would have been correct
if you had ROWNUM <= 5 in the WHERE clause.
10. D. The view is based on a single table, and the only constraint on the table is the primary
key. Although the view is defined with a WHERE clause, you have not enforced that check
while using DML statements through the WITH CHECK OPTION clause.
11. A, C, E, F. To be able to update a base table using the view, the view definition should not
have a DISTINCT clause, a GROUP BY clause, a START WITH clause, a CONNECT BY clause,
ROWNUM, set operators (UNION, UNION ALL, INTERSECT, or MINUS), or a subquery in the
SELECT clause.

95127c07.indd 387 2/17/09 12:33:22 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
95127c07.indd 388 2/17/09 12:33:22 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PART
II
Oracle
Database 11g:
Administration I
95127c08.indd 389 2/17/09 12:45:14 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
95127c08.indd 390 2/17/09 12:45:15 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter
8
Introducing Oracle
Database 11g
Components and
Architecture
ORACLE DATABASE 11g:
ADMINISTRATION I EXAM OBJECTIVES
COVERED IN THIS CHAPTER:
Exploring the Oracle Database Architecture
Explain the Memory Structures

Describe the Process Structures

Overview of Storage Structures

Preparing the Database Environment

tion website at
/>db_pages.getpage?page_id=41&p_exam_id=1Z0_052
for the most current
exam-objectives listing.
Oracle Database Fundamentals
Databases store data. The data itself is composed of related logical units of information.
The database management system (DBMS) facilitates the storage, modification, and
retrieval of this data. Some early database technologies used flat files or hierarchical file
structures to store application data. Others used networks of connections between sets
of data to store and locate information. The early DBMS architecture mixed the physical
manipulation of data with its logical manipulation. When the location of data changed, the
95127c08.indd 392 2/17/09 12:45:15 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Oracle Database Fundamentals

393
application referencing the data had to be updated. Relational databases brought a revolu-
tionary change to this architecture. Relational DBMS introduced data independence, which
separated the physical model of the data from its logical model. Oracle is a relational DBMS.
All releases of Oracle’s database products have used a relational DBMS model to store
data in the database. This relational model is based on the groundbreaking work of Dr.
Edgar Codd, which was first published in 1970 in his paper “A Relational Model of Data
for Large Shared Data Banks.” IBM Corporation, which was then an early adopter of Dr.
Codd’s model, helped develop the computer language that is used to access all relational
databases today—Structured Query Language (SQL). The great thing about SQL is that
you can use it to easily interact with relational databases without having to write complex
computer programs and without needing to know where or how the data is physically
stored on disk. You saw several SQL statements in the previous chapters.
Relational Databases
The concept of a relational database management system (RDBMS) is that the data con-

7902
7698
7698
7839
7698
7839
7698
HIREDATE
17-DEC -8
20-FEB-8
22-FEB-8
02-APR-8
28-SEP-8
07-MAY-8
08-SEP-8
0800
11600
11250
12975
11250
12850
11500
COMM DEPTNO
20
30
30
20
30
30
30

Chapter 8

Introducing Oracle Database 11g Components and Architecture
The
DEPT
table in the lower part of the figure stores information about departments
in the company. Each department is identified by the department ID. Along with the ID,
the name and location of the department are also stored in the table. The
EMP
table stores
information about the employees in the company. Each employee is identified by a unique
employee ID. This table includes employee information such as hire date, salary, manager,
and so on. The
DEPTNO
column in both tables then provides a relationship between the
tables. A department may have many employees, but an employee can work for only one
department.
Since the user accessing this data doesn’t need to know how or where the row is stored
in the database, there must be a way to uniquely identify the rows in the tables. In our
example, the department is uniquely identified by department number, and an employee is
identified by an employee ID. The column (or set of columns) that uniquely identifies a row
is known as the primary key. According to relational theory, each table in a relational data-
base must have a primary key.
When relating tables together, the primary key of one table is placed in another table.
For example, the primary key of the
DEPT
table is a column in the
EMP
table. In RDBMS
terminology, this is known as a foreign key. A foreign key states that the data value in the

Cluster A cluster is a group of tables that share the same storage blocks.
Constraint A constraint is a stored rule to enforce data integrity.
Sequence A sequence provides a mechanism for the continuous generation of
numbers.
Synonym A synonym is an alias for a database schema object.
Triggers A trigger is a PL/SQL program unit that gets executed when an event
occurs.
Stored function Stored functions are PL/SQL programs that can be used to create
user-defined functions to return a value.
Stored procedure Stored procedures are PL/SQL programs to define a business
process.
Package A package is a collection of procedures, functions, and other program
constructs.
Java Stored Java procedures can be created in Oracle to define business
processes.
Database link Database links are used to communicate between databases to
share data.
You use SQL to create database objects and to interact with application data. In the next
section, I will discuss the tools available to access and administer Oracle 11g database.
Interacting with Oracle 11g
SQL is the language used to interact with the Oracle 11g database. Many tools are available
for the DBA to administer an Oracle 11g database. The common tools are as follows:
SQL*Plus, which is a command-line interface utility

SQL Developer, a GUI tool

Oracle Enterprise Manager Database Control, a GUI tool

TABLE 8.1 Oracle Database 11g Objects (continued)
95127c08.indd 395 2/17/09 12:45:16 PM

95127c08.indd 396 2/17/09 12:45:16 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Oracle Database Fundamentals

397
SQL Developer also includes a migration utility to migrate Microsoft Access and Microsoft
SQL Server databases to Oracle 11g. Figure 8.3 shows the object browser screen of SQL
Developer.
FIGURE 8.3 SQL Developer screen
You can download and learn more about SQL Developer on the OTN
website (
/>developer/index.html
).
Enterprise Manager Database Control
Oracle Enterprise Manager Database Control is a web-based database management tool
that is bundled with the Oracle 11g database. This is a graphical tool specifically designed
to administer the Oracle database. The Enterprise Manager Database Control is used to man-
age a single database, whereas the Enterprise Manager Grid Control can manage multiple
databases and other services and applications, such as OAS, and even non-Oracle applica-
tions at the same time. Figure 8.4 shows the Enterprise Manager Database Control home
screen, where an overview of the database is shown.
95127c08.indd 397 2/17/09 12:45:16 PM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
398

Chapter 8

Introducing Oracle Database 11g Components and Architecture
FIGURE 8.4 Enterprise Manager home screen
For all the database-administration examples in this chapter, you may use either SQL*Plus

Monitoring and tuning database performance

The remainder of this book is dedicated to helping you understand how to perform these
and other important Oracle database-administration tasks. But first, to succeed as an Oracle
DBA, you need to completely understand Oracle’s underlying architecture and its mechanisms.
Understanding the relationship between Oracle’s memory structures, background processes,
and I/O activities is critical before learning how to manage these areas.
The Oracle server architecture can be described in three categories:
User-related processes

Logical memory structures that are collectively called an

Oracle instance
Physical file structures that are collectively called a

database
You will also see how the physical structures map to the logical structures of the data-
base you are familiar with, such as tables and indexes.
Database is a confusing term that is often used to represent different things on different
platforms; the only commonality is that it is something related to storing data. In Oracle,
however, the term database represents the physical files that store data. An instance is com-
posed of the memory structures and background processes. Each database should have at
least one instance associated with it. It is possible for multiple instances to access a single
database; such a configuration is known as Real Application Clusters (RAC). In this book,
however, you’ll concentrate only on single-instance databases because RAC is not part of
the certification exam.
Figure 8.5 shows all the parts of an Oracle instance and database.
Although the architecture in Figure 8.5 may at first seem complex, each of these archi-
tecture components is described in more detail in the following sections, beginning with
the user-related processes, and is actually fairly simple. This figure is an important piece of

Database
Data
Files
Control
Files
Redo Log
Files
Database
Archive Log
Files
Password
Files
Parameter
Files
Logical
Database
Structure
SYSTEM
Tablespace
SYSAUX
Tablespace
Tablespace
2
User Processes
At the user level, two types of processes allow a user to interact with the instance and, ulti-
mately, with the database: the user process and the server process.
Whenever a user runs an application, such as a human-resources or order-taking applica-
tion, Oracle starts a user process to support the user’s connection to the instance. Depending
on the technical architecture of the application, the user process exists either on the user’s
own computer or on the middle-tier application server. The user process then initiates a con-