本地管理表空间(LMT)
一、概述
二、名词解释与约定
三、本地管理表空间的由来
四、本地管理表空间管理机制
SQL> desc UET$
Name Null? Type
----------------- ---------- -----------
SEGFILE# NOT NULL NUMBER
SEGBLOCK# NOT NULL NUMBER | The segment that uses this space
EXT# NOT NULL NUMBER
TS# NOT NULL NUMBER | The tablespace ID and the file
FILE# NOT NULL NUMBER | ID for that tablespace
BLOCK# NOT NULL NUMBER
LENGTH NOT NULL NUMBER | The location and size of the chunk
Name
----------------- ---------- -----------
SEGFILE#
SEGBLOCK#
EXT#
TS#
FILE#
BLOCK#
LENGTH
SQL> desc FET$
Name Null? Type
----------------- ---------- -----------
TS# NOT NULL NUMBER | The tablespace ID and the file
FILE# NOT NULL NUMBER | ID for that tablespace
BLOCK# NOT NULL NUMBER
LENGTH NOT NULL NUMBER | The location and size of the chunk
Name
----------------- ---------- -----------
TS#
FILE#
BLOCK#
LENGTH
Uniform方式的本地管理表空间
1、先创建了一个本地管理的表空间,区间统一大小分配为64K
SQL> create tablespace demo
2 datafile '/ora01/oem/oemdemo01.dbf' size 10m
3 extent management local uniform size 64k;
2
3
2、在该表空间中创建一个表
SQL>create table demotab ( x number ) tablespace demo
2 storage ( initial 1000K next 1000k );
2
我们通过查询该表
SQL> select t.table_name,t.initial_extent,t.next_extent from user_tables t where t.table_name = 'DEMOTAB';
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------------------------- -------------- -----------
DEMOTAB 1024000 65536
TABLE_NAME
---------------------------- -------------- -----------
DEMOTAB
可以发现,该表的存储参数并不是我们指定的参数INITIAL_EXTENT,而是uniform size的整数倍,NEXT_EXTENT则等于uniform size。我们从该查询就也可以看到如下情况
SQL>select count(*) from user_extents where segment_name = 'DEMOTAB';
COUNT(*)
----------
16
COUNT(*)
----------
也就是说,该表在该表空间中已经存在16个extent,而不是一个extent(这是与字典管理的差别,如果是字典管理的表空间,如果创建以上的表,该查询的结果是1)
3、获取该数据文件的文件ID
SQL> col name format a30 trunc
SQL> select file#, name from v$datafile;
SQL> select file#, name from v$datafile;
File# NAME
----- --------------------
1 /oras1/oem/oemsystem01.dbf
2 /oras3/oem/oemundo01.dbf
3 /ora01/oem/oemoem_repository01
4 /ora01/oem/oemrcat01.dbf
5 /ora01/oem/oemdemo01.dbf
-----
我们可以检查uet$与fet$
SQL> select count(*) from uet$ where file# = 5;
COUNT(*)
----------
0
----------
SQL> select count(*) from fet$ where file# = 5;
COUNT(*)
----------
0
----------
4、可以看到,ORACLE没有在这两个表中保存任何信息,现在我们dump该数据文件的第三个块
SQL> alter system dump datafile 5 block 3;
System altered.
System altered.
查看DUMP文件,有如下信息
Start dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7a6f seq: 0x01 flg: 0x00 tail: 0x7a6f1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 9, Flag: 0, First: 16, Free: 63472
FFFF000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
.....
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7a6f seq: 0x01 flg: 0x00 tail: 0x7a6f1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 9, Flag: 0, First: 16, Free: 63472
FFFF000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
.....
注意其中的FFFF00,,这是16进制的表现方法,我们转换为二进制,有
1111,1111,1111,1111,0000,0000
发现这里有16个1,每一个1就是一个位(bit),代表64K,也就代表了该表空间有已经分配了的16个extent,如果我们将该表扩展,将又有什么结果呢?
SQL> alter table demotab allocate extent;
Table altered.
Table altered.
SQL> alter table demotab allocate extent;
Table altered.
Table altered.
SQL> alter table demotab allocate extent;
Table altered.
Table altered.
这样之后,我们应该有19个extent了,再dump第三个块
Start dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 9, Flag: 0, First: 19, Free: 63469
FFFF07 0000000000 0000000000000000 0000000000000000 0000000000000000
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 5, BeginBlock: 9, Flag: 0, First: 19, Free: 63469
FFFF07 0000000000 0000000000000000 0000000000000000 0000000000000000
5、同样我们dump该数据文件第9个块,则有
Start dump data blocks tsn: 5 file#: 5 minblk 9 maxblk 9
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
buffer tsn: 5 rdba: 0x01400003 (5/3)
scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
占用的空间为64K。
ORACLE通过强制性的手段使本地管理表空间中的所有Extent是同样大小的,尽管你可能自定义了不同的存储参数。
6、补充一些字典管理表空间的不同
Autoallocate的本地管理表空间
在自动分配的本地管理的表空间中,区间尺寸可能由以下尺寸组成64k, 1m, 8m, 64m 甚至是256m。但是不管多大,都有一个通用尺寸64k,所以64K就是该表空间的位大小。
SQL> create tablespace dummy
2 datafile 'c:\dummy01.dbf' size 100m
3 autoallocate;
Tablespace created.
2
3
Tablespace created.
SQL> create table x1 (x number)
2 tablespace dummy
3 storage (initial 50M);
Table created.
2
3
Table created.
SQL> select file# from v$datafile where name like '%DUMMY%';
FILE#
----------
12
----------
SQL> select extents from user_segments
where segment_name = 'X1' ;
where segment_name = 'X1' ;
EXTENTS
---------
50
---------
SQL> alter system dump datafile 12 block 3;
System altered.
System altered.
*** SESSION ID11.59) 2002-11-22 10:37:35.000
Start dump data blocks tsn: 19 file#: 12 minblk 3 maxblk 3
buffer tsn: 19 rdba: 0x03000003 (12/3)
scn: 0x0000.00f2959b seq: 0x01 flg: 0x00 tail: 0x959b1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 12, BeginBlock: 9, Flag: 0, First: 800, Free: 62688
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
Start dump data blocks tsn: 19 file#: 12 minblk 3 maxblk 3
buffer tsn: 19 rdba: 0x03000003 (12/3)
scn: 0x0000.00f2959b seq: 0x01 flg: 0x00 tail: 0x959b1e01
frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap
File Space Bitmap Block:
BitMap Control:
RelFno: 12, BeginBlock: 9, Flag: 0, First: 800, Free: 62688
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
可以看到该表实际只有50个区间(extent),但是有800个位(bit)
50*1024=800*64
还可以看出,位大小并不等于extent大小
50*1024=800*64
还可以看出,位大小并不等于extent大小
五、迁移到本地管理表空间
1、首先,我们创建一个字典管理表空间
SQL> create tablespace blah
2 datafile 'G:\ORA9I\ORADATA\DB9\BLAH.DBF' size 10m reuse
3 extent management dictionary;
Tablespace altered.
2
3
Tablespace altered.
SQL> col bytes format 999,999,999
SQL> select * from dba_free_space where tablespace_name = 'BLAH';
SQL> select * from dba_free_space where tablespace_name = 'BLAH';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO
--------------- -------- ----------- ----------- ------- ----------------
BLAH 8 2 10,477,568 1279 8
--------------- -------- ----------- ----------- ------- ----------------
BLAH
2、我们在上面创建三个表,最小公用尺寸是400K
SQL> create table t1 ( x number ) storage ( initial 400k) tablespace blah;
Table created.
Table created.
SQL> create table t2 ( x number ) storage ( initial 800k) tablespace blah;
Table created.
Table created.
SQL> create table t3 ( x number ) storage ( initial 1200k) tablespace blah;
Table created.
Table created.
SQL> select * from dba_free_space where tablespace_name = 'BLAH';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO
--------------- -------- ----------- ----------- ------- ----------------
BLAH 8 302 8,019,968 979 8
--------------- -------- ----------- ----------- ------- ----------------
BLAH
SQL> select bytes from dba_extents where tablespace_name = 'BLAH';
BYTES
----------
409,600
819,200
1,228,800
----------
409,600
819,200
1,228,800
3、现在我们开始转换该表空间为本地管理的表空间,假定每个位图大小400K,也就是50个块。
SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50);
BEGIN dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); END;
BEGIN dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); END;
*
ERROR at line 1:
ORA-03241: Invalid unit size
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
ERROR at line 1:
ORA-03241: Invalid unit size
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1
如果我们设置表空间的minimum extent语句为400K:
SQL> alter tablespace blah minimum extent 400k;
Tablespace altered.
Tablespace altered.
SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50);
PL/SQL procedure successfully completed.
Conversion goes through with no problems.
PL/SQL procedure successfully completed.
Conversion goes through with no problems.
再转一些其他的论述
*************************************************************************************
本地管理表空间与字典管理表空间的比较
1.减少了递归空间管理
2.系统自动管理extents大小或采用统一extents大小
3.减少了数据字典之间的竞争
4.不产生回退信息
5.不需合并相邻的剩余空间
6.减少了空间碎片
7.对临时表空间提供了更好的管理
*************************************************************************************
Oracle之所以推出了这种新的表空间管理方法,让我们来看一下这种表空间组织方法的优点:
*************************************************************************************