OCP-1Z0-052-V8.02-23题

23. Note the following structures in your database server:

1: Extents

2: OS Blocks

3: Tablespace

4: Segments

5: Oracle Data Block

Which option has the correct arrangement of these structures from the smallest to the largest?

A.2, 5, 1, 4, 3

B.1, 2, 3, 4, 5

C.5, 2, 1, 3, 4

D.2, 1, 5, 4, 3

Answer: A  

 

如图：Oracle存储模型，逻辑结构在左，物理结构在右。

逻辑结构由大到小

database-->tablespace-->segment-->extent-->oracle data block

 

1、数据库是由多个表空间组成。

sys@TEST0910> desc dba_tablespaces

 

此数据库共有7个表空间。

SQL> select tablespace_name,status,contents,segment_space_management from dba_tablespaces;

 

TABLESPACE_NAME STATUS    CONTENTS  SEGMEN

--------------- --------- --------- ------

SYSTEM          ONLINE    PERMANENT MANUAL

SYSAUX          ONLINE    PERMANENT AUTO

UNDOTBS1        ONLINE    UNDO      MANUAL

TEMP            ONLINE    TEMPORARY MANUAL

USERS           ONLINE    PERMANENT AUTO

EXAMPLE         ONLINE    PERMANENT AUTO

TESTTBS         ONLINE    PERMANENT AUTO

 

7 rows selected.

 

2、每个表空间只属于一个数据库，包含一个或多个数据文件，每个数据文件只属于一个表空间。 

 

sys@TEST0910> desc dba_data_files

 

sys@TEST0910> select file_id,file_name,tablespace_name,bytes/1024/1024 m,blocks from dba_data_files;

 

   FILE_ID FILE_NAME                                                                      TABLESPACE_NAME               M     BLOCKS

---------- --------------------------------------------------                             --------------------                     ---------- ----------

         4 /u01/app/oracle/oradata/test0910/users01.dbf           USERS                                         5        640

         3 /u01/app/oracle/oradata/test0910/undotbs01.dbf     UNDOTBS1                            100      12800

         2 /u01/app/oracle/oradata/test0910/sysaux01.dbf          SYSAUX                                  660      84480

         1 /u01/app/oracle/oradata/test0910/system01.dbf          SYSTEM                                  730      93440

         5 /u01/app/oracle/oradata/test0910/example01.dbf     EXAMPLE                             345.625      44240

         6 /u01/app/oracle/oradata/test0910/testtb.dbf                TESTTBS                              50       6400

 

6 rows selected.

 

3、每个段包括一个或多个extents区

sys@TEST0910> desc dba_segments;

 

sys@TEST0910> select tablespace_name,segment_name,segment_type,extents from dba_segments where owner='SCOTT';

 

TABLESPACE_NAME                SEGMENT_NA SEGMENT_TYPE          EXTENTS

------------------------------     ----------             ------------------                 ----------

USERS                                  PK_EMP             INDEX                               1

USERS                                  PK_DEPT            INDEX                               1

USERS                                  EMP1                   TABLE                               1

USERS                                  SALGRADE           TABLE                               1

USERS                                  EMP                    TABLE                               1

USERS                                  DEPT                   TABLE                               1

 

6 rows selected.

包括:表段、表分区段、索引段、索引分区段、临时段、撤销段、BLOB、CLOB

sys@TEST0910> select distinct segment_type from dba_segments;

 

SEGMENT_TYPE

------------------

LOBINDEX

INDEX PARTITION

TABLE SUBPARTITION

ROLLBACK

TABLE PARTITION

NESTED TABLE

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

TYPE2 UNDO

 

SEGMENT_TYPE

------------------

CLUSTER

 

12 rows selected.

 

4、extent，区，由相邻的数据块的组成。

 

sys@TEST0910> select TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,EXTENT_ID,FILE_ID,BLOCK_ID from dba_extents where owner='SCOTT';

 

TABLESPACE_NAME SEGMENT_NAME                   SEGMENT_TYPE        EXTENT_ID    FILE_ID   BLOCK_ID

--------------- ------------------------------ ------------------ ---------- ---------- ----------

USERS           DEPT                           TABLE                       0          4        128

USERS           PK_DEPT                        INDEX                       0          4        136

USERS           EMP                            TABLE                       0          4        144

USERS           PK_EMP                         INDEX                       0          4        152

USERS           SALGRADE                       TABLE                       0          4        160

USERS           EMP1                           TABLE                       0          4        528

 

6 rows selected.

 

 5、块BLOCK:是数据库中最小的I/O单元,db_block_size

sys@TEST0910> show parameter db_block_size

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     8192