UNIFORM SIZE的測試
SQL> CREATE TABLESPACE eric DATAFILE
2 '/u01/app/oradata/orcl/eric01.dbf' SIZE 10M AUTOEXTEND OFF
3 LOGGING
4 PERMANENT
5 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
6 BLOCKSIZE 8K
7 SEGMENT SPACE MANAGEMENT MANUAL
8 FLASHBACK ON;
Tablespace created.
SQL> select tablespace_name,extent_management,allocation_type
2 from dba_tablespaces where tablespace_name='ERIC';
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
ERIC LOCAL UNIFORM
SQL> create table test
2 tablespace eric as
3 select * from dba_users where 1=0;
Table created.
SQL> select extent_id,block_id,blocks
2 from dba_extents where segment_name='TEST' and tablespace_name='ERIC';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 128
test表創(chuàng)建時(shí)是從block_id為9的開始的,前8個(gè)block都被系統(tǒng)保留了.其中數(shù)據(jù)塊1和2用于記錄數(shù)據(jù)文件頭的信息,數(shù)據(jù)塊
3~8用于記錄區(qū)間的位圖信息.
SQL> alter system dump datafile 9 block min 1 block max 3;
System altered.
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p ,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/admin/orcl/udump/orcl_ora_3868.trc
根據(jù)生成的跟蹤文件,可以發(fā)現(xiàn)前兩個(gè)block記錄的是文件頭的信息
/u01/app/admin/orcl/udump/orcl_ora_3868.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/product/10201/
System name: Linux
Node name: rhel131
Release: 2.6.9-42.ELsmp
Version: #1 SMP Wed Jul 12 23:27:17 EDT 2006
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3868, image:
oracle@rhel131
(TNS V1-V3)
*** 2008-10-22 03:22:09.566
*** ACTION NAME:() 2008-10-22 03:22:09.564
*** MODULE NAME:(
sqlplus@rhel131
(TNS V1-V3)) 2008-10-22 03:22:09.564
*** SERVICE NAME:(SYS$USERS) 2008-10-22 03:22:09.564
*** SESSION ID:(159.6) 2008-10-22 03:22:09.564
Start dump data blocks tsn: 9 file#: 9 minblk 1 maxblk 3
Block 1 (file header) not dumped: use dump file header command
buffer tsn: 9 rdba: 0x02400002 (9/2)
scn: 0x0000.001398aa seq: 0x02 flg: 0x00 tail: 0x98aa1d02
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21D 02400002 001398AA 00020000 [......@.........]
CEB6810 00000000 00000009 00000080 00000500 [................]
CEB6820 00000001 00000000 00000000 00000007 [................]
CEB6830 00000488 00000001 00000008 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
CEB6850 00000009 00000080 00000000 00000000 [................]
CEB6860 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
CEB87F0 00000000 00000000 00000000 98AA1D02 [................]
File Space Header Block:
Header Control:
RelFno: 9, Unit: 128, Size: 1280, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 1160, First: 1, Free: 8
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.001398aa seq: 0x01 flg: 0x00 tail: 0x98aa1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 001398AA 00010000 [......@.........]
CEB6810 00000000 00000009 00000009 00000000 [................]
CEB6820 00000001 0000F7FF 00000000 00000000 [................]
CEB6830 00000000 00000000 00000001 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 98AA1E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
從第3個(gè)塊開始,記錄了位圖的信息.
01是16進(jìn)制轉(zhuǎn)換為二進(jìn)制就是0000 0001,經(jīng)過高位低位的字節(jié)交換得出的結(jié)果是1000 0000(linix平臺(tái)為little-Endian),
表示分配了一個(gè)區(qū)間
下面再分配一個(gè)區(qū)間
SQL> alter table test allocate extent;
Table altered.
此時(shí)的位圖變成了:
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.00139a4b seq: 0x01 flg: 0x00 tail: 0x9a4b1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 00139A4B 00010000 [......@.K.......]
CEB6810 00000000 00000009 00000009 00000000 [................]
CEB6820 00000002 0000F7FE 00000000 00000000 [................]
CEB6830 00000000 00000000 00000003 00000000 [................]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 9A4B1E01 [..............K.]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 2, Free: 63486
0300000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
03代表0000 0011,經(jīng)過高低位的轉(zhuǎn)換也就是1100 0000,代表前兩個(gè)區(qū)間被分配了.
在這個(gè)數(shù)據(jù)文件里再建立一個(gè)對(duì)象,共分配四個(gè)區(qū)間
SQL> create table test2 tablespace eric
2 as select * from dba_objects where 1=2;
Table created.
SQL> alter table test2 allocate extent;
Table altered.
SQL> /
Table altered.
SQL> /
Table altered.
SQL> col segment_name for a10
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST2 0 265 128
TEST2 1 393 128
TEST2 2 521 128
TEST2 3 649 128
6 rows selected.
再轉(zhuǎn)儲(chǔ)文件頭,區(qū)間位圖已變化為:
buffer tsn: 9 rdba: 0x02400003 (9/3)
scn: 0x0000.00139d3d seq: 0x01 flg: 0x04 tail: 0x9d3d1e01
frmt: 0x02 chkval: 0x4d8d type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CEB6800 to 0x0CEB8800
CEB6800 0000A21E 02400003 00139D3D 04010000 [......@.=.......]
CEB6810 00004D8D 00000009 00000009 00000000 [.M..............]
CEB6820 00000006 0000F7FA 00000000 00000000 [................]
CEB6830 00000000 00000000 0000003F 00000000 [........?.......]
CEB6840 00000000 00000000 00000000 00000000 [................]
Repeat 506 times
CEB87F0 00000000 00000000 00000000 9D3D1E01 [..............=.]
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 6, Free: 63482
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
3F轉(zhuǎn)換為二進(jìn)制是0011 1111,高低位轉(zhuǎn)換結(jié)果是1111 1100,也就是對(duì)應(yīng)了前6個(gè)區(qū)間被分配了
再建立一個(gè)對(duì)象.
SQL> create table test3 tablespace eric
2 as select * from dba_objects where 1=2;
Table created.
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST2 0 265 128
TEST2 1 393 128
TEST2 2 521 128
TEST2 3 649 128
TEST3 0 777 128
7 rows selected.
此時(shí)的位圖變化為:
/u01/app/admin/orcl/udump/orcl_ora_3894.trc
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481
7F00000000000000 0000000000000000 0000000000000000 0000000000000000
7F轉(zhuǎn)換為二進(jìn)制為0111 1111,經(jīng)過位轉(zhuǎn)換結(jié)果為1111 1110 ,前7個(gè)區(qū)已分配.
下面drop掉test2表看看
SQL> drop table test2;
Table dropped.
SQL> col segment_name for a10
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=9;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST 0 9 128
TEST 1 137 128
TEST3 0 777 128
奇怪了,怎么還是7F?跟drop前是一樣的.
/u01/app/admin/orcl/udump/orcl_ora_3897.trc
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 7, Free: 63481
7F00000000000000 0000000000000000 0000000000000000 0000000000000000
下面比較了一下最后兩后的trace文件,也沒有發(fā)現(xiàn)不同點(diǎn).
[oracle@rhel131 ~]$ diff /u01/app/admin/orcl/udump/orcl_ora_3894.trc
/u01/app/admin/orcl/udump/orcl_ora_3897.trc
1c1
< /u01/app/admin/orcl/udump/orcl_ora_3894.trc
---
> /u01/app/admin/orcl/udump/orcl_ora_3897.trc
13c13
< Unix process pid: 3894, image:
oracle@rhel131
(TNS V1-V3)
---
> Unix process pid: 3897, image:
oracle@rhel131
(TNS V1-V3)
15,19c15,19
< *** 2008-10-22 03:46:04.029
< *** ACTION NAME:() 2008-10-22 03:46:04.028
< *** MODULE NAME:(
sqlplus@rhel131
(TNS V1-V3)) 2008-10-22 03:46:04.028
< *** SERVICE NAME:(SYS$USERS) 2008-10-22 03:46:04.028
< *** SESSION ID:(146.56) 2008-10-22 03:46:04.028
---
> *** 2008-10-22 03:47:50.875
> *** ACTION NAME:() 2008-10-22 03:47:50.874
> *** MODULE NAME:(
sqlplus@rhel131
(TNS V1-V3)) 2008-10-22 03:47:50.874
> *** SERVICE NAME:(SYS$USERS) 2008-10-22 03:47:50.874
> *** SESSION ID:(146.61) 2008-10-22 03:47:50.874
終于找到原因了，是10g recyclebin引起的。
在drop test2時(shí)，沒有清空回收站的信息，所以在文件頭還是會(huì)記錄空間已分配，試了另外一個(gè)表就知道原因了。
SQL> drop table test purge;
Table dropped.
再來看dump出來的文件
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63483
7C00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
變成7C了,轉(zhuǎn)成二進(jìn)制是0111 1100,高低位轉(zhuǎn)換后就成為0011 1110
再來purge test2.
SQL> purge table test2;
Table purged.
dump出來的信息為:
File Space Bitmap Block:
BitMap Control:
RelFno: 9, BeginBlock: 9, Flag: 0, First: 0, Free: 63487
4000000000000000 0000000000000000 0000000000000000 0000000000000000
變成40了,轉(zhuǎn)成二進(jìn)制是0100 0000,高低位轉(zhuǎn)換后就成為0000 0010了.
下面是autoallocate的測試:
SQL> CREATE TABLESPACE eric2 DATAFILE
2 '/u01/app/oradata/orcl/eric2.dbf' SIZE 500M AUTOEXTEND OFF
3 LOGGING
4 PERMANENT
5 EXTENT MANAGEMENT LOCAL autoallocate
6 BLOCKSIZE 8K
7 SEGMENT SPACE MANAGEMENT MANUAL
8 FLASHBACK ON;
Tablespace created.
SQL> select tablespace_name,extent_management,allocation_type
2 from dba_tablespaces where tablespace_name='ERIC2';
TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
ERIC2 LOCAL SYSTEM
allocation_type是SYSTEM,表示是系統(tǒng)自動(dòng)分配extent.
SQL> create table test4
2 tablespace eric2 as
3 select * from dba_objects where 1=0;
Table created.
SQL> col segment_name for a10;
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=10;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 0 9 8
SQL> alter system dump datafile 10 block min 1 block max 3;
File Space Bitmap Block:
BitMap Control:
RelFno: 10, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
0100000000000000 0000000000000000 0000000000000000 0000000000000000
01轉(zhuǎn)換成二進(jìn)制是0000 0001,高低位轉(zhuǎn)換后為1000 0000
由于是自動(dòng)管理的區(qū)間,這里的1并不能代表分配了一個(gè)區(qū)間,只說代表分配了64K大小,因?yàn)閰^(qū)間尺寸可能是
64K,1MB,8MB,64M或更大,不過都有一個(gè)通用尺寸64KB,所以64KB就是該表空間的一個(gè)位標(biāo)記大小.
下面插入一些數(shù)據(jù),讓其自動(dòng)分配extent,發(fā)現(xiàn)前16個(gè)extent是64K,后面是分配的1M.
SQL> insert into test4
2 select * From dba_objects;
51177 rows created.
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=10;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 0 9 8
TEST4 1 17 8
TEST4 2 25 8
TEST4 3 33 8
TEST4 4 41 8
TEST4 5 49 8
TEST4 6 57 8
TEST4 7 65 8
TEST4 8 73 8
TEST4 9 81 8
TEST4 10 89 8
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 11 97 8
TEST4 12 105 8
TEST4 13 113 8
TEST4 14 121 8
TEST4 15 129 8
TEST4 16 137 128
TEST4 17 265 128
TEST4 18 393 128
TEST4 19 521 128
TEST4 20 649 128
21 rows selected.
此時(shí)DUMP出來的數(shù)據(jù)是:
SQL> alter system dump datafile 10 block min 1 block max 3;
File Space Bitmap Block:
BitMap Control:
RelFno: 10, BeginBlock: 9, Flag: 0, First: 96, Free: 63392
FFFFFFFFFFFFFFFF FFFFFFFF00000000 0000000000000000 0000000000000000
可以看到分配了96個(gè)位(bit),但并不表示分配了96個(gè)extent.這里前兩個(gè)FF代表分配了16個(gè)64KB的extent.剩下的80個(gè)位
(96-16)表示1024Kb/64Kb*5 extent=80,表示每個(gè)位的大小是64K.
下面不斷擴(kuò)展這個(gè)表,看看自動(dòng)分配區(qū)的變化.
SQL> insert into test4
2 select * From dba_objects;
51177 rows created.
SQL> /
51177 rows created.
SQL> /
51177 rows created.
SQL> insert into test4
2 select * From test4;
204708 rows created.
SQL> /
409416 rows created.
SQL> col segment_name for a10;
SQL> select segment_name,extent_id,block_id,blocks
2 from dba_extents where file_id=10;
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 0 9 8
TEST4 1 17 8
TEST4 2 25 8
TEST4 3 33 8
TEST4 4 41 8
TEST4 5 49 8
TEST4 6 57 8
TEST4 7 65 8
TEST4 8 73 8
TEST4 9 81 8
TEST4 10 89 8
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 11 97 8
TEST4 12 105 8
TEST4 13 113 8
TEST4 14 121 8
TEST4 15 129 8
TEST4 16 137 128
TEST4 17 265 128
TEST4 18 393 128
TEST4 19 521 128
TEST4 20 649 128
TEST4 21 777 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 22 905 128
TEST4 23 1033 128
TEST4 24 1161 128
TEST4 25 1289 128
TEST4 26 1417 128
TEST4 27 1545 128
TEST4 28 1673 128
TEST4 29 1801 128
TEST4 30 1929 128
TEST4 31 2057 128
TEST4 32 2185 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 33 2313 128
TEST4 34 2441 128
TEST4 35 2569 128
TEST4 36 2697 128
TEST4 37 2825 128
TEST4 38 2953 128
TEST4 39 3081 128
TEST4 40 3209 128
TEST4 41 3337 128
TEST4 42 3465 128
TEST4 43 3593 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 44 3721 128
TEST4 45 3849 128
TEST4 46 3977 128
TEST4 47 4105 128
TEST4 48 4233 128
TEST4 49 4361 128
TEST4 50 4489 128
TEST4 51 4617 128
TEST4 52 4745 128
TEST4 53 4873 128
TEST4 54 5001 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 55 5129 128
TEST4 56 5257 128
TEST4 57 5385 128
TEST4 58 5513 128
TEST4 59 5641 128
TEST4 60 5769 128
TEST4 61 5897 128
TEST4 62 6025 128
TEST4 63 6153 128
TEST4 64 6281 128
TEST4 65 6409 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 66 6537 128
TEST4 67 6665 128
TEST4 68 6793 128
TEST4 69 6921 128
TEST4 70 7049 128
TEST4 71 7177 128
TEST4 72 7305 128
TEST4 73 7433 128
TEST4 74 7561 128
TEST4 75 7689 128
TEST4 76 7817 128
SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
TEST4 77 7945 128
TEST4 78 8073 128
TEST4 79 8201 1024
TEST4 80 9225 1024
TEST4 81 10249 1024
82 rows selected.
As you can see in this example, the first 16 extents each are 64KB . The next 63 are each 1MB, and the
remaining are 8MB.
另外一點(diǎn)需要說明的是,如果此時(shí)rollback,那么已分配的區(qū)塊是不會(huì)自動(dòng)回收的,不過再有新的資料插入時(shí)會(huì)先使用這些已
分配的區(qū).
refer from:http://jolly10.itpub.net/post/7268/472662
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
