表空間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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- java中size()和length區別Java
- 網站空間和伺服器在做站上的區別網站伺服器
- mysql 空值（null）和空字元（''）的區別MySqlNull字元
- 16、表空間 建立表空間
- 2.5.9.1 指定預設表空間的型別型別
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 臨時表空間和回滾表空間使用率查詢
- Oracle OCP(46)：表空間、段、區、塊Oracle
- 表空間利用率及表空間的補充
- KingbaseES的表空間
- 表空間和資料檔案的管理
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- Java中 length、length()、size()區別Java
- 移動分割槽表和分割槽索引的表空間索引
- 2.5.9.2 重寫預設表空間型別型別
- 靜態方法和普通的成員方法||分配記憶體空間的區別記憶體
- @Bean和@Component之間的區別?Bean
- querySelector和getElementById之間的區別
- CST和GMT時間的區別
- 敏捷和 Scrum 之間的區別敏捷Scrum
- oracle表空間的整理Oracle
- golang nil 切片和空切片區別Golang
- Oracle表空間Oracle
- oracle 表空間Oracle
- PostgreSQL 表空間SQL
- PostgreSQL：表空間SQL
- oracle 建立表空間和使用者Oracle
- Oracle建立表空間和使用者Oracle
- 4.2.1.7 規劃 SYSTEM 和 SYSAUX 表空間UX
- Hive內部表和外部表的區別Hive
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- UNDO表空間空間回收及切換
- Ora-01536：超出了表空間users的空間限量
- MVC，MVP和MVVM之間的區別MVCMVPMVVM
- python和GO語言之間的區別!PythonGo
- return,continue和break之間的區別
- MySQL UNDO表空間獨立和截斷MySql
- undo表空間容量