oracle 9i表分析,我的oracle 9i学习日志（4）--逻辑结构与习题解析

Logical Structure

在oracle database中数据时存储在tablespaces(表空间)里的，一个表空间只能属于一个database，一个表空间可以对应一个或多个物理文件(data file)。

一个oracle server上只有一个database，一个database包含至少一个表空间，一个表空间包含一个或多个段，一段由多个extent组成，extent由许多连续的块(block)组成。

一个segment不能跨越表空间，但一个segment可以属于不同的data file。但一个extent不能跨越data file。

data block是oracle server分配，读写等基本操作的最小存储单元，大小由DB_BLOCK_SIZE这个参数指定，但必须是操作系统block的整数倍，最大尺寸不同的系统不同。

SQL语句的处理过程

处理一个查询：

1.解析：

-查找相同的语句

-检查语法，目标名，权限

-Lock objects used during parse

-创建和存储执行计划

2.绑定：得到一个变量

3.执行：

4.返回：返回结果

处理DML语句，处理DDL语句：

基本过程类似，可分为连接，安全检查，解析，绑定变量，执行，返回结果。

Practice 1: Oracle Architectural Components

1 Which one of the following statements is true?

a An Oracle server is a collection of data consisting of three file types.

b A user establishes a connection with the database by starting an Oracle instance.

c A connection is a communication pathway between the Oracle server and the Oracle instance.

d A session starts when a user is validated by the Oracle server.

答案：d。

解析：a，这是对database files的描述，而不是oracle server。b，一个oracle server只会启动一个instance，user直接与server process打交道而不是instance，一般来说一个connection对应一个server process。c，参考“我的学习笔记(3)”可知，这是对session的描述，而不是connection。d，正确。

2 Which one of the following memory areas is not part of the SGA?

a Database Buffer Cache

b PGA

c Redo Log Buffer

d Shared Pool

答案：b。

3 Which two of the following statements are true about the Shared Pool?

a The Shared Pool consists of the Library Cache, Data Dictionary Cache, Shared  SQL area, Java Pool, and Large Pool.

b The Shared Pool is used to store the most recently executed SQL statements.

c The Shared Pool is used for an object that can be shared globally.

d The Library Cache consists of the Shared SQL and Shared PL/SQL areas.

答案：b，d。

解析：c答案在oracle教材上有这样的话“Because the Shared Pool is used for objects that can be shared globally”，但在这个题目中有歧义，c，d两项最佳答案是d。

4 Which one of the following memory areas is used to cache the data dictionary

information?

a Database Buffer Cache

b PGA

c Redo Log Buffer

d Shared Pool

答案：d。

5 The primary purpose of the Redo Log Buffer is to record all changes to the database

data blocks.

a True

b False

答案：a。

6 The PGA is a memory region that contains data and control information for multiple

server processes or multiple background processes.

a True

b False

答案：b。

解析：不是multiple，是single。一个PGA对应一个进程。

7 Which of the following becomes available when an Oracle instance is started?

a User process

b Server process

c Background processes

答案：c。

8 Identify five mandatory background processes.

答案：DBWn,LGWR,SMON,PMON,CKPT

9 Match the process with its task.

a Database Writer          1 Assists with writing to the data file headers

b Log Writer                     2 Is responsible for instance recovery

c System Monitor            3 Cleans up after failed processes

d Process Monitor          4 Records database changes for recovery purposes

e Checkpoint                   5 Writes dirty buffers to the data files

答案：a-5,b-4,c-2,d-3,e-1。

10 The physical structure of an Oracle database consists of control files, data files, and

online redo log files.

a True

b False

答案：a。

11 Place the following structures in order of hierarchy beginning with database.

a Tablespaces

b Extent

c Segment

d Database

e Block

答案：从小到大顺序：e,b,c,a,d.

12 Identify the components of an Oracle server.

答案：database，instance。

13 Identify the components of an Oracle instance.

答案：内存结构(或SGA)和后台进程

14 Identify three file types that make up an Oracle database.

答案：data files，control files，redo log files。