Oracle---存储结构

存储结构：
   逻辑结构           物理结构
  database
     |
 tablespace    --->  datafile
     |                  |
  segment               |
     |                  |
  extent                |
     |                  |
oracle block   --->  os block
-----------------------------------------------------------------------------------
表空间的空间管理：
DMT(dictionary managment tablespace):
LMT(local managment tablespace):

查看表空间的空间管理算法：
select tablespace_name,extent_management from dba_tablespaces;
将数据文件中8K块的信息转储到用户进程跟踪文件
alter system dump datafile 9 block min 1 block max 128;
-----------------------------------------------------------------------------------
段的空间管理模式：是指段内的空闲空间如何管理，insert操作的时候如何使行快速入库
查看段空间管理算法：
select tablespace_name,segment_space_management from dba_tablespaces;
MANUAL:使用空闲列表管理空闲块（可以用来做insert操作的块）
AUTO:

什么是段空间的手工管理算法：
create tablespace tbs4 datafile '/u01/app/oracle/oradata/demo/tbs4.dbf' size 10m segment space management manual;
create table scott.t04 (x int,name varchar2(10)) segment creation immediate tablespace tbs4;
查看表的空间占用情况：
select file_id,block_id,blocks from dba_extents where segment_name='T04';

   FILE_ID   BLOCK_ID      BLOCKS
---------- ---------- ----------
    10      128           8

alter system checkpoint;
alter system dump datafile 10 block min 128 block max 135;
-------------------------------
      Highwater::  0x02800081  --> HWM:高水位，段中第一个空白块

  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

做交易，观察表的空间变化：
insert into scott.t04 values (0,'A');
commit;
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;
-------------------------------
      Highwater::  0x02800082
  SEG LST:: flg: USED   lhd: 0x02800081 ltl: 0x02800081

做交易将8K插满数据，观察表的空间变化：
begin
  for i in 2..660 loop
    insert into scott.t04 values (i,'A');
  end loop;
  commit;
end;
/
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;

将7个8K都插满数据，观察表的空间变化：
begin
  for i in 662..4620 loop
    insert into scott.t04 values (i,'A');
  end loop;
  commit;
end;
/
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;
----------------------------------------
      Highwater::  0x02800088
  SEG LST:: flg: USED   lhd: 0x02800087 ltl: 0x02800087

再差1行数据，观察表的空间变化：
insert into scott.t04 values (4621,'A');
commit;
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;
----------------------------------------
      Highwater::  0x0280008d --> 141
  SEG LST:: flg: USED   lhd: 0x02800088 ltl: 0x0280008c -->高水位推进5个8K

查看表中空闲列表的数量：
select freelists from dba_tables where table_name='T04';
 FREELISTS
----------
     1  --> master freelist

增加空闲列表的数量：上限是99个
alter table scott.t04 storage (freelists 2);

SYS@ demo> select freelists from dba_tables where table_name='T04';

 FREELISTS
----------
     2 --> process freelist

-------------------------------------------------------
  SEG LST:: flg: USED   lhd: 0x02800088 ltl: 0x0280008c --> master freelist
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 --> process freelist
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 --> process freelist

transaction freelist:
查看每一个数据块中有多少行数据：
select dbms_rowid.rowid_block_number(rowid),count(1) from scott.t04 group by dbms_rowid.rowid_block_number(rowid) order by 1;
将131号块删除100行
delete scott.t04 where dbms_rowid.rowid_block_number(rowid)=131 and rownum<101;
commit;
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;

delete scott.t04 where dbms_rowid.rowid_block_number(rowid)=131 and rownum<101;
commit;
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;

delete scott.t04 where dbms_rowid.rowid_block_number(rowid)=131 and rownum<101;
commit;
alter system checkpoint;
conn / as sysdba
alter system dump datafile 10 block 128;

--------------------------------------------------------------------------------
  SEG LST:: flg: USED   lhd: 0x02800088 ltl: 0x0280008c
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
  XCT LST:: flg: USED   lhd: 0x02800083 ltl: 0x02800083 xid: 0x0015.05d.00000015

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

什么是段空间的自动管理算法：使用位图块管理段内的空闲空间
select tablespace_name,segment_space_management from dba_tablespaces;

create table scott.t03 (x int,name varchar2(10)) segment creation immediate tablespace tbs3;

查看表的空间占用情况：
select file_id,block_id,blocks from dba_extents where segment_name='T03';

   FILE_ID   BLOCK_ID      BLOCKS
---------- ---------- ----------
     9      128           8

查看段头块的位置
select header_file,header_block from dba_segments where segment_name='T03';

HEADER_FILE HEADER_BLOCK
----------- ------------
      9         130


alter system checkpoint;
conn / as sysdba
alter system dump datafile 9 block min 128 block max 130;
==============================================================
buffer tsn: 10 rdba: 0x02400080 (9/128)
scn: 0x0000.000b33a4 seq: 0x02 flg: 0x04 tail: 0x33a42002
frmt: 0x02 chkval: 0x4a95 type: 0x20=FIRST LEVEL BITMAP BLOCK

buffer tsn: 10 rdba: 0x02400081 (9/129)
scn: 0x0000.000b33a4 seq: 0x02 flg: 0x04 tail: 0x33a42102
frmt: 0x02 chkval: 0xb01f type: 0x21=SECOND LEVEL BITMAP BLOCK

buffer tsn: 10 rdba: 0x02400082 (9/130)
scn: 0x0000.000b33a4 seq: 0x03 flg: 0x04 tail: 0x33a42303
frmt: 0x02 chkval: 0x9e73 type: 0x23=PAGETABLE SEGMENT HEADER

Highwater::  0x02400083
Last Level 1 BMB:  0x02400080
Last Level II BMB:  0x02400081
Last Level III BMB:  0x00000000
==============================================================
使用level记录块的空间使用情况
空间使用情况被分成6中标示位
unformat
full
free 0~25%
free 25~50%
free 50~75%
free 75~100%

begin
  for i in 1..3300 loop
    insert into scott.t03 values (i,'A');
  end loop;
  commit;
end;
/

alter system checkpoint;
conn / as sysdba
alter system dump datafile 9 block 130;

select file_id,block_id,blocks from dba_extents where segment_name='T03';

   FILE_ID   BLOCK_ID      BLOCKS
---------- ---------- ----------
     9      128           8
     9      136           8

begin
  for i in 1..5280 loop
    insert into scott.t03 values (i,'A');
  end loop;
  commit;
end;
/

alter system checkpoint;
conn / as sysdba
alter system dump datafile 9 block 130;

select file_id,block_id,blocks from dba_extents where segment_name='T03';

   FILE_ID   BLOCK_ID      BLOCKS
---------- ---------- ----------
     9      128           8
     9      136           8
     9      144           8

查看每一个数据块中有多少行数据：
select dbms_rowid.rowid_block_number(rowid),count(1) from scott.t03 group by dbms_rowid.rowid_block_number(rowid) order by 1;

计算不同空闲空间的块的数量
set serverout on
declare
   v_unformatted_blocks NUMBER;
   v_unformatted_bytes NUMBER;
   v_fs1_blocks NUMBER;
   v_fs1_bytes NUMBER;
   v_fs2_blocks NUMBER;
   v_fs2_bytes NUMBER;
   v_fs3_blocks NUMBER;
   v_fs3_bytes NUMBER;
   v_fs4_blocks NUMBER;
   v_fs4_bytes NUMBER;
   v_full_blocks NUMBER;
   v_full_bytes NUMBER;
begin
DBMS_SPACE.SPACE_USAGE(
'SCOTT','T03','TABLE',
   v_unformatted_blocks,
   v_unformatted_bytes,
   v_fs1_blocks,
   v_fs1_bytes,
   v_fs2_blocks,
   v_fs2_bytes,
   v_fs3_blocks,
   v_fs3_bytes,
   v_fs4_blocks,
   v_fs4_bytes,
   v_full_blocks,
   v_full_bytes);
dbms_output.put_line('unformatted_blocks: '||v_unformatted_blocks);
dbms_output.put_line('full_blocks: '||v_full_blocks);
dbms_output.put_line('free 0~25%: '||v_fs1_blocks);
dbms_output.put_line('free 25~50%: '||v_fs2_blocks);
dbms_output.put_line('free 50~75%: '||v_fs3_blocks);
dbms_output.put_line('free 75~100%: '||v_fs4_blocks);
end;
/

begin
  for i in 1..10000 loop
    if mod(i,2)=0 then
      delete scott.t03 where x=i;
    end if;
  end loop;
end;
/
=================================================================================
extent（范围、区、片）的管理：是段分配空间的单位，最小64K
extent何时分配：
1.段创建时
--create table scott.t03 (x int,name varchar2(10)) tablespace tbs3;
create table scott.t03 (x int,name varchar2(10)) segment creation immediate tablespace tbs3;

2.数据增长时
insert & update

3.手工分配
alter table scott.t03 allocate extent (size 64k);

extent何时回收：
1.手工回收：
alter table scott.t03 deallocate unused;

delete scott.t03 where rownum<20000;
commit;
alter table scott.t03 enable row movement;
alter table scott.t03 shrink space;

2.truncate table & drop table purge
--truncate table scott.t03 reuse storage;
truncate table scott.t03;

extent分配算法：
create table scott.t03 (x int,name varchar2(10)) segment creation immediate tablespace tbs3;

create table scott.t03 (x int,name varchar2(10)) segment creation immediate storage (initial 1m) tablespace tbs3;

begin
  for i in 1..1000 loop
    insert into scott.t03 values (i,'A');
  end loop;
  commit;
end;
/

查看extent的空间分配算法：
select tablespace_name,allocation_type from dba_tablespaces;
SYSTEM：阶梯增长
1~16  : 8*8K(前16个区64K一个)
17~79 : 128*8K （后63个区1M一个）
80~200: 1024*8K（后120个8M一个）
200-->: 8192*8k （以后64M一个）

UNIFORM：统一分配（制式增长，傻瓜式的增长）
create tablespace tbs5 datafile '/u01/app/oracle/oradata/demo/tbs5.dbf' size 100m reuse uniform size 10m;

create table scott.t05 (x int,name varchar2(10)) segment creation immediate tablespace tbs5;

select file_id,block_id,blocks from dba_extents where segment_name='T05';

==================================
DMT LMT MANUAL AUTO SYSTEM UNIFORM
==================================



oracle block的空间管理：
8K块要预留块的头：84~107
块的头的内容：块的地址，块被修改的时间（scn），块的类型，表目录，行目录，事务槽（24byte）块头至少会预留2个事务槽
除了块头以外的空间做insert操作是否可以写满数据？取决于一个参数（打包因子）pctfree
select pct_free,pct_used from dba_tables where table_name='T03';

  PCT_FREE
------------
    10 --> 10% ： 做insert操作时要留10%的空闲，应付块内行自身的增长

pct_used:只有在段空间手工管理模式下才会存在

create table t02 (x int,y varchar2(100)) pctfree 0;
begin
  for i in 1..1000 loop
    insert into t02 values (i,'A');
  end loop;
  commit;
end;
/

查看T02的空间占用情况
select file_id,block_id,blocks from dba_extents where segment_name='T02';

   FILE_ID   BLOCK_ID      BLOCKS
---------- ---------- ----------
     7      280           8

计算T02中每个8K存放多少行数据：
select dbms_rowid.rowid_block_number(rowid),count(1) from scott.t02 group by dbms_rowid.rowid_block_number(rowid) order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(1)
------------------------------------ ----------
                 284        733
                 285        267

select 660/733 from dual;

   660/733
----------
.900409277

将7号文件的284号块中的一行update成100个‘A’：
select * from scott.t02 where dbms_rowid.rowid_block_number(rowid)=284 and rownum<11;

 X Y
-- -
 1 A
 2 A
 3 A
 4 A
 5 A
 6 A
 7 A
 8 A
 9 A
10 A

将7号文件的284号块dump出来:
alter system checkpoint;
conn / as sysdba
alter system dump datafile 7 block 284;
----------------------------------------------------------------------------
Start dump data blocks tsn: 4 file#:7 minblk 284 maxblk 284
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=29360412
BH (0x823fb410) file#: 7 rdba: 0x01c0011c (7/284) class: 1 ba: 0x823d4000
  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 252,19
  dbwrid: 0 obj: 13826 objn: 13826 tsn: 4 afn: 7 hint: f
  hash: [0x90552e20,0x90552e20] lru: [0x827d6108,0x823fb3c8]
  ckptq: [NULL] fileq: [NULL] objq: [0x827d63a0,0x823fb3f0] objaq: [0x827d6278,0x823fb400]
  st: XCURRENT md: NULL fpin: 'kcbwh6: kcbnew' tch: 6
  flags: block_written_once redo_since_read
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x01c0011c (7/284)
scn: 0x0000.000bb8bd seq: 0x01 flg: 0x06 tail: 0xb8bd0601
frmt: 0x02 chkval: 0xd845 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
----------------------------------------------------------------------------

修改第9行数据：
update scott.t02 set y=rpad('A',100,'A') where x=9;
commit;

将7号文件的284号块dump出来:
alter system checkpoint;
conn / as sysdba
alter system dump datafile 7 block 284;
----------------------------------------
tab 0, row 8, @0x634
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x01c0011e.1 --> 行的迁移
            块编号.行号
            286 . row 1
----------------------------------------
select to_number('11e','xxx') from dual;

conn / as sysdba
alter system dump datafile 7 block 286;
----------------------------------------------------------------------------
tab 0, row 1, @0x1f06
tl: 113 fb: ----FL-- lb: 0x2  cc: 2
hrid: 0x01c0011c.8
             284.row 8
col  0: [ 2]  c1 0a
col  1: [100]
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
----------------------------------------------------------------------------

select * from scott.t02 where dbms_rowid.rowid_block_number(rowid)=284 and rownum<11;

使用函数将rowid转成10进制数：
select
rowid,
dbms_rowid.ROWID_OBJECT(rowid) object_id,
dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_id,
dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block_id,
dbms_rowid.ROWID_ROW_NUMBER(rowid) row_id
from scott.t02
where x=9;

dump函数的反向函数：
select utl_raw.cast_to_number(replace('c2 0b 02',' ')) from dual;