ORACLE 体系 - 12(上)（oracle数据库体系）

【十二 - 上】逻辑存储架构

12.1 TABLESPACE(表空间）

12.1.1 类型

①PERMANENT 永久表空间

②UNDO 撤销表空间

③TEMPORARY 临时表空间

12.1.2 管理方式：

段的管理方式和区的管理方式是在建立表空间时确定的，段管理方式有AUTO和MANUAL两种，区管理方式有本地管理和字典管理（已淘汰）两种。

SQL> select tablespace_name,contents ,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN SEGMEN
------------------------------ --------- ---------- ------
SYSTEM PERMANENT DICTIONARY MANUAL
SYSAUX PERMANENT LOCAL AUTO
TEMP TEMPORARY LOCAL MANUAL
USERS PERMANENT LOCAL AUTO
EXAMPLE PERMANENT LOCAL AUTO
UNDO_TBS01 UNDO LOCAL MANUAL
TMP01 TEMPORARY LOCAL MANUAL
TEST PERMANENT DICTIONARY MANUAL

注意两点：

1）如果system表空间是数据字典管理，其他表空间可以是数据字典管理或local管理（默认）。

2）字典管理可以转换成本地管理，但是对于系统表空间，要求执行一些附加步骤，比较麻烦。

SQL>execute dbms_space_admin.tablespace_migragte_to_local('tablespacename');

12.1.3 基本操作

1）建立表空间

SQL> create tablespace a datafile '/u01/oradata/prod/a01.dbf' size 10m;

利用oracle提供的dbms_metadata.get_ddl包看看缺省值都给的是什么？

SQL> set serverout on;
SQL>
declare
aa varchar2(2000);
begin
select dbms_metadata.get_ddl('TABLESPACE','A') into aa FROM dual;
dbms_output.put_line(aa);
end;
/

结果：

CREATE TABLESPACE "A" DATAFILE
'/u01/oradata/prod/a01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE
8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO

PL/SQL 过程已成功完成。

关注最后一行，两个重要信息是：(1)区本地管理且自动分配空间 (2)段自动管理。

dbms_metadata.get_ddl也可以查看表，('TABLE','EMP','SCOTT')替换('TABLESPACE','B')试试。

SQL>

create tablespace b datafile '/u01/oradata/prod/b01.dbf' size 10m
extent management local uniform size 128k
segment space management manual

同上，调dbms_metadata.get_ddl包看Oracle对该语句的ddl操作是：

CREATE TABLESPACE "B" DATAFILE
'/u01/oradata/prod/a01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE
8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 SEGMENT SPACE MANAGEMENT MANUAL、

最后一行信息是：区本地管理且统一分配128K, 段手动管理。如果在建表时使用缺省说明，则该表将服从其表空间的这些定义。

2） 删除表空间

1、表空间的删除和offline

SQL>drop tablespace test including contents and datafiles;

contents包括控制文件和数据字典信息，datafiles是物理数据文件。

数据库OPEN下不能删除的表空间是：

①system

②active undo tablespace

③default temporary tablespace

④default tablespace

数据库OPEN下不能offine的表空间是：

①system

②active undo tablespace

③default temporary tablespace

3）查看表空间大小

SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

4）查看表空间空闲大小

SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
UNDOTBS1 98.4375
SYSAUX 14.625
USERS 48.1875
SYSTEM 1.875
EXAMPLE 31.25

5）查看表空间（数据文件）是否自动扩展

SQL> col file_name for a40;
SQL> select file_name,tablespace_name,bytes/1024/1024 mb,autoextensible from dba_data_files;

6）建立大文件（bigfile）的表空间

①small file：在一个表空间可以建立多个数据文件（默认）

②bigfile ：在一个表空间只能建立一个数据文件 （最大可达32T），简化对数据文件管理。

SQL> create bigfile tablespace big_tbs datafile '/u01/oradata/prod/bigtbs01.dbf' size 100m;

试图在该表空间下增加一个数据文件会报错：

SQL> alter tablespace big_tbs add datafile '/u01/oradata/prod/bigtbs02.dbf' size 100m;

报错：ORA-32771: cannot add file to bigfile tablespace

查看大文件表空间：

SQL> select name,bigfile from v$tablespace;

12.2 SEGMENT(段）

12.2.1 特点：

1）表空间在逻辑上可以对应多个段，物理上可以对应多个数据文件，一个段比较大时可以跨多个数据文件。

2）创建一个表，ORACLE为表创建一个（或多个）段，在一个段中保存该表的所有表数据(表数据不能跨段）。

3）段中至少有一个初始区。当这个段数据增加使得区（extent）不够时，将为这个段分配新的后续区。

4）延迟段参数，deferred_segment_creation 默认是TRUE，当建立表后不立即建立表段以及分配初始区。

12.2.2段管理方式：

1）自动管理方式，简称ASSM(Auto Segment Space Management) 采用位图管理段的存储空间。

原理：简单说就是每个段的段头都有一组位图（5个位图），位图描述每个块的满度，根据满度的不同将每个块登记到相应的位图上，位图自动跟踪每个块的使用空间（动态），5个位图的满度按如下定义：满度100%，75%、50%、25%和0%，比如块大小为8k，你要插入一行是3k的表行，那么oracle就给你在满度50%的位图上找个登记的可插入的块。

ASSM的前提是EXTENT MANAGEMENT LOCAL，在ORACLE 9i 以后，缺省状态为自动管理方式，ASSM废弃pctused属性。

2）手工管理方式，简称MSSM(Manual Segment Space Management) 采用FREELIST(空闲列表）管理段的存储空间

原理：这是传统的方法，现在仍然在使用，涉及三个概念 freelist、pctfree和pctused。

①freelist：空闲列表中登记了可以插入数据的可用块，位置在段头，插入表行数据时首先查找该列表。

②pctfree：用来为一个块保留的空间百分比，以防止在今后的更新操作中增加一列或多列值的长度。达到该值，从freelist清除该块信息。

③pctused：一个块的使用水位的百分比，这个水位将使该块返回到可用列表中去等待更多的插入操作。达到该值，该块信息登录到freelist。

这个参数在ASSM下不使用，ASSM使用位图状态位取代了pctused。

12.2.3 表和段的关系

1）一般来讲，一个单纯的表就分配一个段，但往往表没那么单纯，比如表上经常会有主键约束，那么就会有索引，索引有索引段，还有分区表，每个分区会有独立的段，再有就是Oracle的大对象, 如果你的表里引用blob，clob那么这个表就又被分出多个段来。

测试：

SQL> conn / as sysdba
SQL> grant connect,resource to tim identified by tim;
SQL> conn tim/tim
SQL> select * from user_segments;
SQL> create table t1 (id int);
SQL> select segment_name from user_segments;
SQL> create table t2 (id int constraint pk_t2 primary key, b blob, c clob);
SQL> select segment_name from user_segments;

2）延迟段：顾名思义，创建表的时候并不马上建立相应的段。

Oracle 11g R2又增加了一个新的初始化参数DEFERRED_SEGMENT_CREATION(仅适用未分区的heap table）, 此参数默认TRUE，当create table后并不马上分配segment, 仅当第一个insert语句后才开始分配segment，这对于应用程序的部署可能有些好处。(PPT-II-476-478)，也可以使局部设置改变这一功能(覆盖DEFERRED_SEGMENT_CREATION=TRUE)，在create table语句时加上SEGMENT CREATION子句指定。如：

SQL>create table scott.t1(id int,name char(10)) SEGMENT CREATION IMMEDIATE TABLESPACE TB1;
create table t2(id int,name char(10)) SEGMENT CREATION deferred;

12.3 EXTENT（区）

12.3.1 特点：

区是ORACLE进行存储空间分配的最小单位。一个区是由一系列逻辑上连续的Oracle数据块组成的逻辑存储结构。一个区不可以跨数据文件，段中第一个区叫初始区，随后分配的区叫后续区。

12.3.2 管理方式：

1）字典管理：在数据字典中管理表空间的区空间分配。Oracle 8i以前只有通过uet$和fet$的字典管理。

缺点：某些在字典管理方式下的存储分配有时会产生递归操作，并且容易产生碎片，从而影响了系统的性能，现在已经淘汰了。

2）本地管理：在每个数据文件中使用位图管理空间的分配。表空间中所有区（extent)的分配信息都保存在该表空间对应的数据文件的头部。

优点：速度快，存储空间的分配和回收只是简单地改变数据文件中的位图，而不像字典管理方式还需要修改数据库。无碎片，更易于DBA维护。

12.3.3 表和区的关系：

当建立表的时候建立段，然后自动分配相应的extent（1个或者多个），亦可以手工提前分配extent（用于需大量插入数据的表）

实验：查看段的初始区分配情况

sys:
SQL> create tablespace test datafile '/u01/oradata/prod/test01.dbf' size 10m;
SQL> create table scott.t1 tablespace test as select * from scott.dept;
SQL> col segment_name for a20;
SQL> select segment_name,file_id,extent_id,blocks,block_id,bytes/1024/1024 mb from dba_extents where segment_name='T1';
SQL> Insert into scott.t1 select * from scott.t1;
SQL> commit;

删除掉四分之三数据，看看extent是否收回

SQL> delete scott.t1 where deptno>15;

已删除98304行

SQL> commit;

重新插入一倍的数据，看看extent是否增加

SQL>insert into scott.t1 select * from scott.t1;

已创建32768行。

思考一下上面的步骤说明了什么？

12.3.4 预先分配空间

可以根据需要预先分配一些extent，减少并发分配时可能发生申请区块的争用。

SQL>alter table scott.t1 allocate extent (datafile '/u01/oradata/prod/test01.dbf' size 5m);

注意：预分配的空间一定是在表空间可达到的size范围内回收free extent, 使用deallocate,

SQL> alter table scott.t1 deallocate unused;

注意：只能收回从未使用的extent。

12.4 BLOCK(数据块）

12.4.1 OracleBlock的构成

BLOCK是Oracle进行存储空间IO操作的最小单位。构成上分为block header、free space、data

数据块头部：

①ITL：事务槽，可以有多个ITL以支持并发事务，每当一个事务要更新数据块里的数据时，必须先得到一个ITL槽，然后将当前事务ID，事务所用的undo数据块地址，SCN号，当前事务是否提交等信息写到ITL槽里。

②initrans ：初始化事务槽的个数，表默认1, index 默认为2；

③maxtrans： 最大的事务槽个数 （默认255）

④ROW DIR: 行目录, 指向行片段行起始和结束的偏移量。

使块头增加的可能情况是，row entries增加，增加更多的事务槽（ITL）空间。

12.4.2行链接和行迁移

1）什么是行链接和行迁移

①行链接：指一行存储在多个块中的情况，即行链接是跨越多块的行。

②行迁移：指一个数据行由于update语句导致当前块被重新定位到另一个块（那里有充足的空间）中，但在原始块中会保留一个指针。原始块中的指针是必需的，因为索引的ROWID项仍然指向原始位置。

行迁移是update语句当pctfree空间不足时引起的，它与insert和delete语句无关。

2）如何知道发生了行链接或行迁移

查看dba_tables的AVG_ROW_LEN列和CHAIN_CNT列。当CHAIN_CNT有值时，看AVG_ROW_LEN，它表示行的平均长度（byte），如果AVG_ROW_LEN<块大小，发生的是行迁移，否则可能有行链接。

测试：

SCOTT：
SQL> create table t1 (c1 varchar2(20));
SQL>
begin
for i in 1..1000 loop
insert into t1 values(null);
end loop;
end;
/

先分析一下t1表，确定无行迁移

SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 3 0 5

使用了5个块

SQL> select distinct file#,block# from v$bh a,user_objects b where a.objd=b.object_id and b.object_name='T1' order by 2;

v$bh视图可以显示出t1表一共分配了8个块，具体是那些块。

填充这些空列，再分析t1,有了行迁移

SQL> update t1 set c1='prod is my name';
SQL> commit;
SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 22 865 13

说明1000行中有865行发生了行迁移，使用的块也增加了。

3）怎样确定那些行发生了行迁移

$ sqlplus / as sysdba
SQL> @/u01/oracle/rdbms/admin/utlchain.sql
SQL> analyze table scott.t1 LIST CHAINED ROWS;
SQL> select count(*) from chained_rows;
COUNT(*)
----------
865
SQL> select table_name, HEAD_ROWID from chained_rows where rownum<=3;
TABLE_NAME HEAD_ROWID
------------------------------ ------------------
T1 AAASC4AAEAAAAIfABQ
T1 AAASC4AAEAAAAIfABR
T1 AAASC4AAEAAAAIfABS
SQL> Select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fn,
dbms_rowid.rowid_block_number(rowid) bn, rowid,c1 from scott.t1 where rowid='AAASPhAAEAAAAIdABQ';
FN BN ROWID C1
---------- ---------- ------------------ --------------------
4 541 AAASPhAAEAAAAIdABQ prod is my name
SYS@ prod>drop table chained_rows;

4）解决行迁移有很多方法

可以根据上例chained_rows 表中提供的rowid，将t1表中的那些记录删除，然后在重新插入。

这里使用move解决，简单些：

SQL> alter table t1 move; 使用shrink解决不了行迁移
move表后，再分析t1,行迁移消失。
SQL> analyze table t1 compute statistics;
SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1';
PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS
---------- ---------- ----------- ---------- ----------
10 19 0 6

手工方法解决行迁移

SQL>@/u01/oracle/rdbms/admin/utlchain.sql
SQL>analyze table scott.t1 LIST CHAINED ROWS;
SQL>create table scott.t2 as select * from scott.t1 where rowid in (select HEAD_ROWID from chained_rows);
SQL>delete table scott.t1 where rowid in (select HEAD_ROWID from chained_rows);
SQL>insert into scott.t1 select * from sott.t2;
SQL>drop table scott.t2;

5）行链接实验

SCOTT@ prod>create table t1 (c1 varchar2(4000),c2 varchar2(4000));
SCOTT@ prod>insert into t1 values(lpad('a',4000,'*'),lpad('b',4000,'*'));
SCOTT@ prod>commit;
SCOTT@ prod>analyze table t1 compute statistics;
SCOTT@ prod>select table_name, AVG_ROW_LEN,CHAIN_CNT from user_tables where table_name='T1';
TABLE_NAME AVG_ROW_LEN CHAIN_CNT
------------------------------ ----------- ----------
T1 8015 1
SYS@ prod>create tablespace ttt datafile '/u01/oradata/prod/ttt01.dbf' size 10m blocksize 16k;
SCOTT@ prod>alter table t1 move tablespace ttt;
SCOTT@ prod>analyze table t1 compute statistics;
SCOTT@ prod>select table_name, AVG_ROW_LEN,CHAIN_CNT from user_tables where table_name='T1';
TABLE_NAME AVG_ROW_LEN CHAIN_CNT
------------------------------ ----------- ----------
T1 8009 0

12.4.3表和数据块的关系

1）什么是高水位线？

高水位线（high-water mark，HWM）

在数据库中，如果把表想象成从左到右依次排开的一系列块，高水位线就是曾经包含了数据的最右边的块。原则上HWM只会增大, 即使将表中的数据全部删除，HWM也不会降低。

2）HWM有利有弊

优点：可以使HWM以下的块重复利用

缺点：使用全表扫描时要读取HWM以下的所有block，耗费更多的IO资源。

12.4.4如何降低HWM

多种方法可以降低HWM：

①移动表

②收缩表

③导入导出表

④在线重定义表

1）移动表

move方法, 将表从一个表空间移动到另一个表空间（也可以在本表空间内move)。

语法：alter table t1 move [tablespace users];

优点：可以清除数据块中的碎片,降低高水位线。适用MSSM和ASSM

缺点：

①move需要额外（一倍）的空间。

②move过程中会锁表，其他用户不能在该表上做DML或DDL操作。

③move之后，相关索引都不可用了，表上的索引需要重建。

测试move后索引不可用

SCOTT:
SQL>create table emp1 as select * from emp;
SQL>create index emp1_idx on emp1(ename);
SQL>select table_name,index_name,status from user_indexes where table_name='EMP1';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
EMP1 EMP1_IDX VALID
SQL>alter table emp1 move;
SQL>select table_name,index_name,status from user_indexes where table_name='EMP1';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
EMP1 EMP1_IDX UNUSABLE
SQL>alter index EMP1_IDX rebuild;
SQL>select table_name,index_name,status from user_indexes where table_name='EMP1';
TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
EMP1 EMP1_IDX VALID

2）收缩表

Shrink方法，也叫段重组，表收缩的底层实现的是通过匹配的INSERT和DELETE操作。

语法：alter table t2 shrink space [cascade][compact];

优点：使用位图管理技术 ①降低热块，②更合理的重新利用空闲块。

缺点：①要求段管理是ASSM方式，②表上启用row movement。

它分两个不同的阶段：压缩阶段和降低HWM阶段。(PPT-II-491)

第一阶段：发出alter table t2 shrink space compact命令; 这是压缩阶段。在业务高峰时可以先完成这样步骤

第二阶段：再次alter table t2 shrink space; 因压缩阶段工作大部分已完成，将很快进入降低HWM阶段，DML操作会有短暂的锁等待发生。

测试：

SQL>create table scott.t2 as select * from dba_objects;
scott：
SQL>select max(rownum) from t2;
SQL> analyze table t2 compute statistics;
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table_name='T2';
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 1039 113 68875
Blocks：表示使用过的块，即低于HWM的块数量。
empty_blocks：表示extent分配了，但从未使用过的块，即高于HWM的块数量
两项之和1039+113=1152是这个段分配的块数
SQL> select segment_name,blocks from user_segments where segment_name='T2';
SEGMENT_NAME BLOCKS
-------------------- ----------
T2 1152
删除40000行
SQL>delete t2 where rownum<=40000;
SQL>commit;
SQL> analyze table t2 compute statistics;
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table_name='T2';
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 1039 113 28875
num_rows已经减掉了40000条， 但 blocks 并没有减少， 说明HWM没有下降。
做shrink
SQL>alter table t2 enable row movement; 使能行移动

第一步：压缩阶段

SQL>alter table t2 shrink space compact;
SQL>analyze table t2 compute statistics for table;
SQL> select table_name, blocks, empty_blocks, num_rows from user_tables where table_name='T2'; HWM不会降低。

第二步：降低HWM阶段

SQL> alter table t2 shrink space;
SQL> analyze table t2 compute statistics;
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table_name='T2';
TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T2 426 22 28875

the end ！！！

@jackman 共筑美好！