表空間uniform size和 autoallocate的區別
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表建立時是從block_id為9的開始的,前8個block都被系統保留了.其中資料塊1和2用於記錄資料檔案頭的資訊,資料塊
3~8用於記錄區間的點陣圖資訊.
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
根據生成的跟蹤檔案,可以發現前兩個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: (TNS V1-V3)
*** 2008-10-22 03:22:09.566
*** ACTION NAME:() 2008-10-22 03:22:09.564
*** MODULE NAME:( (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個塊開始,記錄了點陣圖的資訊.
01是16進位制轉換為二進位制就是0000 0001,經過高位低位的位元組交換得出的結果是1000 0000(linix平臺為little-Endian),
表示分配了一個區間
下面再分配一個區間
SQL> alter table test allocate extent;
Table altered.
此時的點陣圖變成了:
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,經過高低位的轉換也就是1100 0000,代表前兩個區間被分配了.
在這個資料檔案裡再建立一個物件,共分配四個區間
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.
再轉儲檔案頭,區間點陣圖已變化為:
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轉換為二進位制是0011 1111,高低位轉換結果是1111 1100,也就是對應了前6個區間被分配了
再建立一個物件.
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.
此時的點陣圖變化為:
/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轉換為二進位制為0111 1111,經過位轉換結果為1111 1110 ,前7個區已分配.
下面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檔案,也沒有發現不同點.
[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: (TNS V1-V3)
---
> Unix process pid: 3897, image: (TNS V1-V3)
15,19c15,19
< *** 2008-10-22 03:46:04.029
< *** ACTION NAME:() 2008-10-22 03:46:04.028
< *** MODULE NAME:( (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:( (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時，沒有清空回收站的資訊，所以在檔案頭還是會記錄空間已分配，試了另外一個表就知道原因了。
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了,轉成二進位制是0111 1100,高低位轉換後就成為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了,轉成二進位制是0100 0000,高低位轉換後就成為0000 0010了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31520497/viewspace-2156874/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle11g之create table儲存引數initial和表空間autoallocate或uniform的關係OracleORM
- local管理的表空間下autoallocate方式extent的分配原則
- 表空間自動擴充套件 AUTOALLOCATE 的擴充套件規律套件
- Oracle——01表空間和區Oracle
- local + uniform size 設定ORM
- 單例項和RAC下建立表空間的小區別單例
- 表空間監控（二）datafile size detailAI
- Oracle的邏輯結構(表空間、段、區間、塊)——表空間Oracle
- maxlength和size屬性區別
- java中size()和length區別Java
- 刪除表空間和表空間包含的檔案
- 表空間OFFLINE和資料檔案OFFLINE的區別
- Oracle表空間、段、區和塊簡述Oracle
- size resize與capacity reserve之間的區別
- SIZE AUTO和SIZE SKEWONLY在gather_table_stats時的區別
- MySQL InnoDB 共享表空間和獨立表空間MySql
- MySQL InnoDB 共享表空間和獨立表空間MySql
- Shell磁碟空間和表空間告警程式
- 網站空間和伺服器在做站上的區別網站伺服器
- 關於排序、sort_area_size、臨時表空間（轉）排序
- 表空間離線與資料檔案離線的區別
- 查詢表空間已使用空間和空閒空間的簡單檢視
- mysql 空值（null）和空字元（''）的區別MySqlNull字元
- 關於表空間和表的關係
- 資料檔案、表空間offline用法及區別
- 如何處理表空間級別，表級別，索引級別的碎片索引
- MySQL innoDB獨立表空間和共享表空間的優點和缺點介紹MySql
- Oracle修改預設表空間和預設臨時表空間Oracle
- 2.5.9.1 指定預設表空間的型別型別
- List isEmpty()和size()==0 有什麼區別
- 管理表空間（表空間的屬性）轉貼
- 表空間查詢和管理
- 表空間offline,資料檔案offline 的區別(ZT)
- Oracle OCP(46)：表空間、段、區、塊Oracle
- 表空間、段、區、塊的相關總結
- 臨時表空間和回滾表空間使用率查詢
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 分析表空間空閒率並收縮表空間