[20161108]关于数据文件的问题.txt
--昨天看了一些数据文件位图问题，今天探究数据文件的其他问题。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.段空间ASSM或MSSM。
--数据文件表空间支持段空间自动管理和手动管理。
SCOTT@book> select * from dba_extents where owner=user and segment_name='EMP';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP TABLE USERS 0 4 144 65536 8 4
SCOTT@book> alter system dump datafile 4 block min 144 block max 151;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54197.trc
buffer tsn: 4 rdba: 0x01000090 (4/144)
frmt: 0x02 chkval: 0x9220 type: 0x20=FIRST LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000091 (4/145)
frmt: 0x02 chkval: 0xd2fc type: 0x21=SECOND LEVEL BITMAP BLOCK
buffer tsn: 4 rdba: 0x01000092 (4/146)
frmt: 0x02 chkval: 0xffc0 type: 0x23=PAGETABLE SEGMENT HEADER
buffer tsn: 4 rdba: 0x01000093 (4/147)
frmt: 0x02 chkval: 0xb843 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000094 (4/148)
frmt: 0x02 chkval: 0xeca2 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000095 (4/149)
frmt: 0x02 chkval: 0x6932 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000096 (4/150)
frmt: 0x02 chkval: 0xc695 type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000097 (4/151)
frmt: 0x02 chkval: 0x332e type: 0x06=trans data
buffer tsn: 4 rdba: 0x01000098 (4/152)
--ASSM存在FIRST LEVEL BITMAP BLOCK,SECOND LEVEL BITMAP BLOCK,PAGETABLE SEGMENT HEADER.如果表很大很大也许还存在THIRD LEVEL BITMAP BLOCK(很少见)。
--在段的分配上还有EXTENT MANAGEMENT LOCAL AUTOALLOCATE 和 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 。
--后者每个段统一大小，2者各有个的优缺点。我一般使用前者EXTENT MANAGEMENT LOCAL AUTOALLOCATE。
--如果有一些大表我会使用单独的表空间采用EXTENT MANAGEMENT LOCAL UNIFORM SIZE。而且一般我会设置UNIFORM SIZE至少64M。
--这样当你建立表空间时如果采用SEGMENT SPACE MANAGEMENT auto，UNIFORM SIZE不能设置太小，例子：
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/suagr01.dbf' SIZE 40M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT auto
FLASHBACK ON;
*
ERROR at line 1:
ORA-03249: Uniform size for auto segment space managed tablespace should have atleast 5 blocks
--也就是这种模式UNIFORM SIZE 最少5个块，便于容纳FIRST LEVEL BITMAP BLOCK，SECOND LEVEL BITMAP BLOCK，PAGETABLE SEGMENT HEADER。
3.下面做一些测试：
--建立数据文件最小是多少，昨天我测试建立ASSM的情况，参考链接：http://blog.itpub.net/267265/viewspace-2127936/
--对于MSSM呢？假设UNIFORM SIZE 8K(这个已经是最小UNIFORM SIZE).
--这样位图区必须1个位图区头，1个位图区。文件头占1块，数据块至少2块(这种模式使用freelist管理)，这样最小的数据文件是40k。
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40k AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
$ ls -l /mnt/ramdisk/book/suagr01.dbf
-rw-r----- 1 oracle oinstall 49152 2016-11-08 09:00:19 /mnt/ramdisk/book/suagr01.dbf
--49152/1024=48K.
SCOTT@book> create table xx tablespace sugar as select * from emp where rownum=1;
Table created.
SCOTT@book> select * from dba_extents where owner=user and segment_name='XX';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- --------- ------------ ------------ ------------ ------------ ------------
SCOTT XX TABLE SUGAR 0 6 4 16384 2 6
SCOTT@book> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name='XX';
HEADER_FILE HEADER_BLOCK
------------ ------------
6 4
SYS@book> alter system checkpoint;
System altered.
SYS@book> alter system dump datafile 6 block min 1 block max 5;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_54353.trc
buffer tsn: 7 rdba: 0x01800002 (6/2)
frmt: 0x02 chkval: 0x9ab0 type: 0x1d=KTFB Bitmapped File Space Header
buffer tsn: 7 rdba: 0x01800003 (6/3)
frmt: 0x02 chkval: 0x4e49 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x01800004 (6/4)
frmt: 0x02 chkval: 0xbca0 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
buffer tsn: 7 rdba: 0x01800005 (6/5)
frmt: 0x02 chkval: 0xd979 type: 0x06=trans data
4.这样的文件能扩展到多少，位图区仅仅1块.如果文件变大，位图在那里呢？
--继续做一些测试，后面的测试千万不要在生产系统进行!!
SYS@book> alter system dump datafile 6 block 3;
System altered.
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0000.0029286f seq: 0x01 flg: 0x04 tail: 0x286f1e01
frmt: 0x02 chkval: 0x4e49 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F9C8BD02A00 to 0x00007F9C8BD04A00
7F9C8BD02A00 0000A21E 01800003 0029286F 04010000 [........o().....]
7F9C8BD02A10 00004E49 00000006 00000004 00000000 [IN..............]
7F9C8BD02A20 00000001 0000F7FF 00000000 00000000 [................]
7F9C8BD02A30 00000000 00000000 00000001 00000000 [................]
7F9C8BD02A40 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
7F9C8BD049F0 00000000 00000000 00000000 286F1E01 [..............o(]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 4, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
--首先遇到一个奇怪的问题，明明使用2个数据块，应该标识2个1.而现在仅仅1个。而如果你看表空间的定义实际上如下：
SYS@book> select dbms_metadata.get_ddl( 'TABLESPACE', 'SUGAR') c100 from dual;
C100
----------------------------------------------------------------------------------------------------
CREATE TABLESPACE "SUGAR" DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40960
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16384 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL
--这样看实际上最小的UNIFORM SIZE=16K,虽然上面的执行没有报错，实际上建立的UNIFORM SIZE=16K。
--如果把位图区全部变成1，要建立多大的文件：
--63487+1 =63488
--63488*16384=1040187392
--1040187392/1024/1024=992M
--1040187392/1024+16=1015824K
--63488*2=126976
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' AUTOEXTEND ON NEXT 16K MAXSIZE 1015824K;
Database altered.
SYS@book> @ &r/desc_proc sys DBMS_SPACE_ADMIN TABLESPACE_REBUILD_BITMAPS
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_SPACE_ADMIN TABLESPACE_REBUILD_BITMAPS 3 BITMAP_BLOCK BINARY_INTEGER IN BINARY_INTEGER Y
2 BITMAP_RELATIVE_FILE BINARY_INTEGER IN BINARY_INTEGER Y
1 TABLESPACE_NAME VARCHAR2 IN VARCHAR2 N
--注意千万不要在生产系统执行如下命令!!
-- EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR');
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015824K;
Database altered.
SYS@book> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.
SYS@book> alter system checkpoint;
System altered.
SYS@book> alter system dump datafile 6 block 3;
System altered.
--检查转储文件。
Block dump from disk:
buffer tsn: 7 rdba: 0x01800003 (6/3)
scn: 0x0000.00292e85 seq: 0x01 flg: 0x04 tail: 0x2e851e01
frmt: 0x02 chkval: 0x41b6 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F9C8BD02A00 to 0x00007F9C8BD04A00
7F9C8BD02A00 0000A21E 01800003 00292E85 04010000 [..........).....]
7F9C8BD02A10 000041B6 00000006 00000004 00000000 [.A..............]
7F9C8BD02A20 0000F800 00000000 00000000 00000000 [................]
7F9C8BD02A30 00000000 00000000 FFFFFFFF FFFFFFFF [................]
7F9C8BD02A40 FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF [................]
Repeat 494 times
7F9C8BD04930 FFFFFFFF FFFFFFFF 00000000 00000000 [................]
7F9C8BD04940 00000000 00000000 00000000 00000000 [................]
Repeat 10 times
7F9C8BD049F0 00000000 00000000 00000000 2E851E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 4, Flag: 0, First: 63488, Free: 0
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
...
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
End dump data blocks tsn: 7 file#: 6 minblk 3 maxblk 3
--你可以发现执行DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR')后位图区全部变成F，也就是全部占用。
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
Block dump from disk:
buffer tsn: 7 rdba: 0x01800002 (6/2)
scn: 0x0000.00292e82 seq: 0x01 flg: 0x04 tail: 0x2e821d01
frmt: 0x02 chkval: 0x6aab type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F1F94E18200 to 0x00007F1F94E1A200
7F1F94E18200 0000A21D 01800002 00292E82 04010000 [..........).....]
7F1F94E18210 00006AAB 00000006 00000002 0001F002 [.j..............]
7F1F94E18220 00000019 00000002 0001F002 00000002 [................]
7F1F94E18230 0001F001 00000000 00000000 000E2008 [............. ..]
7F1F94E18240 00000000 00000000 00000000 00000000 [................]
7F1F94E18250 00000004 00000002 00000000 00000000 [................]
7F1F94E18260 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
7F1F94E1A1F0 00000000 00000000 00000000 2E821D01 [................]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126978, Flag: 25
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126977, First: 0, Free: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deallocation scn: 925704.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--注意看～部分，Tail: 126977.
--我数据文件resize到1015824K，1015824/8=126978,这样看看最后一块的情况。
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 126978;
System altered.
Start dump data blocks tsn: 7 file#:6 minblk 126978 maxblk 126978
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25292802
Block dump from disk:
buffer tsn: 7 rdba: 0x0181f002 (6/126978)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0x5683 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x00007F1F94E18200 to 0x00007F1F94E1A200
7F1F94E18200 0000A200 0181F002 00000000 05010000 [................]
7F1F94E18210 00005683 00000000 00000000 00000000 [.V..............]
7F1F94E18220 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F1F94E1A1F0 00000000 00000000 00000000 00000001 [................]
Dump of memory from 0x00007F1F94E18214 to 0x00007F1F94E1A1FC
7F1F94E18210 00000000 00000000 00000000 [............]
7F1F94E18220 00000000 00000000 00000000 00000000 [................]
Repeat 508 times
7F1F94E1A1F0 00000000 00000000 00000000 [............]
End dump data blocks tsn: 7 file#: 6 minblk 126978 maxblk 126978
--奇怪类型居然为unknown。
SCOTT@book> create table yy tablespace sugar as select * from dept where rownum=1;
Table created.
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and file_id=6;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT XX TABLE SUGAR 0 6 4 16384 2 6
SCOTT YY TABLE SUGAR 0 6 6 16384 2 6
--居然可以使用标志1的空间!!
SYS@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 8 1040138240 126970 6
--可以发现还有126970块没有使用，重启数据库也一样。
SCOTT@book> create table zz tablespace sugar as select rownum id from dual connect by level<=2;
Table created.
SCOTT@book> ALTER TABLE zz MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
SCOTT@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 10 1040121856 126968 6
-- 126968*2=253936.
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253936 ;
insert into zz select rownum+2 id from dual connect by level<=253936
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253934;
insert into zz select rownum+2 id from dual connect by level<=253934
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
--不行,重来。
drop table xx purge ;
drop table yy purge ;
drop table zz purge ;
create table zz tablespace sugar as select rownum id from dual connect by level<=2;
ALTER TABLE zz MINIMIZE RECORDS_PER_BLOCK ;
SCOTT@book> select * from DBA_FREE_SPACE where tablespace_name='SUGAR';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
SUGAR 6 6 1040154624 126972 6
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253823;
insert into zz select rownum+2 id from dual connect by level<=253823
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
SCOTT@book> truncate table zz;
Table truncated.
SCOTT@book> insert into zz select rownum+2 id from dual connect by level<=253822;
253822 rows created.
SCOTT@book> commit ;
Commit complete.
--也就是将有一些块存在其他用途。
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 4 ;
System altered.
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 63487 #blocks: 126911
last map 0x0181e994 #maps: 62 offset: 4128
Highwater:: 0x0181f002 ext#: 63486 blk#: 2 ext size: 2
#blocks in seg. hdr's freelists: 1
#blocks below: 126911
mapblk 0x0181e994 offset: 822
Unlocked
Map Header:: next 0x018003f6 #extents: 505 obj#: 88929 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
--噢，忽略一个问题，如果DATA SEGMENT HEADER - UNLIMITED写满了，要通过增加块来建立map。
SCOTT@book> @ &r/dfb16 0x018003f6
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
6 1014 alter system dump datafile 6 block 1014 ;
SCOTT@book> alter system dump datafile 6 block 1014 ;
System altered.
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 63487 #blocks: 126911
last map 0x0181e994 #maps: 62 offset: 4128
Highwater:: 0x0181f002 ext#: 63486 blk#: 2 ext size: 2
#blocks in seg. hdr's freelists: 1
#blocks below: 126911
mapblk 0x0181e994 offset: 822
Unlocked
Map Header:: next 0x018003f6 #extents: 505 obj#: 88929 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01800005 length: 1
0x01800006 length: 2
0x01800008 length: 2
....
SCOTT@book> alter system dump datafile 6 block 1014 ;
System altered.
*** 2016-11-08 12:06:15.471
Start dump data blocks tsn: 7 file#:6 minblk 1014 maxblk 1014
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=25166838
Block dump from disk:
buffer tsn: 7 rdba: 0x018003f6 (6/1014)
scn: 0x0000.005d06f6 seq: 0x01 flg: 0x04 tail: 0x06f61201
frmt: 0x02 chkval: 0xf6ba type: 0x12=EXTENT MAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F2D432E8200 to 0x00007F2D432EA200
7F2D432E8200 0000A212 018003F6 005D06F6 04010000 [..........].....]
7F2D432E8210 0000F6BA 000003FB 01800BEC 00015B61 [............a[..]
7F2D432E8220 10000000 018003F7 00000001 018003F8 [................]
7F2D432E8230 00000002 018003FA 00000002 018003FC [................]
...
7F2D432EA1F0 00000002 01800BEA 00000002 06F61201 [................]
EMB Dump:
Map Header:: next 0x01800bec #extents: 1019 obj#: 88929 flag: 0x10000000
Extent Map
-----------------------------------------------------------------
-- dba = 6,4 #extents: 505 ,而dba = 6 ,1014 #extents: 1019
-- 平均以1000个extents计算。
126972/1000/2=63.486 大约64.
126972 - 64=126908
126908*2=253816
--而实际能插入253822。基本接近了。
转储alter system dump datafile 6 block 2;显示：
Initial Area: 2, Tail: 126977, First: 0, Free: 0
SCOTT@book> select * from (select * from dba_extents where file_id=6 order by block_id desc) where rownum=1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT ZZ TABLE SUGAR 63486 6 126976 16384 2 6
--这样126978 仅仅剩下一块不够扩展.忽略转储信息,少算了3块：
RelFno: 6, BeginBlock: 4, Flag: 0, First: 1, Free: 63487
--63488*2=126976
--126976+4-1=126979
--126979*8192/1024=1015832.00000000000000000000
--1015824+8*3=1015848
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015848K;
Database altered.
--正常可以插入6条记录。
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
insert into zz select 1e5+rownum id from dual connect by level<=2
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.ZZ by 2 in tablespace SUGAR
--可以发现仅仅插入4条。
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=4;
4 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system dump datafile 6 block min 126978 block max 126981;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55794.trc
buffer tsn: 7 rdba: 0x0181f002 (6/126978)
frmt: 0x02 chkval: 0xb7e7 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f003 (6/126979)
frmt: 0x02 chkval: 0x1d30 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f004 (6/126980)
frmt: 0x02 chkval: 0x5685 type: 0x00=unknown
buffer tsn: 7 rdba: 0x0181f005 (6/126981)
frmt: 0x02 chkval: 0x5684 type: 0x00=unknown
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126981, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126979, First: 63488, Free: 0
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--可以发现最后2块无法使用。再增加1个extent。
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 1015864K;
Database altered.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block min 126980 block max 126983;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55897.trc
buffer tsn: 7 rdba: 0x0181f004 (6/126980)
frmt: 0x02 chkval: 0x20b9 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f005 (6/126981)
frmt: 0x02 chkval: 0xf034 type: 0x06=trans data
buffer tsn: 7 rdba: 0x0181f006 (6/126982)
frmt: 0x02 chkval: 0x4e3b type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 7 rdba: 0x0181f007 (6/126983)
frmt: 0x02 chkval: 0x5686 type: 0x00=unknown
--你可以发现dba= 6,126982 对于的是KTFB Bitmapped File Space Bitmap。
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 126983, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 126978
Initial Area: 2, Tail: 126981, First: 63489, Free: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--你可以发现现Tail: 126981. 后面的块是位图区。
5.继续测试：
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' AUTOEXTEND ON NEXT 16K MAXSIZE UNLIMITED;
Database altered.
SCOTT@book> insert into zz select 1e5+rownum id from dual connect by level<=2;
2 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 6 block 2;
System altered.
File Space Header Block:
Header Control:
RelFno: 6, Unit: 2, Size: 133335, Flag: 9
AutoExtend: YES, Increment: 2, MaxSize: 4194302
Initial Area: 2, Tail: 133333, First: 63490, Free: 3175
Deallocation scn: 6095082.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 7 file#: 6 minblk 2 maxblk 2
--可以发现tail:133333.
SCOTT@book> alter system dump datafile 6 block 126982;
System altered.
SCOTT@book> alter system dump datafile 6 block 133334;
System altered.
$ egrep 'type: 0x|buffer tsn' /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55897.trc
buffer tsn: 7 rdba: 0x0181f006 (6/126982)
frmt: 0x02 chkval: 0xce13 type: 0x06=trans data
buffer tsn: 7 rdba: 0x018208d6 (6/133334)
frmt: 0x02 chkval: 0xb6e8 type: 0x1e=KTFB Bitmapped File Space Bitmap
--你可以发现位图区变成了dba=6,133334,而dba=6,126982,变成了存放数据。
6.总结：
1.测试还是乱。
2.在8k数据块下，最小的数据文件是40k，SEGMENT SPACE MANAGEMENT MANUAL，并且最小EXTENT MANAGEMENT LOCAL UNIFORM SIZE＝16K。
3.这样在位图区使用完后，随着数据文件加大，位图头块记录的tail变大，后面总有1点剩余作为位图区。
4.EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('SUGAR'); 会把位图区置为1，但是视乎对应用没有什么影响。
