[20150113]系統管理表空間的疑問2.txt

[20150113]系統管理表空間的疑問2.txt

--昨天探究系統管理表空間點陣圖區分佈的問題。
--自己得到一些結論：

http://blog.itpub.net/267265/viewspace-1399275/

總結：
1.使用系統管理表空間，點陣圖區不僅僅在塊開始的2－8塊(10g)。11g沒有問題，因為11g資料檔案前面128塊保留。
2.點陣圖區除了點陣圖資訊，還有其他一些資訊.
3.如果前面的點陣圖區不夠滿足需要，從block=2的tail+1作為點陣圖區
4.如果資料檔案改變大小，如果尾部存在點陣圖區，會出現點陣圖區移動的情況(除非你僅僅改變減少增加幾個塊,這個留給大家測試)。
5.改變減少增加幾個塊，可能出現點陣圖重疊的問題，出現ORA-03215錯誤。

$ oerr ora 3215
03215, 00000, "File Size specified for resize is too small "
// *Cause: File Size specified for resize datafile/tempfile causes
//         bitmap control structures to overlap
// *Action: Increase the specification for file size

--我當時還得到一個結論，如果設定
檔案大小改變：
ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
--這樣後面的3個塊正好夠放餘下的點陣圖區，需要3塊，位於塊4194297，4194298，4194299。

--當前情況如下：
ALTER DATABASE DATAFILE 54 RESIZE 33554320K;
alter system dump datafile 54  block  2;

Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db19fc66 seq: 0x01 flg: 0x04 tail: 0xfc661d01
frmt: 0x02 chkval: 0x90bf type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B0F3400 to 0x000000001B0F5400
01B0F3400 0000A21D 0D800002 DB19FC66 04010002  [........f.......]
01B0F3410 000090BF 00000036 00000008 003FFFF2  [....6.........?.]
01B0F3420 00000001 00000000 00000000 00000007  [................]
01B0F3430 003FFFE8 00000000 000002CC 00000000  [..?.............]
01B0F3440 00000000 00000000 00000000 00000000  [................]
01B0F3450 003FFFE9 00000003 00000000 00000000  [..?.............]
01B0F3460 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
01B0F53F0 00000000 00000000 00000000 FC661D01  [..............f.]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194290, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194280, First: 0, Free: 716
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2

--我們可以看到點陣圖區位於4194281,4194282,4194283.

--如果我修改33554392 -8 =33554384k,這樣結尾僅僅剩餘2塊，是無法容納點陣圖區，看看oracle是如何操作的?

SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554384K;
Database altered.

SYS@xxxxx> alter system dump datafile 54  block  2;
System altered.

Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db459295 seq: 0x01 flg: 0x00 tail: 0x92951d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB459295 00010002  [..........E.....]
01B31C410 00000000 00000036 00000008 003FFFFA  [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007  [................]
01B31C430 003FFFF0 00000000 000002CD 00000000  [..?.............]
01B31C440 00000000 00000000 00000000 A9010000  [................]
01B31C450 003FFFF1 00000003 00000000 00000000  [..?.............]
01B31C460 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
01B31E3F0 00000000 00000000 00000000 92951D01  [................]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194298, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194288, First: 0, Free: 717
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2

--可以發現點陣圖區發生了改變，但是並沒有移動到最後，tail:4194288。點陣圖區4194289，4194290，4194291

SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554392K;
Database altered.

SYS@xxxxx> alter system dump datafile 54  block  2;
System altered.

*** 2015-01-13 10:04:34.379
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db45dfc4 seq: 0x01 flg: 0x00 tail: 0xdfc41d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB45DFC4 00010002  [..........E.....]
01B31C410 00000000 00000036 00000008 003FFFFB  [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007  [................]
01B31C430 003FFFF8 00000000 000002CE 00000000  [..?.............]
01B31C440 00000000 00000000 00000000 A9010000  [................]
01B31C450 003FFFF9 00000003 00000000 00000000  [..?.............]
01B31C460 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
01B31E3F0 00000000 00000000 00000000 DFC41D01  [................]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194299, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194296, First: 0, Free: 718
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2

--僅僅資料檔案增加8k，這樣結尾正好能非放下3塊。點陣圖區4194297，4194298，4194299.從檢視也可以確定。

SYS@xxxxx> select * from dba_data_files where file_id=54;
FILE_NAME                                                         FILE_ID TABLESPACE_NAME                       BYTES       BLOCKS STATUS    RELATIVE_FNO AUT     MAXBYTES    MAXBLOCKS INCREMENT_BY   USER_BYTES  USER_BLOCKS ONLINE_
------------------------------------------------------------ ------------ ------------------------------ ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
+G0/xxxxx/datafile/xxxxx_check_lis.20490.852033985                     54 xxxxx_CHECK_LIS                 34359697408      4194299 AVAILABLE           54 NO             0            0            0  34359607296      4194288 ONLINE

--BLOCKS =4194299.

--也就是資料檔案大小改變時結尾要保留放下點陣圖區的空間，後面的空間就浪費了。

--減少8k時：
SYS@xxxxx> ALTER DATABASE DATAFILE 54 RESIZE 33554384K;
Database altered.

SYS@xxxxx> alter system dump datafile 54  block  2;
System altered.

*** 2015-01-13 10:12:31.841
Start dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2
buffer tsn: 18 rdba: 0x0d800002 (54/2)
scn: 0x0002.db46da1f seq: 0x01 flg: 0x00 tail: 0xda1f1d01
frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001B31C400 to 0x000000001B31E400
01B31C400 0000A21D 0D800002 DB46DA1F 00010002  [..........F.....]
01B31C410 00000000 00000036 00000008 003FFFFA  [....6.........?.]
01B31C420 00000001 00000000 00000000 00000007  [................]
01B31C430 003FFFF0 00000000 000002CD 00000000  [..?.............]
01B31C440 00000000 00000000 00000000 00000000  [................]
01B31C450 003FFFF1 00000003 00000000 00000000  [..?.............]
01B31C460 00000000 00000000 00000000 00000000  [................]
        Repeat 504 times
01B31E3F0 00000000 00000000 00000000 DA1F1D01  [................]
File Space Header Block:
Header Control:
RelFno: 54, Unit: 8, Size: 4194298, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 4194288, First: 0, Free: 717
Deallocation scn: 0.0
Header Opcode:
Save: No Pending Op
End dump data blocks tsn: 18 file#: 54 minblk 2 maxblk 2

SYS@xxxxx> select * from dba_data_files where file_id=54;
FILE_NAME                                                         FILE_ID TABLESPACE_NAME                       BYTES       BLOCKS STATUS    RELATIVE_FNO AUT     MAXBYTES    MAXBLOCKS INCREMENT_BY   USER_BYTES  USER_BLOCKS ONLINE_
------------------------------------------------------------ ------------ ------------------------------ ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
+G0/xxxxx/datafile/xxxxx_check_lis.20490.852033985                     54 xxxxx_CHECK_LIS                 34359689216      4194298 AVAILABLE           54 NO             0            0            0  34359541760      4194280 ONLINE

--BLOCKS=4194298.點陣圖區4194289，4194290，4194291, 後面的4194292－4194298就浪費了，當然這點空間根本不算什麼。

----對於asm就不算什麼，因為aus＝1M。