段\区\块

来源：互联网发布：sqlserver触发器语法编辑：程序博客网时间：2024/05/18 13:11

段（segment）

概述

为特定的数据库对象（如表，索引等）分配的一系列数据存储区域。

默认段名与数据库对象名相同。

一个对象一般只对应一个段。

一个段segment只能属于一个表空间，但可以跨多个数据文件。

段分为数据段，索引段，回滚段，临时段等类型。

select segment_name,segment_type,tablespace_name,bytesfrom dba_segments;

【查询系统中所有段】

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES

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

WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION SYSAUX 65536

WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION SYSAUX 65536

WRH$_DB_CACHE_ADVICE TABLE PARTITION SYSAUX 65536

SQL> select segment_type fromdba_segments group bysegment_type;

【查询系统中所有段种类】

SEGMENT_TYPE

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

LOBINDEX

INDEX PARTITION

TABLE PARTITION

NESTED TABLE

ROLLBACK

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

CLUSTER

TYPE2 UNDO

11 rows selected.

Elapsed: 00:00:00.53

SQL> select distinct segment_type from dba_segments;

【查询系统中所有段种类】

SEGMENT_TYPE

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

LOBINDEX

INDEX PARTITION

TABLE PARTITION

NESTED TABLE

ROLLBACK

LOB PARTITION

LOBSEGMENT

INDEX

TABLE

CLUSTER

TYPE2 UNDO

11 rows selected.

段管理方式

MSSM（manual segment space management手动段空间管理）在每个段中以freelist链表方式管理段中空闲数据块（块中空闲区高于pctfree值），是ora9.2以前的数据块管理方式。

ASSM（automatic segment space management自动段空间管理,默认）以位图BMB（bitmap managed segments）方式管理段中的空闲数据块（块中空闲区高于pctfree值）以避免freelist链表竞争，是ora9.2开始新出现的数据块管理方式。

区别：1.注意ASSM与内存管理中ASMM(自动共享内存管理)不同。

2.DMT（dictionary managed tablespace数据字典管理方式） USET 和 FET

/LMT（locally managed tablespace,本地表管理空间）每个表的数据文件的头部加入一个二维表（auotoallocate(16,64k;63 ,1M;120,8M;64M)/uniform(1M)）

和MSSM/ASSM的联系与区别：

两者都在建立表空间时指定。

DMT/LMT用于管理（segment中）空闲的extent.

MSSM/ASSM用于管理（extent中的）空闲的block.

SQL> select tablespace_name,extent_management,allocation_type,segment_space_managementfrom dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIOSEGMEN

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

SYSTEM LOCAL SYSTEM MANUAL

UNDOTBS1 LOCAL SYSTEM MANUAL

SYSAUX LOCAL SYSTEM AUTO

TEMP LOCAL UNIFORM MANUAL

USERS LOCAL SYSTEM AUTO

EXAMPLE LOCAL SYSTEM AUTO

I LOCAL SYSTEM AUTO

TEST LOCAL SYSTEM AUTO

TEST2 LOCAL SYSTEM AUTO

TEMP1 LOCAL UNIFORM MANUAL

TEST4 LOCAL SYSTEM AUTO

TEST5 LOCAL SYSTEM AUTO

T1 LOCAL SYSTEM AUTO

T2 LOCAL UNIFORM AUTO

CWDATA LOCAL SYSTEM AUTO

CWINDEX LOCAL SYSTEM AUTO

TEMP2 LOCAL UNIFORM MANUAL

TEMP3 LOCAL UNIFORM MANUAL

TEMP4 LOCAL UNIFORM MANUAL

19 rows selected.

【查询表空间的空闲extent管理方式（DMT/LMT(autoallocate/uniform)）和空闲block管理方式（MSSM/ASSM）】

SQL> create tablespace tb1 datafile'/u01/tb1.dbf' size 50M segment space managementmanual;

【指定表空间的段空间管理方式为MSSM】

Tablespace created.

SQL> selecttablespace_name,extent_management,allocation_type,segment_space_management fromdba_tablespaces;

TB1 LOCAL SYSTEM MANUAL

数据段

数据段也称为表段，它包含数据并且与表和簇相关。当创建一个表时，系统自动创建一个以该表的名字命名的数据段。

数据段是用于存储数据的段，其存储在数据表空间中。每一个表都有一个数据段，表的数据存储在数据段中，默认情况下段名与表名相同。

SQL> create table xiao tablespace t1 asselect * from dba_objects;

create table xiao tablespace t1 as select *from dba_objects

ERROR at line 1:

ORA-01652: unable to extend temp segment by128 in tablespace T1

SQL>alter tablespace t1 add datafile '/u01/a.dat' size 50M;

【扩展表空间】

Tablespace altered.

Elapsed: 00:00:07.48

SQL> create table xiao tablespace t1 as select* from dba_objects;

Table created.【在指定表空间中建表】

SQL> selectsegment_name,segment_type,tablespace_name,bytes from dba_segments wheresegment_name='XIAO';

【查询随建表而建立的数据段】

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES

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

XIAO TABLE T1 6291456

索引段

索引段用于存储索引，包含了用于提高系统性能的索引。一旦建立索引，系统自动创建一个以该索引的名字命名的索引段。

SQL> selectsegment_name,segment_type,tablespace_name,bytes from dba_segments wheresegment_name='X' and segment_type='INDEX';

【查询索引段不存在】

no rows selected

SQL> create index x on xiao (object_id);

【对表建立索引】

Index created.

SQL> selectsegment_name,segment_type,tablespace_name,bytes from dba_segments where segment_name='X'and segment_type='INDEX';

【再查索引段已存在】

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES

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

X INDEX SYSTEM 983040

SQL> selectindex_name,table_name,tablespace_name from dba_indexes where index_name='X';

【注意与索引本身信息的区别】

INDEX_NAME TABLE_NAME TABLESPACE_NAME

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

X XIAO SYSTEM

回退段

回退段用于存储用户数据修改之前的值，因而可以在特定条件下回退数据。回退段与事务是一对多的关系，一个事务只能使用一个回退段，而一个回退段可存放一个或多个事务的回退数据。当一个事务开始处理时，系统为之分配回滚段存储回滚信息，当所有回退段都已被事务占用时，系统将创建出新的回退段来存储新事务的回滚信息，当表空间中无空间可用于再分配新的回退段时，多个事务将共用一个回退段。

Oracle9i之后，oracle增加了undo表空间，由系统自动管理。故可不再需要考虑对回退段的管理，在系统自动管理模式下，ORACLE会自动完成回退段的创建、删除以及ONLINE/OFFLINE，DBA不能干预这些操作。

SQL> select segment_id,segment_name fromdba_rollback_segs;

【查询Undo表空间中的回退段】

SEGMENT_ID SEGMENT_NAME

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

0 SYSTEM 【系统回滚段】

1 _SYSSMU1$

2 _SYSSMU2$

3 _SYSSMU3$

4 _SYSSMU4$

5 _SYSSMU5$

6 _SYSSMU6$

7 _SYSSMU7$

8 _SYSSMU8$

9 _SYSSMU9$

10_SYSSMU10$

11 rows selected.

SQL> selecta.name,b.xacts,b.writes,b.extents,b.rssize from v$rollname a,v$rollstat b wherea.usn=b.usn;

【查每个回退段名称，每个段当前所包含活动事务数，启动后每个段上所有活动事务在该段上所占字节数，每个段的大小（extents的个数），每个段的大小（字节数）。】

NAME XACTS WRITES EXTENTS RSSIZE

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

SYSTEM 0 7728 6 385024

_SYSSMU1$ 0 336628 3 1171456

_SYSSMU2$ 0 463522 3 1171456

_SYSSMU3$ 0 185212 3 1171456

_SYSSMU4$ 0 110000 3 1171456

_SYSSMU5$ 0 108264 3 1171456

_SYSSMU6$ 0 89612 3 1171456

_SYSSMU7$ 0 182510 3 1171456

_SYSSMU8$ 0 194032 4 253952

_SYSSMU9$ 0 315174 4 253952

_SYSSMU10$ 0 202786 3 1171456

11 rows selected.

SQL> drop table t1;

Table dropped.

Elapsed: 00:00:04.71

SQL> create table t1 as select * fromdba_objects where 1=2;

Table created.

Elapsed: 00:00:00.08

SQL> insert into t1 select * fromdba_objects;

50793 rows created.

SQL> select a.name,b.xacts,b.writes,b.extentsfrom v$rollname a,v$rollstat b where a.usn=b.usn;

NAME XACTS WRITES EXTENTS

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

SYSTEM 0 7728 6

_SYSSMU1$ 0 343316 3

_SYSSMU2$ 0 480452 3

_SYSSMU3$ 0 197400 3

_SYSSMU4$ 0 118040 3

_SYSSMU5$ 1 309656 3

_SYSSMU6$ 0 98656 3

_SYSSMU7$ 0 189976 3

_SYSSMU8$ 0 211206 4

_SYSSMU9$ 0 12295186 27

_SYSSMU10$ 0 234340 3

11 rows selected.

SQL> delete from t1;

50793 rows deleted.

Elapsed: 00:00:15.26

SQL> selecta.name,b.xacts,b.writes,b.extents from v$rollname a,v$rollstat b wherea.usn=b.usn;

NAME XACTS WRITES EXTENTS

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

SYSTEM 0 7728 6

_SYSSMU1$ 0 343316 3

_SYSSMU2$ 0 480452 3

_SYSSMU3$ 0 197400 3

_SYSSMU4$ 0 118040 3

_SYSSMU5$ 1 12086214 14

_SYSSMU6$ 0 98932 3

_SYSSMU7$ 0 190402 3

_SYSSMU8$ 0 211360 4

_SYSSMU9$ 0 12295186 27

_SYSSMU10$ 0 234340 3

11 rows selected.

SQL> select segment_name,extent_id,bytes,status from dba_undo_extents;

SEGMENT_NAME EXTENT_ID BYTES STATUS

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

_SYSSMU1$ 0 65536 EXPIRED

_SYSSMU1$ 1 65536 UNEXPIRED

_SYSSMU1$ 2 1048576 UNEXPIRED

_SYSSMU2$ 0 65536 EXPIRED

_SYSSMU2$ 1 65536 EXPIRED

_SYSSMU2$ 2 1048576 EXPIRED

_SYSSMU3$ 0 65536 EXPIRED

_SYSSMU3$ 1 65536 EXPIRED

_SYSSMU3$ 2 1048576 EXPIRED

_SYSSMU4$ 0 65536 EXPIRED

_SYSSMU4$ 1 65536 EXPIRED

_SYSSMU4$ 2 1048576 EXPIRED

_SYSSMU5$ 0 65536 ACTIVE

_SYSSMU5$ 1 65536 ACTIVE

_SYSSMU5$ 2 1048576 ACTIVE

_SYSSMU5$ 3 1048576 ACTIVE

_SYSSMU5$ 4 1048576 ACTIVE

_SYSSMU5$ 5 1048576 ACTIVE

_SYSSMU5$ 6 1048576 ACTIVE

_SYSSMU5$ 7 1048576 ACTIVE

_SYSSMU5$ 8 1048576 ACTIVE

_SYSSMU5$ 9 1048576 ACTIVE

SEGMENT_NAME EXTENT_ID BYTES STATUS

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

_SYSSMU5$ 10 1048576 ACTIVE

_SYSSMU5$ 11 1048576 ACTIVE

_SYSSMU5$ 12 1048576 ACTIVE

_SYSSMU5$ 13 1048576 ACTIVE

_SYSSMU6$ 0 65536 EXPIRED

_SYSSMU6$ 1 65536 EXPIRED

_SYSSMU6$ 2 1048576 EXPIRED

_SYSSMU7$ 0 65536 EXPIRED

_SYSSMU7$ 1 65536 EXPIRED

_SYSSMU7$ 2 1048576 EXPIRED

_SYSSMU8$ 0 65536 EXPIRED

_SYSSMU8$ 1 65536 EXPIRED

_SYSSMU8$ 2 65536 EXPIRED

_SYSSMU8$ 3 65536 EXPIRED

_SYSSMU9$ 0 65536 UNEXPIRED

_SYSSMU9$ 1 65536 UNEXPIRED

_SYSSMU9$ 2 65536 UNEXPIRED

_SYSSMU9$ 3 65536 UNEXPIRED

_SYSSMU9$ 4 65536 UNEXPIRED

_SYSSMU9$ 5 65536 UNEXPIRED

_SYSSMU9$ 6 65536 UNEXPIRED

_SYSSMU9$ 7 65536 UNEXPIRED

SEGMENT_NAME EXTENT_ID BYTES STATUS

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

_SYSSMU9$ 8 65536 UNEXPIRED

_SYSSMU9$ 9 65536 UNEXPIRED

_SYSSMU9$ 10 65536 UNEXPIRED

_SYSSMU9$ 11 65536 UNEXPIRED

_SYSSMU9$ 12 65536 UNEXPIRED

_SYSSMU9$ 13 65536 UNEXPIRED

_SYSSMU9$ 14 65536 UNEXPIRED

_SYSSMU9$ 15 1048576 UNEXPIRED

_SYSSMU9$ 16 1048576 UNEXPIRED

_SYSSMU9$ 17 1048576 UNEXPIRED

_SYSSMU9$ 18 1048576 UNEXPIRED

_SYSSMU9$ 19 1048576 UNEXPIRED

_SYSSMU9$ 20 1048576 UNEXPIRED

_SYSSMU9$ 21 1048576 UNEXPIRED

_SYSSMU9$ 22 1048576 UNEXPIRED

_SYSSMU9$ 23 1048576 UNEXPIRED

_SYSSMU9$ 24 1048576 UNEXPIRED

_SYSSMU9$ 25 1048576 UNEXPIRED

_SYSSMU9$ 26 65536 UNEXPIRED

_SYSSMU10$ 0 65536 EXPIRED

_SYSSMU10$ 1 65536 EXPIRED

_SYSSMU10$ 2 1048576 EXPIRED

【查回退段的extent分配详情及状态】

Active:有活动事务在此extent上

Unexpired（未过期的，为尽的）:已结束事务，undo信息未达到undo_retention（保留，扣留，记忆）时间限制

Expired（过期的，失效的）：已结束事务，undo信息超过undo_retention时间限制。

SQL> create table xiaoxue.t1 as select *from dba_objects;

Table created.

SQL> alter database datafile'/u01/a.dat' autoextend on maxsize 100M;

Database altered.

SQL>alter tablespace CWdata add datafile '/u01/e.dat' size 50M;

Tablespacealtered.

[oracle@desktop241 ~]$ sqlplusxiaoxue/123456

SQL> insert into t1 select * from t1;

50794 rows created.

Elapsed: 00:00:07.71

SQL> insert into t1 select * from t1;

101588 rows created.

Elapsed: 00:00:07.08

SQL> insert into t1 select * from t1;

203176 rows created.

SQL> selecta.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction cwhere a.saddr=c.ses_addr and b.usn=c.xidusn;

【查DB中所有活动（未提交或回退）事务所占用的回退段情况,sys】

USERNAME NAME USED_UBLK

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

XIAOXUE _SYSSMU9$ 166

SQL> selecta.name,b.xacts,b.writes,b.extents from v$rollname a,v$rollstat b wherea.usn=b.usn;[]

NAME XACTS WRITES EXTENTS

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

SYSTEM 0 7728 6

_SYSSMU1$ 0 447038 3

_SYSSMU2$ 0 519708 3

_SYSSMU3$ 0 274628 3

_SYSSMU4$ 0 236260 3

_SYSSMU5$ 0 12095186 14

_SYSSMU6$ 0 171158 3

_SYSSMU7$ 0 213268 3

_SYSSMU8$ 0 233848 4

_SYSSMU9$ 1 13797338 6

_SYSSMU10$ 0 357506 3

11 rows selected.

SQL> rollback;

Rollback complete.

SQL> selecta.name,b.xacts,b.writes,b.extents from v$rollname a,v$rollstat b wherea.usn=b.usn;

NAME XACTS WRITES EXTENTS

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

SYSTEM 0 7728 6

_SYSSMU1$ 0 447410 3

_SYSSMU2$ 0 519862 3

_SYSSMU3$ 0 275584 3

_SYSSMU4$ 0 236590 3

_SYSSMU5$ 0 12095962 14

_SYSSMU6$ 0 172834 3

_SYSSMU7$ 0 213804 3

_SYSSMU8$ 0 235596 4

_SYSSMU9$ 0 13797338 6

_SYSSMU10$ 0 357782 3

11 rows selected.

【回退上页事务后再查回退段的使用情况】

SQL> selectsegment_name,extent_id,bytes,status from dba_undo_extents;

SEGMENT_NAME EXTENT_ID BYTES STATUS

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

_SYSSMU1$ 0 65536 EXPIRED

_SYSSMU1$ 1 65536 EXPIRED

_SYSSMU1$ 2 1048576 EXPIRED

_SYSSMU2$ 0 65536 EXPIRED

事务可以使用回滚段的同一个区，但每个回滚段的块只能包含一个事务的信息。

例如（两个事务使用同一个回滚段，该回滚段由四个区）：

1. 事务在进行中，他们正在使用回滚段的第三个区；

2. 当第三个区满后，事务将写到第四个区，当事务开始写到一新区时，称翻转WRAP;

3. 当第四个区用满时，若第一个区是空闲或非活动的，即该区中所有事务均已完成，没有未提交或回退事务，则事务将循环再使用第一个区。

回滚段的扩张（EXTEND）

在当前区的所有块用完而事务还需要更多还需要更多的回滚空间时，将使用回滚的下一个区，当最后一个区用完，将循环重新使用第一个区。

回滚段使用下一个区的前提是下一个区没有活动事务，若下一个区正在使用即存在活动事务时，则需进行回滚段扩张，事务将：

1. 从undo表空间中为回滚段分配一个新的空闲区，或从其他回滚段中获取一个expire状态的区，插入到当前区和下一个区之间。

2. 若Undo空间已无新空闲区，或其他回滚段中也无exipre状态区，则尝试扩张undo空间；

3. 若表空间autoextensiable自动扩张属性被禁止，则尝试从其他回滚段中获取unexipre状态（未超过retention 期限）的区（因此可能导致之后发生01555错））

4. 若上面所有扩张方法均失败，则报错空间不足。

为确保其他回滚段中unexpired状态的区不被用掉，避免01555错误，可设置参数guarantee,则extent将一定保留到超过retention时间，则回滚段扩展时将跳过第3步。

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SQL> select segment_name,owner,status from dba_rollback_segs;

【查询数据库自动管理回退信息时在undo表空间中使用的回退段】

SEGMENT_NAME OWNER STATUS

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

SYSTEM SYS ONLINE

_SYSSMU1$ PUBLIC ONLINE

_SYSSMU2$ PUBLIC ONLINE

_SYSSMU3$ PUBLIC ONLINE

_SYSSMU4$ PUBLIC ONLINE

_SYSSMU5$ PUBLIC ONLINE

_SYSSMU6$ PUBLIC ONLINE

_SYSSMU7$ PUBLIC ONLINE

_SYSSMU8$ PUBLIC ONLINE

_SYSSMU9$ PUBLIC ONLINE

_SYSSMU10$ PUBLIC ONLINE

11 rows selected.

SQL> create table a as select * from dba_objects;

Table created.

Elapsed: 00:00:03.06

SQL> create table b as select * from dba_objects;

Table created.

SQL> delete from a;

50794 rows deleted.

Elapsed: 00:00:19.61

SQL> select distinct sid from v$mystat;

【建两个测试表，发起一个delete操作耗费回滚段空间，并查找出任务号】

SID

----------

158

[oracle@desktop241 ~]$ sqlplus / as sysdba

【另开一个会话并发起另一个update操作耗费回滚段空间，并查找出任务号】

SQL> update b set object_id=0;

50795 rows updated.

Elapsed: 00:00:30.18

SQL> select distinct sid from v$mystat;

SID

----------

139

【会话号】

SQL> select r.name,s.serial#,s.sid,s.username,t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program,1,40)from sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r where t.addr =s.taddr and t.xidusn = r.usn and s.sid = 158;

NAME SERIAL# SID USERNAME STATUS CR_GET PHY_IO USED_UBLK NOU SUBSTR(S.PROGRAM,1,40)

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

_SYSSMU8$ 7 158 SYS ACTIVE 879 448 1466 NO sqlplus@desktop241.example.com (TNS V1-V

Elapsed: 00:00:00.09

NAME SERIAL# SID USERNAME STATUS CR_GET PHY_IO USED_UBLK NOU SUBSTR(S.PROGRAM,1,40)

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

_SYSSMU10$ 76 139 SYS ACTIVE 1544 3901030 NO sqlplus@desktop241.example.com (TNS V1-V

SQL> selectr.name,s.serial#,s.sid,s.username,t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program,1,40)from sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r where t.addr =s.taddr and t.xidusn = r.usn and s.sid = &sid;

Enter value for sid: 139

回滚表空间的回缩（SHRINK）

Undo表空间被系统自动重复使用，当有大事务发生时，系统将自动扩充undo表空间，但不会自动进行回缩，故需手工回缩undo表空间。

SQL> create undo tablespace undotbs02 datafile'/u01/app/oracle/oradata/ora10/undotbs02.dbf' size 50M;

【新建回滚表空间】

Tablespace created.

Elapsed: 00:00:05.12

SQL> alter system set undo_tablespace=undotbs02;

【设置新的回滚表空间】

System altered.

Elapsed: 00:00:00.27

SQL> show parameter undo_tablespace;

【查询当前使用的undo表空间名】

NAME TYPE VALUE

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

undo_tablespace string UNDOTBS02

SQL> select segment_name,owner,status from dba_rollback_segs;

【查询数据库自动管理回退信息时在undo表空间中使用的回退段。可见原测试所占回退段仍online，提交或回退测试语句不久后将offline,才能删除原undo空间，若原undo空间中还含有unexpire区，则可能导致之后发生01555错，故应等待retention时间长度后再删原undo空间】

SEGMENT_NAME OWNER STATUS

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

SYSTEM SYS ONLINE

_SYSSMU1$ PUBLIC OFFLINE

_SYSSMU2$ PUBLIC OFFLINE

_SYSSMU3$ PUBLIC OFFLINE

_SYSSMU4$ PUBLIC OFFLINE

_SYSSMU5$ PUBLIC OFFLINE

_SYSSMU6$ PUBLIC OFFLINE

_SYSSMU7$ PUBLIC OFFLINE

_SYSSMU8$ PUBLIC OFFLINE

_SYSSMU9$ PUBLIC OFFLINE

_SYSSMU10$ PUBLIC ONLINE

_SYSSMU11$ PUBLIC ONLINE

_SYSSMU12$ PUBLIC ONLINE

_SYSSMU13$ PUBLIC ONLINE

_SYSSMU14$ PUBLIC ONLINE

_SYSSMU15$ PUBLIC ONLINE

_SYSSMU16$ PUBLIC ONLINE

_SYSSMU17$ PUBLIC ONLINE

_SYSSMU18$ PUBLIC ONLINE

_SYSSMU19$ PUBLIC ONLINE

_SYSSMU20$ PUBLIC ONLINE

21 rows selected.

临时段

当一个SQL语句需要临时工作区时，由oracle将临时表空间创建为临时段。一旦语句执行完毕，临时表空间即临时段的空间便退回给系统。

临时段用于order by 语句的排序以及一些汇总。

SQL> alter database default temporarytablespace temp;

Database altered.

Elapsed: 00:00:00.23

SQL> selectsegment_file,total_extents,total_blocks,used_extents,used_blocks,free_extentsfrom v$sort_segment;

【查临时表空间（临时段的总区、块数，已用区、块数及空闲区、块数】

SEGMENT_FILE TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS

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

0 0 0 0 0 0

SQL> selectusername,segfile#,extents,blocks from v$sort_usage;

【查当前使用临时表空间（临时段）中的用户，及所占区、块数】

no rows selected

SQL> conn xiaoxue/123456

SQL> select a.table_name,b.table_namefrom dict a,dict b order by a.table_name;

SQL> conn scott/tiger

SQL> select a.table_name,b.table_namefrom dict a,dict b order by b.table_name;

SQL> alter database default temporarytablespace temp2;

Database altered.

Elapsed: 00:00:00.13

SQL> selectusername,segfile#,extents,blocks from v$sort_usage;

USERNAME SEGFILE# EXTENTS BLOCKS

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

SCOTT 202 20 2560

XIAOXUE 202 21 2688

Elapsed: 00:00:00.16

SQL> selectsegment_file,total_extents,total_blocks,used_extents,used_blocks,free_extentsfrom v$sort_segment;

SEGMENT_FILE TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTSUSED_BLOCKS FREE_EXTENTS

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

0 0 0 0 0 0

0 41 5248 41 5248 0

0 0 0 0 0 0

SQL> select segment_file,total_extents,total_blocks,used_extents,used_blocks,free_extents,free_blocksfrom v$sort_segment;

SEGMENT_FILE TOTAL_EXTENTS TOTAL_BLOCKSUSED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS

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

0 0 0 0 0 0 0

0 41 5248 41 5248 0 0

0 0 0 0 0 0 0

SQL> create temporary tablespacetempp tempfile '/u01/temp01.dat' size10M;

Tablespace created.

Elapsed: 00:00:02.56

SQL> alter database default temporarytablespace tempp;

Database altered.

$Sqlplus / as sysdba

SQL> create temporary tablespacetempp tempfile '/u01/temp01.dat' size10M;

Tablespace created.

Elapsed: 00:00:02.56

SQL> alter database default temporarytablespace tempp;

Database altered.

Elapsed: 00:00:00.07

SQL> alter database tempfile'/u01/temp01.dat' autoextend on;

Database altered.

SQL> select a.table_name,b.table_namefrom dict a,dict b order by a.table_name;

SQL> conn / as sysdba

Connected.

SQL> selecttotal_extents,total_blocks,used_extents,used_blocks,free_extents,free_blocksfrom v$sort_segment;

TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTSUSED_BLOCKS FREE_EXTENTS FREE_BLOCKS

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

0 0 0 0 0 0

41 5248 0 0 41 5248

0 0 0 0 0 0

9 1152 0 0 9 1152

SQL> select total_extents,total_blocks,used_extents,used_blocks,free_extents,free_blocksfrom v$sort_segment;

TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTSUSED_BLOCKS FREE_EXTENTS FREE_BLOCKS

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

0 0 0 0 0 0

41 5248 0 0 41 5248

0 0 0 0 0 0

63 8064 63 8064 0 0

SQL> selecttotal_extents,total_blocks,used_extents,used_blocks,free_extents,free_blocksfrom v$sort_segment;

TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTSUSED_BLOCKS FREE_EXTENTS FREE_BLOCKS

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

0 0 0 0 0 0

41 5248 0 0 41 5248

0 0 0 0 0 0

71 9088 71 9088 0 0

SQL> selecttotal_extents,total_blocks,used_extents,used_blocks,free_extents,free_blocksfrom v$sort_segment;

TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTSUSED_BLOCKS FREE_EXTENTS FREE_BLOCKS

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

0 0 0 0 0 0

41 5248 0 0 41 5248

0 0 0 0 0 0

159 20352 159 20352 0 0

区（extent）

概述

又称范围，是一组表空间内连续分配的相邻数据块。

是数据库分配磁盘空间的最小逻辑单位，也是表中数据增长的基本单位。

一个oracle对象包含至少一个数据区。

区的storage分配参数：

Initial: 段建立时被分配的第一个区（第一块空间）的大小；

Next：段被分配的第二个区的大小；

Minextents: 段被分配的区个数下限；

Maxextents:段被分配的区个数上限；

Pctincrease:从第3个分区开始，段被分配的新区比以前一个分配的分区扩大的百分比；

Autoallocate管理方式下，表空间的extent分配原则是：initial,min,max自动取默认值（分别为64k,1,2^31），next，pct参数无效；表空间中建表时，extent分配情况按表空间所定参数进行，并以下列规则进行extent扩充，而建表时指定上述各项storage参数无效。

Next,pct 参数无效，所有空间分配按64K的整数倍大小进行分配；

第0—15 个分区，每个分区大小64k,合计1M;

第16—79个分区，每个分区大小1M，合计63M；

以上两项大小合计64M；

第80—199个分区，每个分区大小8M，合计960M,

以上三项大小合计1024M；

第200个分区以上，每个分区大小是64M。

Oracle10.2.0.2分区分配规则：

第0—127个分区，每个分区大小8M，合计1G；

第128个分区以上，每个分区64M。

SQL> create tablespace tb2 datafile'tb2.dbf' size 50M;

【建表空间默认使用autoallocate管理方式】

Tablespace created.

Elapsed: 00:00:03.94

SQL> selectsubstr(tablespace_name,1,8),initial_extent,next_extent,min_extents,max_extents,pct_increasefrom dba_tablespaces;

【查看表空间分区情况】

SUBSTR(TABLESPAC INITIAL_EXTENT NEXT_EXTENTMIN_EXTENTS MAX_EXTENTSPCT_INCREASE

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

SYSTEM 65536 1 2147483645

UNDOTBS1 65536 1 2147483645

SYSAUX 65536 1 2147483645

TEMP 1048576 1048576 1 0

USERS 65536 1 2147483645

EXAMPLE 65536 1 2147483645

I 65536 1 2147483645

TEST 65536 1 2147483645

TEST2 65536 1 2147483645

TEMP1 1048576 1048576 1 0

TEST4 65536 1 2147483645

TEST5 65536 1 2147483645

T1 65536 1 2147483645

T2 2097152 2097152 1 2147483645 0

CWDATA 65536 1 2147483645

CWINDEX 65536 1 2147483645

TEMP2 1048576 1048576 1 0

TEMP3 1048576 1048576 1 0

TEMP4 1048576 1048576 1 0

TB1 65536 1 2147483645

UNDOTBS0 65536 1 2147483645

TB2 65536 1 2147483645

22 rows selected.

SQL> create table t2 tablespace tb2storage(initial 50k next 80k minextents 3 maxextents 9 pctincrease 40) asselect * from dba_objects where 1=2;

【建表时指定分区参数】

Table created.

SQL> selectsubstr(table_name,1,8),initial_extent,next_extent,min_extents,max_extents,pct_increasefrom dba_tables where table_name='T2';

【查表分区参数，可见所有指定参数均失效】

SUBSTR(TABLE_NAM INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTSMAX_EXTENTS PCT_INCREASE

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

T2 253952 81920 1 2147483645

【50+80+80*（40%+1）=242k】

SQL> selectextent_id,substr(segment_name,1,8),tablespace_name,bytes from dba_extents wheresegment_name='T2';

【查询指定表的实际分区情况，可见首个分区分配为64K】

EXTENT_ID SUBSTR(SEGMENT_N TABLESPACE_NAME BYTES

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

0 T2 TB2 65536

1 T2 TB2 65536

2 T2 TB2 65536

3 T2 TB2 65536

SQL> insert into t2 select * fromdba_objects;

【扩充表后再用上一条命令查表的实际分区情况，可见扩充分区分配满足上页规则】

50795 rows created.

SQL> selectextent_id,substr(segment_name,1,8),tablespace_name,bytes from dba_extents wheresegment_name='T2';

EXTENT_ID SUBSTR(SEGMENT_N TABLESPACE_NAME BYTES

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

0 T2 TB2 65536

1 T2 TB2 65536

2 T2 TB2 65536

3 T2 TB2 65536

4 T2 TB2 65536

5 T2 TB2 65536

6 T2 TB2 65536

7 T2 TB2 65536

8 T2 TB2 65536

9 T2 TB2 65536

10T2 TB2 65536

11T2 TB2 65536

12T2 TB2 65536

13T2 TB2 65536

14T2 TB2 65536

15T2 TB2 65536

16T2 TB2 1048576

17T2 TB2 1048576

18T2 TB2 1048576

19T2 TB2 1048576

20T2 TB2 1048576

21 rows selected.

Uniform管理方式下，表空间的extent分配原则是：initial,next值固定为uniform指定值，min,max,pct参数自动设置默认值（分别为1,2^31，0）；表空间中新建表时，extent分配情况按表空间所定参数进行，并以固定不变的uniform值进行extent扩充，而建表时指定上述各项storage参数无效。

SQL> create tablespace tb1 datafile'tb1.dbf' size 50Muniform size 1500k;

【建表空间指定Uniform分区大小】

Tablespace created.

SQL> select substr(tablespace_name,1,8),initial_extent,next_extent,min_extents,max_extents,pct_increasefrom dba_tablespaces;

【查表空间分区分配情况】

SUBSTR(TABLESPAC INITIAL_EXTENT NEXT_EXTENTMIN_EXTENTS MAX_EXTENTS PCT_INCREASE

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

SYSTEM 65536 1 2147483645

UNDOTBS1 65536 1 2147483645

SYSAUX 65536 1 2147483645

TEMP 1048576 1048576 1 0

USERS 65536 1 2147483645

EXAMPLE 65536 1 2147483645

I 65536 1 2147483645

TEST 65536 1 2147483645

TEST2 65536 1 2147483645

TEMP1 1048576 1048576 1 0

TEST4 65536 1 2147483645

TEST5 65536 1 2147483645

T1 65536 1 2147483645

T2 2097152 2097152 1 2147483645 0

CWDATA 65536 1 2147483645

CWINDEX 65536 1 2147483645

TEMP2 1048576 1048576 1 0

TEMP3 1048576 1048576 1 0

TEMP4 1048576 1048576 1 0

TB1 1540096 1540096 1 2147483645 0

UNDOTBS0 65536 1 2147483645

TB2 65536 1 2147483645

22 rows selected.

SQL> create table t1 tablespace tb1storage (initial 50k next 80k minextents 3 maxextents 9 pctincrease 40) asselect * from dba_objects where 1=2;

【建表时指定分区参数】

Table created.

SQL> select substr(table_name,1,8),initial_extent,next_extent,min_extents,max_extents,pct_increasefrom dba_tables where table_name='T1';

【查表分区参数，可见所有指定参数均失效】

SUBSTR(TABLE_NAM INITIAL_EXTENT NEXT_EXTENTMIN_EXTENTS MAX_EXTENTS PCT_INCREASE

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

T1 65536 1048576 1 2147483645

T1 253952 81920 1 2147483645 0

SQL> selectextent_id,substr(segment_name,1,8),tablespace_name,bytes from dba_extents wheresegment_name='T1';

【查询指定表的实际分区情况，可见首个分区分配为uniform指定值】

EXTENT_ID SUBSTR(SEGMENT_N TABLESPACE_NAME BYTES

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

0 T1 TB1 1540096

SQL> insert into t1 select * fromdba_objects;

【扩充表后再用上一条命令查表的实际分区情况，可见扩充分区分配也均为uniform值】

50795 rows created.

Elapsed: 00:00:01.93

SQL> selectextent_id,substr(segment_name,1,8),tablespace_name,bytes from dba_extents wheresegment_name='T1';

EXTENT_ID SUBSTR(SEGMENT_N TABLESPACE_NAME BYTES

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

0 T1 TB1 1540096

1 T1 TB1 1540096

2 T1 TB1 1540096

3 T1 TB1 1540096

块（block）

概述

是一个或一组连续的OS块（512bytes），大小是OS块的整数倍。

是数据库中粒度最小的数据组织单位与管理单位

是数据文件磁盘存储空间单位，也是数据库I/O的最小单位

数据块大小由DB_BLOCK_SIZE参数决定，默认8K

9i以前只能设定一种块大小，9i/10g中不同表空间可指定不同块大小。

块结构

块头：块概要信息，包括块地址，段类型等

表目录：在该块中存储了数据行的表的表信息

行目录：存储在该块中的数据行的行信息

空闲区：用于插入新行和需要额外空间的行更新

行数据：存储在该块中的数据行的具体内容

块参数（pctfree,pctused）

Pctfree(默认值10)

当向一个空数据块插入的数据量达到该数据块大小1-pctfree时，即停止对该块进行插入（剩余空间留给update更新使用），而再分配另一新数据块进行插入，并将该块从空闲块列表freelist中移出。

经常进行update的表应将pctfree调高，则更新时可尽量保证更新数据仍位于原数据块中，避免发生行迁移或行链接。因为update操作会消耗掉空闲空间，当一个块中的空闲空间因为太小而不够update操作消耗的话，系统就会增加新块，从而形成记录链接或行迁移映响表的I/O功能。

Pctused（默认值40）

当数据块内数据被删除到pctused所指定的值时，即将该块移入空闲列表freelist中，使其可以接受新数据的插入。

经常进行delete的表应将pctused调高，以便能有效利用空间，因为一个pctused值较高的数据块，其中能存放的数据将更多，该参数和pctfree配合使用，pctused越大越有利于insert,pctfree越大越有利于update,两参数加起来不能超过100.

行迁移：一行记录初始插入时可以存储在一个块中，但因后来更新导致行长增加，而块的自由空间已经完全满了，此时产生行迁移，将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的新的位置。

行链接：当第一次插入数据时一个块就不能存下一行记录的情况下，系统将使用链接在多个块中存储该记录。

SQL> create tablespace tb2 datafile't.dbf' size 10M;

Tablespace created.

SQL> create table t3 (x int,yvarchar2(200)) pctfree 0;

Table created.

SQL> create table t4 (x int,yvarchar2(200)) pctfree 20;

Table created.

SQL> begin

2 for I in 1..4000

3 loop

4 insert into t3 values(I,'aaaaaaaaaa');

5 insert into t4 values(I,'aaaaaaaaaa');

6 end loop;

7 commit;

8 end;

9 /

PL/SQL procedure successfully completed

SQL> select extent_id,blocks,block_idfrom dba_extents wheresegment_name='T3';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 63537

1 8 64041

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='T4';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 63569

1 8 63897

SQL> analyze table t3 computestatistics;

Table analyzed.

SQL> analyze table t4 computestatistics;

Table analyzed.

SQL> select chain_cnt from dba_tableswhere table_name='T3';

CHAIN_CNT

---------

SQL> select chain_cnt from dba_tableswhere table_name='T4';

CHAIN_CNT

---------

SQL> update t3 set y='aaaaaaaaaaa';

4000 rows updated.

SQL> update t4 set y='aaaaaaaaaaa';

4000 rows updated.

SQL> commit;

Commit complete.

SQL> analyze table t3 computestatistics;

Table analyzed.

SQL> analyze table t4 computestatistics;

Table analyzed.

SQL> select chain_cnt from dba_tableswhere table_name='T3';【行链接信息】

CHAIN_CNT

---------

402

SQL> select chain_cnt from dba_tableswhere table_name='T4';

CHAIN_CNT

---------

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='T3';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 63537

1 8 64041

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='T4';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 63569

1 8 63897

SQL> selecttablespace_name,segment_space_management from dba_tablespaces;

【查询表空间的段空间管理方式，ASSM方式下建立的表将忽略pct_used参数】

TABLESPACE_NAME SEGMEN

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

SYSTEM MANUAL

UNDOTBS1 MANUAL

SYSAUX AUTO

TEMP MANUAL

USERS AUTO

EXAMPLE AUTO

I AUTO

TEST AUTO

TEST2 AUTO

TEMP1 MANUAL

TEST4 AUTO

TEST5 AUTO

T1 AUTO

T2 AUTO

CWDATA AUTO

CWINDEX AUTO

TEMP2 MANUAL

TEMP3 MANUAL

TEMP4 MANUAL

TB1 AUTO

UNDOTBS02 MANUAL

TB2 AUTO

SQL> create table test1 (id char(2));

【使用系统默认pctfree/pctused参数】

Table created.

SQL> create table test2 (id char(2))pctfree 20 pctused 50 tablespace system;

【在system表空间（MSSM段空间管理方式）中指定表的pctfree/pctused值】

Table created.

SQL> create table test3 (id char(2))pctfree 20 pctused 50 tablespace users;

【在users表空间（ASSM段空间管理方式）中指定表的pctfree/pctused值】

Table created.

SQL> select table_name,pct_free,pct_usedfrom user_tables where table_name in ('TEST1','TEST2','TEST3');

【查询表的pct_free 和pctused值】

TABLE_NAME PCT_FREE PCT_USED

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

TEST1 10 40

TEST2 20 50

TEST3 20

Free list & free group

在segment中，若同一时刻发生多个Insert,将引起free list争用，故一个segment中一般存在多个free list。Free list 的多少决定了同一时刻可以在同一segment中进行insert操作的数量，多个free list 又可归属于不同的free list group ,free list group用于进一步将一个段头块中的多个freelist分散到多个块中去，进一步减少段头块的访问竞争。

SQL> create table t5 (a int,b char(6));

Table created.

SQL> selectsegment_name,freelists,freelist_groups from dba_segments where segment_name='T5';

SEGMENT_NAME FREELISTS FREELIST_GROUPS

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

T5 1

SQL> alter table t5 storage (freelists5);

Table altered.

SQL> select segment_name,freelists,freelist_groupsfrom dba_segments where segment_name='T5';

SEGMENT_NAME FREELISTS FREELIST_GROUPS

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

T5 5 1

SQL> create table t6(a int,b char(16)) storage (freelists 4 freelist groups2);

Table created.

SQL> selectsegment_name,freelists,freelist_groups from dba_segments where segment_name='T6';

SEGMENT_NAME FREELISTS FREELIST_GROUPS

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

T6 4 2

当freelist group=1时，freelist都存储在segment header(第一个块)内，真正数据的存储就从第2块开始。当freelist group >1时，freelist从segment header内分离出来，存储在单独的块里面，每个freelist group占一个block，即真正数据的存储就从1（for segment header）+n(free list group数量)块之后的块开始。

Free list & HWM(high water mark)

一个表若预分配100万个block(如果使用较大的initial extent 参数)，则oracle不会讲全部100万个block都放到free list链表上，否则维护量太大，而是使用HWM进行解决。

HWM是一个标记，用来标记segment中有多少block是“已被用过”的，有多少block是“从未被用过”的。只要一block被用过，其就位于HWM以下了，即使块中数据全删掉，该块也仍位于HWM以下，即HWM只升不降。该块也仍位于HWM以下，即HWM只升不降。

HWM的好处是减小了freelist上，即Oracle总使自己用过的空闲块进行Insert,避免了空间浪费同时提高了free list的性能和可管理性；同时在需要扫描整个segment中数据块时，因HWM存在，也只需扫描HWM以下的块，从而提高了性能。

SQL> create table t7 (a int,b char(10));

Table created.

SQL> analyze table t7 computestatistics;

Table analyzed.

SQL> select blocks,empty_blocks from dba_tables where table_name='T7';

BLOCKS EMPTY_BLOCKS

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

0 7

SQL> selectextent_id,block_id,blocks from dba_extents where segment_name='T7';

EXTENT_IDBLOCK_ID BLOCKS

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

0 63601 8

SQL> selectsegment_name,header_file,header_block,blocks fromdba_segments wheresegment_name='T7';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

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

T7 1 63601 8

SQL> altersystem dump datafile 1 block 63601;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_19189.trc

#blocks in seg. hdr's freelists: 0

#blocks below: 0

SQL> begin

2 forI in 1..2000

3 loop

4 insert into t7 values (I,'aaaaaaaaaa');

5 endloop;

6 commit;

7 end;

8 /

PL/SQL proceduresuccessfully completed.

SQL> analyzetable t7 compute statistics;

Table analyzed.

SQL> selectblocks,empty_blocks from dba_tables where table_name='T7';

BLOCKS EMPTY_BLOCKS

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

7 0

SQL> selectextent_id,block_id,blocks from dba_extents where segment_name='T7';

EXTENT_IDBLOCK_ID BLOCKS

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

0 63601 8

SQL> selectsegment_name,header_file,header_block,blocks from dba_segments wheresegment_name='T7';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

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

T7 1 63601 8

SQL> altersystem dump datafile 1 block 63601;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_19189.trc

Highwater:: 0x0040f879 ext#: 0 blk#: 7 ext size: 7

#blocks in seg. hdr's freelists: 2

#blocks below: 7

mapblk 0x00000000 offset: 0

SQL> begin

2 forI in 2001..4000

3 loop

4 insert into t7 values (I,'aaaaaaaaaa');

5 endloop;

6 commit;

7 end;

8 /

PL/SQL proceduresuccessfully completed.

SQL> analyzetable t7 compute statistics;

Table analyzed.

SQL> selectblocks,empty_blocks from dba_tables where table_name='T7';

BLOCKSEMPTY_BLOCKS

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

12 3

SQL> selectextent_id,block_id,blocks from dba_extents where segment_name='T7';

EXTENT_IDBLOCK_ID BLOCKS

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

0 63601 8

1 64049 8

SQL> selectsegment_name,header_file,header_block,blocks from dba_segments wheresegment_name='T7';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

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

T7 1 63601 16

SQL> altersystem dump datafile 1 block 63601;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_19189.trc

Highwater:: 0x0040fa36 ext#: 1 blk#: 5 ext size: 8

#blocks in seg. hdr's freelists: 1

#blocks below: 12

mapblk 0x00000000 offset: 1

SQL> deletefrom t7 where a>500;

3500 rowsdeleted.

SQL> commit;

Commit complete.

SQL> analyzetable t7 compute statistics;

Table analyzed.

SQL> selectblocks,empty_blocks from dba_tables where table_name='T7';

BLOCKSEMPTY_BLOCKS

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

12 3

SQL> selectextent_id,block_id,blocks from dba_extents where segment_name='T7';

EXTENT_IDBLOCK_ID BLOCKS

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

0 63601 8

1 64049 8

SQL> select segment_name,header_file,header_block,blocksfrom dba_segments where segment_name='T7';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

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

T7 1 63601 16

SQL> altersystem dump datafile 1 block 63601;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_19189.trc

Highwater:: 0x0040fa36 ext#: 1 blk#: 5 ext size: 8

#blocks in seg. hdr's freelists: 11

#blocks below: 12

mapblk 0x00000000 offset: 1

Unlocked

HWM太高，HWM下空闲块过多的解决办法：

1. recreate table;

2. truncate table;

3. alter talbe shrink space;(forora10g &ASSM)

数据块头（包含块头，表目录区，行目录区）组成：

1. 定长部分数据：记录block type（即segment type）,block 地址等。

2. 变长部分数据：行地址，ITL（interestedtransaction list 有效事务列表）等。

ITL实际为block header中的一段用于保存数据的数据结构，称为ITL slot,其中保存的数据包括block中关于事务信息的记录，包括List编号、回滚段地址、事务状态，事务在该block影响的记录条数等。

每个需要存取该数据块的事务都需先获得一个该数据块的ITL slot，因此一个数据块上ITL slot入口的多少决定该数据块支持并发事务数量的多少，可在create table时以inittans（默认1）和maxtrans(默认255)参数指定初始和最大ITL slot数。当initrans<=2时，每个分配的数据块中都会有2个ITL slot，当ITL slot不够用时，oracle会动态增加ITL slot的数量。

SQL> create table t8 (x int,y varchar2(200)) pctfree 0;

Table created.

SQL> select ini_trans,max_trans from dba_tables where table_name='T8';

INI_TRANS MAX_TRANS

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

1 255

SQL> begin

2 for I in 1..4000

3 loop

4 insert into t8 values (I,'aaaaaaaaaa');

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> select extent_id,blocks,block_id from dba_extents wheresegment_name='T8';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 64057

1 8 64065

SQL> select rowid from t8 where x=1;

ROWID

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

AAANkBAABAAAPo6AAA

SQL> select rowid from t8 where x=2;

ROWID

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

AAANkBAABAAAPo6AAB

SQL> select rowid from t8 where x=3;

ROWID

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

AAANkBAABAAAPo6AAC

SQL> select dbms_rowid.rowid_block_number('AAANkBAABAAAPo6AAA')from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANKBAABAAAPO6AAA')

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

64058

SQL> select dbms_rowid.rowid_block_number('AAANkBAABAAAPo6AAB')from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANKBAABAAAPO6AAB')

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

64058

SQL> select dbms_rowid.rowid_block_number('AAANkBAABAAAPo6AAC')from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANKBAABAAAPO6AAC')

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

64058

SQL> update t8 set x=1 where x=1;

1 row updated.

SQL> updatet8 set x=2 where x=2;

2 row updated.

SQL> update t8 set x=3 where x=3;

SQL> commit

2 ;

Commit complete.

SQL> update t8 set x=3 where x=3;

1 row updated.

SQL> select rowid from scott.emp;

ROWID

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

AAAMfMAAEAAAAAcAAA

AAAMfMAAEAAAAAgAAA

AAAMfMAAEAAAAAgAAB

AAAMfMAAEAAAAAgAAC

AAAMfMAAEAAAAAgAAD

AAAMfMAAEAAAAAgAAE

AAAMfMAAEAAAAAgAAF

AAAMfMAAEAAAAAgAAG

AAAMfMAAEAAAAAgAAH

AAAMfMAAEAAAAAgAAI

AAAMfMAAEAAAAAgAAJ

AAAMfMAAEAAAAAgAAK

AAAMfMAAEAAAAAgAAL

AAAMfMAAEAAAAAgAAM

AAAMfMAAEAAAAAgAAN

15 rows selected.

A-Z:0-25 ， a-z: 26-51, 0-9: 52-61, /:62, +:63

SQL> selectdbms_rowid.rowid_object('AAAMfMAAEAAAAAgAAN') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAMFMAAEAAAAAGAAN')

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

51148

SQL> select object_name from dba_objectswhere object_id=&1;

Enter value for 1: 51148

old 1: select object_name from dba_objects where object_id=&1

new 1: select object_name from dba_objects where object_id=51148

OBJECT_NAME

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

EMP

SQL> selectdbms_rowid.rowid_object('AAANkBAABAAAPo6AAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAANKBAABAAAPO6AAA')

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

55553

SQL> select object_name from dba_objectswhere object_id=&1;

Enter value for 1: 55553

old 1: select object_name from dba_objects where object_id=&1

new 1: select object_name from dba_objects where object_id=55553

OBJECT_NAME

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

SQL> selectdbms_rowid.rowid_relative_fno('AAANkBAABAAAPo6AAA') from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAANKBAABAAAPO6AAA')

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

SQL> select file_name fromdba_data_files where file_id=&2;

Enter value for 2: 1

old 1: select file_name from dba_data_files where file_id=&2

new 1: select file_name from dba_data_files where file_id=1

FILE_NAME

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

/u01/app/oracle/oradata/ora10/system01.dbf

SQL> select dbms_rowid.rowid_relative_fno('AAAMFMAAEAAAAAGAAM')from dual;

DBMS_ROWID.ROWID_RELATIVE_FNO('AAAMFMAAEAAAAAGAAM')

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

SQL> select file_name fromdba_data_files where file_id=&2;

Enter value for 2: 4

old 1: select file_name from dba_data_files where file_id=&2

new 1: select file_name from dba_data_files where file_id=4

FILE_NAME

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

/u01/app/oracle/oradata/ora10/users01.dbf

SQL> selectdbms_rowid.rowid_block_number('AAANkBAABAAAPo6AAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANKBAABAAAPO6AAA')

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

64058

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='T8';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 64057

1 8 64065

SQL> selectdbms_rowid.rowid_row_number('AAANkBAABAAAPo6AAA') from dual;

DBMS_ROWID.ROWID_ROW_NUMBER('AAANKBAABAAAPO6AAA')

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

SQL> selectdbms_rowid.rowid_row_number('AAANkBAABAAAPo6AAB') from dual;

DBMS_ROWID.ROWID_ROW_NUMBER('AAANKBAABAAAPO6AAB')

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

SQL> alter system dump datafile 1 block64058;

System altered.

[oracle@desktop241 udump]$ vimora10_ora_7438.trc

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000b.019.00000041 0x050015b4.001c.2c --U- 1 fsc 0x0000.001e763d

0x02 0x0014.00b.0000003d 0x05000d08.0028.10 --U- 1 fsc 0x0000.001e7885

data_block_dump,data header at 0xdf2445c

===============

SQL> selectdbms_rowid.rowid_block_number ('AAAMFMAAEAAAAAGAAM') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAMFMAAEAAAAAGAAM')

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

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='EMP';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 25

Free list和ITL都影响并发性，区别如下：

1. ITL影响block层的并发性，free list影响segment层的并发性；

2. ITL影响并发的事务，freee list 影响并发的insert操作；

3. 事务进行全程均占用ITL，事务结束后释放；insert仅在事务进行前搜索占用freelist ，事务进行时free list已释放；

块清除block cleanouts

当事物commit时，block中的ITL被清除，称为block cleanouts.

Fast block cleanouts

Commit时若需cleanouts的块位于buffer cache，则立即清除ITL(fast block cleanouts),若事物太大，需要清除的块数量大于buffer cache的10%，则暂只立即清除10%以内的部分。

Delalyed block cleanouts

Commit时若需cleanouts的块已写到磁盘，则延时清除ITL(delayed block cleanouts),若事物太大，需要清除的块数量大于buffer cache的10%，则10%以外的部分进行延时清除。延时清除将在块被再次访问时进行，以提高commit的性能。

SQL> createtable test as select * from dba_objects;

Table created.

SQL> setautot on stat;

SQL> altersession set events='immediate trace name flush_cache';

Session altered.

SQL> commit;

Commit complete.

SQL> selectcount(*) from test;

COUNT(*)

----------

50793

Statistics

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

28 recursive calls

0 db block gets

777 consistent gets

698 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> selectcount (*) from test;

COUNT(*)

----------

50793

Statistics

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

4 recursive calls

0 db block gets

775 consistent gets

0 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

补充：

MSSM段管理方式

SQL> create table a1 (a number,bchar(30)) tablespace system;

Table created.

SQL> begin

2 For I in 1..2000 loop

3 insert into a1 values(I,'aaaaaaaaaa');

4 commit;

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> select extent_id,blocks,block_idfrom dba_extents where segment_name='A1';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 64409

1 8 64417

SQL> select header_file,header_blockfrom dba_segments where segment_name='A1';

HEADER_FILE HEADER_BLOCK

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

1 64409

SQL> alter system dump datafile&HEADER_FILE block &HEADER_BLOCK;

Enter value for header_file: 1

Enter value for header_block: 64409

old 1: alter system dump datafile &HEADER_FILE block &HEADER_BLOCK

new 1: alter system dump datafile 1 block 64409

System altered.

Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 15

last map 0x00000000 #maps: 0 offset: 4128

Highwater:: 0x0040fba6 ext#: 1 blk#: 5 ext size: 8

#blocks in seg. hdr's freelists: 1

#blocks below: 12

mapblk 0x00000000 offset: 1

Unlocked

SQL> select rowid from a1 where a=1;

ROWID

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

AAANkEAABAAAPuaAAA

SQL> selectdbms_rowid.rowid_block_number('AAANkEAABAAAPuaAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAANKEAABAAAPUAAAA')

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

64410

System表空间为MSSM管理方式，由freelist链表管理段内空闲空间，上述可见所有已分配extent内所包含block中，仅第一个block为段头信息（存放freelist 及HWM信息，可dump后查看），其余均为数据块，第一条数据即是从第二个块开始插入的。

SQL> createtablespace tb2 datafile '/u01/tb2.dat' size 10M;

Tablespacecreated.

SQL> alterdatabase datafile '/u01/tb2.dat' autoextend on;

Databasealtered.

SQL> createtable a2 tablespace tb2 as select * from dba_objects;

Table created.

SQL> selectextent_id,blocks,block_id from dba_extents where segment_name='A2';

EXTENT_ID BLOCKS BLOCK_ID

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

0 8 9【一级位图块】10【二级位图块】11【区的编号和二级位图地址】【三个块存放控制信息】

1 8 17

2 8 25【一个块存放控制信息】

3 8 33

4 8 41【一个块存放控制信息】

5 8 49

6 8 57

7 8 65

8 8 73

9 8 81

10 8 89

11 8 97

12 8 105

13 8 113

14 8 121

15 8 129

16 128 137【2个块存放控制信息】【126 个块存放数据】

17 128 265【2个块存放控制信息】【每个控制块管理64个数据块】

18 128 393

19 128 521

20 128 649

SQL> altersystem dump datafile 21 block 41;

System altered.

Dump of FirstLevel Bitmap Block

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

nbits : 4 nranges: 2 parent dba: 0x0540000a poffset: 2

unformatted: 0 total: 16 first useful block: 1

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 16

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 55557

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

DBA Ranges :

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

0x05400029 Length: 8 Offset: 0

0x05400031 Length: 8 Offset: 8

0:Metadata 1:FULL 2:FULL 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

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

End dump datablocks tsn: 28 file#: 21 minblk 41 maxblk 41

SQL> select header_file,header_blockfrom dba_segments where segment_name='A2';

HEADER_FILEHEADER_BLOCK

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

21 11

SQL> alter system dump datafile21 block 11;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_16134.trc

Extent ControlHeader

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

Extent Header:: spare1: 0 spare2: 0 #extents: 21 #blocks: 768

last map 0x00000000 #maps: 0 offset: 2716

Highwater:: 0x054002d7 ext#: 20 blk#: 78 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 718

mapblk 0x00000000 offset: 20

Unlocked

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

Low HighWater Mark :

Highwater:: 0x054002d7 ext#: 20 blk#: 78 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 718

mapblk 0x00000000 offset: 20

Level 1 BMB for High HWM block: 0x0540028a

Level 1 BMB for Low HWM block: 0x0540028a

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

Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0

L2 Array start offset: 0x00001434

First Level 3 BMB: 0x00000000

L2 Hint for inserts: 0x0540000a

Last Level 1 BMB: 0x0540028a

Last Level II BMB: 0x0540000a

Last Level III BMB: 0x00000000

Map Header:: next 0x00000000 #extents: 21 obj#: 55557 flag: 0x10000000

Inc # 0

Extent Map【区图】

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

0x05400009 length: 8

0x05400011 length: 8

0x05400019 length: 8

0x05400021 length: 8

0x05400029 length: 8

0x05400031 length: 8

0x05400039 length: 8

0x05400041 length: 8

0x05400049 length: 8

0x05400051 length: 8

0x05400059 length: 8

0x05400061 length: 8

0x05400069 length: 8

0x05400071 length: 8

0x05400079 length: 8

0x05400081 length: 8

0x05400089 length: 128

0x05400109 length: 128

0x05400189 length: 128

0x05400209 length: 128

0x05400289 length: 128

Auxillary Map【所有区的编号】

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

Extent 0 : L1 dba: 0x05400009 Data dba: 0x0540000c

Extent 1 : L1 dba: 0x05400009 Data dba: 0x05400011

Extent 2 : L1 dba: 0x05400019 Data dba: 0x0540001a

Extent 3 : L1 dba: 0x05400019 Data dba: 0x05400021

Extent 4 : L1 dba: 0x05400029 Data dba: 0x0540002a

Extent 5 : L1 dba: 0x05400029 Data dba: 0x05400031

Extent 6 : L1 dba: 0x05400039 Data dba: 0x0540003a

Extent 7 : L1 dba: 0x05400039 Data dba: 0x05400041

Extent 8 : L1 dba: 0x05400049 Data dba: 0x0540004a

Extent 9 : L1 dba: 0x05400049 Data dba: 0x05400051

Extent 10 : L1 dba: 0x05400059 Data dba: 0x0540005a

Extent 11 : L1 dba: 0x05400059 Data dba: 0x05400061

Extent 12 : L1 dba: 0x05400069 Data dba: 0x0540006a

Extent 13 : L1 dba: 0x05400069 Data dba: 0x05400071

Extent 14 : L1 dba: 0x05400079 Data dba: 0x0540007a

Extent 15 : L1 dba: 0x05400079 Data dba: 0x05400081

Extent 16 : L1 dba: 0x05400089 Data dba: 0x0540008b

Extent 17 : L1 dba: 0x05400109 Data dba: 0x0540010b

Extent 18 : L1 dba: 0x05400189 Data dba: 0x0540018b

Extent 19 : L1 dba: 0x05400209 Data dba: 0x0540020b

Extent 20 : L1 dba: 0x05400289 Data dba: 0x0540028b

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

Second Level Bitmap block DBAs【二级位图块地址】

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

DBA 1: 0x0540000a

End dump datablocks tsn: 28 file#: 21 minblk 11 maxblk 11

SQL> alter system dump datafile21 block 10;

[oracle@desktop241udump]$ vim ora10_ora_16480.trc

Dump of Second Level Bitmap Block【二级位图块】

number: 18 nfree: 1 ffree: 17 pdba: 0x0540000b

Inc #: 0 Objd: 55557

opcode:0

xid:

L1 Ranges :

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

0x05400009 Free: 1 Inst: 1

0x05400019 Free: 1 Inst: 1

0x05400029 Free: 1 Inst: 1

0x05400039 Free: 1 Inst: 1

0x05400049 Free: 1 Inst: 1

0x05400059 Free: 1 Inst: 1

0x05400069 Free: 1 Inst: 1

0x05400079 Free: 1 Inst: 1

0x05400089 Free: 1 Inst: 1

0x0540008a Free: 1 Inst: 1

0x05400109 Free: 1 Inst: 1

0x0540010a Free: 1 Inst: 1

0x05400189 Free: 1 Inst: 1

0x0540018a Free: 1 Inst: 1

0x05400209 Free: 1 Inst: 1

0x0540020a Free: 1 Inst: 1

0x05400289 Free: 1 Inst: 1

0x0540028a Free: 5 Inst: 1

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

End dump datablocks tsn: 28 file#: 21 minblk 10 maxblk 10

SQL> alter system dump datafile21 block 9;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_16571.trc

Dump of First Level Bitmap Block【一级位图块】

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

nbits : 4 nranges: 2 parent dba: 0x0540000a poffset: 0

unformatted: 0 total: 16 first useful block: 3

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 16

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 55557

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

DBA Ranges :

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

0x05400009 Length: 8 Offset: 0

0x05400011 Length: 8 Offset: 8

0:Metadata 1:Metadata 2:Metadata 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

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

End dump datablocks tsn: 28 file#: 21 minblk 9 maxblk 9

SQL> alter system dump datafile21 block 25;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_16714.trc

Dump of FirstLevel Bitmap Block

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

nbits : 4 nranges: 2 parent dba: 0x0540000a poffset: 1

unformatted: 0 total: 16 first useful block: 1

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 16

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 55557

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

DBA Ranges :

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

0x05400019 Length: 8 Offset: 0

0x05400021 Length: 8 Offset: 8

0:Metadata 1:FULL 2:FULL 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

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

End dump datablocks tsn: 28 file#: 21 minblk 25 maxblk 25

SQL> alter system dump datafile21 block 137;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_16771.trc

Dump of FirstLevel Bitmap Block

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

nbits: 4 nranges: 1 parent dba: 0x0540000a poffset: 8

unformatted: 0 total: 64 first useful block: 2

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 64

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 55557

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

DBA Ranges :

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

0x05400089 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:FULL 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

16:FULL 17:FULL 18:FULL 19:FULL

20:FULL 21:FULL 22:FULL 23:FULL

24:FULL 25:FULL 26:FULL 27:FULL

28:FULL 29:FULL 30:FULL 31:FULL

32:FULL 33:FULL 34:FULL 35:FULL

36:FULL 37:FULL 38:FULL 39:FULL

40:FULL 41:FULL 42:FULL 43:FULL

44:FULL 45:FULL 46:FULL 47:FULL

48:FULL 49:FULL 50:FULL 51:FULL

52:FULL 53:FULL 54:FULL 55:FULL

56:FULL 57:FULL 58:FULL 59:FULL

60:FULL 61:FULL 62:FULL 63:FULL

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

End dump datablocks tsn: 28 file#: 21 minblk 137 maxblk 137

SQL> altersystem dump datafile 21 block 138;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_17269.trc

Dump of FirstLevel Bitmap Block

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

nbits : 4 nranges: 1 parent dba: 0x0540000a poffset: 9

unformatted: 0 total: 64 first useful block: 0

owning instance : 1

instance ownership changed at

Last successful Search

Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map BlockOffset: 4294967295

First free datablock : 64

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Inc #: 0 Objd: 55557

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

DBA Ranges :

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

0x054000c9 Length: 64 Offset: 0

0:FULL 1:FULL 2:FULL 3:FULL

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:FULL 11:FULL

12:FULL 13:FULL 14:FULL 15:FULL

16:FULL 17:FULL 18:FULL 19:FULL

20:FULL 21:FULL 22:FULL 23:FULL

24:FULL 25:FULL 26:FULL 27:FULL

28:FULL 29:FULL 30:FULL 31:FULL

32:FULL 33:FULL 34:FULL 35:FULL

36:FULL 37:FULL 38:FULL 39:FULL

40:FULL 41:FULL 42:FULL 43:FULL

44:FULL 45:FULL 46:FULL 47:FULL

48:FULL 49:FULL 50:FULL 51:FULL

52:FULL 53:FULL 54:FULL 55:FULL

56:FULL 57:FULL 58:FULL 59:FULL

60:FULL 61:FULL 62:FULL 63:FULL

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

End dump datablocks tsn: 28 file#: 21 minblk 138 maxblk 138

SQL> altersystem dump datafile 21 block 139;

System altered.

[oracle@desktop241udump]$ vim ora10_ora_17343.trc

全部是数据

新建tb2表空间默认为ASSM管理方式，由位图管理段内空闲空间，上述可见所有已分配extent内所包含block中，第3个block为段头信息（存放所有区的编号及二级位图块地址，可dump后查看），第2个块为二级位图块，第1个块为一级位图块。