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