Chinaunix首页 | 论坛 | 博客
  • 博客访问: 9166826
  • 博文数量: 1669
  • 博客积分: 16831
  • 博客等级: 上将
  • 技术积分: 12594
  • 用 户 组: 普通用户
  • 注册时间: 2011-02-25 07:23
个人简介

柔中带刚,刚中带柔,淫荡中富含柔和,刚猛中荡漾风骚,无坚不摧,无孔不入!

文章分类

全部博文(1669)

文章存档

2023年(4)

2022年(1)

2021年(10)

2020年(24)

2019年(4)

2018年(19)

2017年(66)

2016年(60)

2015年(49)

2014年(201)

2013年(221)

2012年(638)

2011年(372)

分类: Oracle

2012-11-22 15:25:17

理解local management tablespace的uniform和autoallocate的分配方式  

2012-11-22 14:17:32|  分类: linux下Oracle 10 |  标签: |字号 

本文内容主要参考: 老盖的《循序渐进 Oracle数据库管理,优化与备份恢复》

以及博文 http://jolly10.itpub.net/post/7268/472662

1. 本地管理表空间的区大小分配方式

表空间的管理方式主要有:本地管理和数据字典管理。本地管理采用在位图记录区的分配情况,而数据字典管理的方式采用字典表记录区的分配情况,字典管理方式 从10g开始渐渐被淘汰。区作为基本分配单位分配给段时,其大小也有区别的。

  1. create tablespace lmt_bmb
  2. datafile 'lmt_bmb' size 100M autoextend off
  3. logging
  4. permanent
  5. extent management local uniform size 1M --uniform方式,区大小固定为1M
  6. blocksize 8k
  7. segment space management manual --段管理方式确认后,将无法修改,之能重建
  8. flashback on;
  9. create tablespace lmt_bmb_auto
  10. datafile 'lmt_bmb_auto.dbf' size 100M autoextend off
  11. logging permanent
  12. extent management local autoallocate --autoallocate方式,区大小又系统自动分配
  13. blocksize 8k
  14. segment space management manual
  15. flashback on;
create tablespace lmt_bmb datafile 'lmt_bmb' size 100M autoextend off logging permanent extent management local uniform size 1M --uniform方式,区大小固定为1M blocksize 8k segment space management manual --段管理方式确认后,将无法修改,之能重建 flashback on; create tablespace lmt_bmb_auto datafile 'lmt_bmb_auto.dbf' size 100M autoextend off logging permanent extent management local autoallocate --autoallocate方式,区大小又系统自动分配 blocksize 8k segment space management manual flashback on;uniform方式按统一的区大小分配给段,而autoallocate方式会逐渐调整分配给段的大小。通过以下语句可以查询表空间的情况:

  1. SQL> select tablespace_name, extent_management, allocation_type
  2. 2 from dba_tablespaces;
  3. TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE
  4. ------------------------------ ----------------- ---------------
  5. SYSTEM LOCAL SYSTEM
  6. SYSAUX LOCAL SYSTEM
  7. UNDOTBS1 LOCAL SYSTEM
  8. TEMP LOCAL UNIFORM
  9. USERS LOCAL SYSTEM
  10. ADDDATA01 LOCAL SYSTEM
  11. ADDDATA02 LOCAL SYSTEM
  12. ADDINDX01 LOCAL SYSTEM
  13. ADDINDX02 LOCAL SYSTEM
  14. ADDTEMP01 LOCAL UNIFORM
  15. UNDOTBS001 LOCAL SYSTEM
  16. LMTS LOCAL SYSTEM
  17. LMT_BMB LOCAL UNIFORM
  18. LMT_BMB_AUTO LOCAL SYSTEM
SQL> select tablespace_name, extent_management, allocation_type 2 from dba_tablespaces; TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE ------------------------------ ----------------- --------------- SYSTEM LOCAL SYSTEM SYSAUX LOCAL SYSTEM UNDOTBS1 LOCAL SYSTEM TEMP LOCAL UNIFORM USERS LOCAL SYSTEM ADDDATA01 LOCAL SYSTEM ADDDATA02 LOCAL SYSTEM ADDINDX01 LOCAL SYSTEM ADDINDX02 LOCAL SYSTEM ADDTEMP01 LOCAL UNIFORM UNDOTBS001 LOCAL SYSTEM LMTS LOCAL SYSTEM LMT_BMB LOCAL UNIFORM LMT_BMB_AUTO LOCAL SYSTEM

system表示autoallocate方式,后续通过dump数据文件头来了解区的位图信息,以及区分配方式。


2. Uniform方式以及数据文件头的位图信息

  1. SQL> create table test tablespace lmt_bmb as select * from dba_users where 1=0;
  2. Table created
  3. SQL> select tablespace_name, extent_id, block_id, blocks from dba_extents where tablespace_name = 'LMT_BMB';
  4. TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
  5. ------------------------------ ---------- ---------- ----------
  6. LMT_BMB 0 9 128
SQL> create table test tablespace lmt_bmb as select * from dba_users where 1=0; Table created SQL> select tablespace_name, extent_id, block_id, blocks from dba_extents where tablespace_name = 'LMT_BMB'; TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS ------------------------------ ---------- ---------- ---------- LMT_BMB 0 9 128Test表创建时是从block_id为9的开始的,前8个block都被系统保留了.其中数据块1和2用于记录数据文件头的信息,
数据块3~8用于记录区间的位图信息. 通过dump前3个块查看区分配的问题信息.

  1. SQL> select file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name = 'LMT_BMB';
  2. FILE_NAME FILE_ID BYTES/1024/1024
  3. -------------------------------------------------------------------------------- ---------- ---------------
  4. F:\PROGRAM_FILES\ORACLE\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\LMT_BMB.DBF 40 100
  5. SQL> alter system dump datafile 40 block min 1 block max 3;
  6. System altered
  7. SQL> select
  8. 2 d.value || '/' || lower (rtrim (i.instance, chr (0))) || '_ora_' || p.spid || '.trc' trace_file_name
  9. 3 from (
  10. 4 select p.spid
  11. 5 from v$mystat m, v$session s, v$process p
  12. 6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr
  13. 7 ) p ,
  14. 8 (
  15. 9 select t.instance
  16. 10 from v$thread t, v$parameter v
  17. 11 where v.name = 'thread'
  18. 12 and (v.value = 0 or t.thread# = to_number (v.value))
  19. 13 ) i,
  20. 14 (
  21. 15 select value
  22. 16 from v$parameter
  23. 17 where name = 'user_dump_dest') d;
  24. TRACE_FILE_NAME
  25. --------------------------------------------------------------------------------
  26. f:\program_files\oracle\administrator\diag\rdbms\ucs\ucs\trace/ucs_ora_7052.trc
  27. ######### 分析trc文件
  28. File Space Bitmap Block:
  29. BitMap Control:
  30. RelFno: 40, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
  31. 0100000000000000 0000000000000000 0000000000000000 0000000000000000
  32. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  33. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  34. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  35. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  36. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
SQL> select file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name = 'LMT_BMB'; FILE_NAME FILE_ID BYTES/1024/1024 -------------------------------------------------------------------------------- ---------- --------------- F:\PROGRAM_FILES\ORACLE\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\LMT_BMB.DBF 40 100 SQL> alter system dump datafile 40 block min 1 block max 3; System altered SQL> select 2 d.value || '/' || lower (rtrim (i.instance, chr (0))) || '_ora_' || p.spid || '.trc' trace_file_name 3 from ( 4 select p.spid 5 from v$mystat m, v$session s, v$process p 6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr 7 ) p , 8 ( 9 select t.instance 10 from v$thread t, v$parameter v 11 where v.name = 'thread' 12 and (v.value = 0 or t.thread# = to_number (v.value)) 13 ) i, 14 ( 15 select value 16 from v$parameter 17 where name = 'user_dump_dest') d; TRACE_FILE_NAME -------------------------------------------------------------------------------- f:\program_files\oracle\administrator\diag\rdbms\ucs\ucs\trace/ucs_ora_7052.trc ######### 分析trc文件 File Space Bitmap Block: BitMap Control: RelFno: 40, BeginBlock: 9, Flag: 0, First: 1, Free: 63487 0100000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 从第3个块开始,记录了位图的信息.
01是16进制转换为二进制就是0000 0001,经过高位低位的字节交换得出的结果是1000 0000表示分配了一个区间。

  1. SQL> alter table test allocate extent;
  2. Table altered
  3. SQL> alter system dump datafile 40 block min 1 block max 3;
  4. System altered
  5. SQL>
SQL> alter table test allocate extent; Table altered SQL> alter system dump datafile 40 block min 1 block max 3; System altered SQL> 分析trc信息如下:

  1. BitMap Control:
  2. RelFno: 40, BeginBlock: 9, Flag: 0, First: 2, Free: 63486
  3. 0300000000000000 0000000000000000 0000000000000000 0000000000000000
  4. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  5. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  6. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  7. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  8. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
BitMap Control: RelFno: 40, BeginBlock: 9, Flag: 0, First: 2, Free: 63486 0300000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000对第一个03转换后就是0000 0011, 进行高地位反转1100 0000,表示分配了2个区间.通过查询也可以看出这种情况:

  1. SQL> select segment_name, tablespace_name, extent_id, block_id, blocks
  2. 1 from dba_extents
  3. 2 where tablespace_name = 'LMT_BMB';
  4. SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
  5. ------------------------- ------------------------------ ---------- ---------- ----------
  6. TEST LMT_BMB 0 9 128
  7. TEST LMT_BMB 1 137 128
SQL> select segment_name, tablespace_name, extent_id, block_id, blocks 1 from dba_extents 2 where tablespace_name = 'LMT_BMB'; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS ------------------------- ------------------------------ ---------- ---------- ---------- TEST LMT_BMB 0 9 128 TEST LMT_BMB 1 137 128

注意这里分配的2个区都是128 * 8K = 1M。

  1. SQL> create table test2 tablespace lmt_bmb as select * from dba_users where 1=0;
  2. Table created
  3. SQL> select segment_name, tablespace_name, extent_id, block_id, blocks
  4. 1 from dba_extents
  5. 2 where tablespace_name = 'LMT_BMB';
  6. SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
  7. ----------------- ------------------------------ ---------- ---------- ----------
  8. TEST LMT_BMB 0 9 128
  9. TEST LMT_BMB 1 137 128
  10. TEST2 LMT_BMB 0 265 128
  11. SQL> alter table test2 allocate extent;
  12. Table altered
  13. SQL> alter table test2 allocate extent;
  14. Table altered
  15. SQL> alter table test2 allocate extent;
  16. Table altered
  17. SQL> select segment_name, tablespace_name, extent_id, block_id, blocks
  18. 1 from dba_extents
  19. 2 where tablespace_name = 'LMT_BMB';
  20. SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
  21. ----------------- ------------------------------ ---------- ---------- ----------
  22. TEST LMT_BMB 0 9 128
  23. TEST LMT_BMB 1 137 128
  24. TEST2 LMT_BMB 0 265 128
  25. TEST2 LMT_BMB 1 393 128
  26. TEST2 LMT_BMB 2 521 128
  27. TEST2 LMT_BMB 3 649 128
  28. 6 rows selected
  29. SQL> alter system dump datafile 40 block min 1 block max 3;
  30. System altered
  31. ######### 分析trace
  32. BitMap Control:
  33. RelFno: 40, BeginBlock: 9, Flag: 0, First: 6, Free: 63482
  34. 3F00000000000000 0000000000000000 0000000000000000 0000000000000000
  35. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  36. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  37. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  38. 3F转化之后为0011 1111 反转之后1111 0011, 所以是6个区.
SQL> create table test2 tablespace lmt_bmb as select * from dba_users where 1=0; Table created SQL> select segment_name, tablespace_name, extent_id, block_id, blocks 1 from dba_extents 2 where tablespace_name = 'LMT_BMB'; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS ----------------- ------------------------------ ---------- ---------- ---------- TEST LMT_BMB 0 9 128 TEST LMT_BMB 1 137 128 TEST2 LMT_BMB 0 265 128 SQL> alter table test2 allocate extent; Table altered SQL> alter table test2 allocate extent; Table altered SQL> alter table test2 allocate extent; Table altered SQL> select segment_name, tablespace_name, extent_id, block_id, blocks 1 from dba_extents 2 where tablespace_name = 'LMT_BMB'; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS ----------------- ------------------------------ ---------- ---------- ---------- TEST LMT_BMB 0 9 128 TEST LMT_BMB 1 137 128 TEST2 LMT_BMB 0 265 128 TEST2 LMT_BMB 1 393 128 TEST2 LMT_BMB 2 521 128 TEST2 LMT_BMB 3 649 128 6 rows selected SQL> alter system dump datafile 40 block min 1 block max 3; System altered ######### 分析trace BitMap Control: RelFno: 40, BeginBlock: 9, Flag: 0, First: 6, Free: 63482 3F00000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 3F转化之后为0011 1111 反转之后1111 0011, 所以是6个区.
drop掉test,观察分区和位图信息

  1. SQL> drop table test purge;
  2. Table dropped
  3. SQL> select segment_name, tablespace_name, extent_id, block_id, blocks
  4. 1 from dba_extents where tablespace_name = 'LMT_BMB';
  5. SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS
  6. --------------- ----------------- ---------- ---------- ----------
  7. TEST2 LMT_BMB 0 265 128
  8. TEST2 LMT_BMB 1 393 128
  9. TEST2 LMT_BMB 2 521 128
  10. TEST2 LMT_BMB 3 649 128
  11. SQL> alter system dump datafile 40 block min 1 block max 3;
  12. System altered
  13. SQL>
  14. ################ 分析dump文件信息
  15. BitMap Control:
  16. RelFno: 40, BeginBlock: 9, Flag: 0, First: 0, Free: 63484
  17. 3C00000000000000 0000000000000000 0000000000000000 0000000000000000
  18. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  19. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  20. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
SQL> drop table test purge; Table dropped SQL> select segment_name, tablespace_name, extent_id, block_id, blocks 1 from dba_extents where tablespace_name = 'LMT_BMB'; SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BLOCK_ID BLOCKS --------------- ----------------- ---------- ---------- ---------- TEST2 LMT_BMB 0 265 128 TEST2 LMT_BMB 1 393 128 TEST2 LMT_BMB 2 521 128 TEST2 LMT_BMB 3 649 128 SQL> alter system dump datafile 40 block min 1 block max 3; System altered SQL> ################ 分析dump文件信息 BitMap Control: RelFno: 40, BeginBlock: 9, Flag: 0, First: 0, Free: 63484 3C00000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000

对3C进行分析,得出0011 1100, 高地位为1100 0011, 也就是有4个区被分配,是一致的.

对于10g开始的回收站情况,可以通过以下sql语句进行查询和清理.

  1. 1)清除的方法如下:
  2.   1、purge table origenal_tableName;
  3. purge index origenal_indexName;
  4. 2、purge recyclebin;
  5. 2)查询垃圾信息,可以用如下SQL语句:
  6.   select t.object_name,t.type ,t.original_name from user_recyclebin t;
  7. 3)删除Table不进入Recycle的方法:
  8.   drop table tableName purge;
1)清除的方法如下:   1、purge table origenal_tableName; purge index origenal_indexName; 2、purge recyclebin; 2)查询垃圾信息,可以用如下SQL语句:   select t.object_name,t.type ,t.original_name from user_recyclebin t; 3)删除Table不进入Recycle的方法:   drop table tableName purge;
Autoallocate的区大小管理方式的位图信息和uniform的有稍微区别,uniform的位图表示分配了多少个区,而autoallocate方式则表示大小情况,每个表示64KB。


3.Autoallocate方式以及对应位图信息

先创建表,查看分区情况,dump文件头的3个块。
  1. SQL> select tablespace_name, extent_management, allocation_type
  2. 2 from dba_tablespaces
  3. 3 where tablespace_name = 'LMT_BMB_AUTO';
  4. TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE
  5. ------------------------------ ----------------- ---------------
  6. LMT_BMB_AUTO LOCAL SYSTEM
  7. SQL>
  8. --allocate_type = system 表示是系统自动分配extent的大小
  9. SQL> select file_name, file_id, bytes/1024/1024 from dba_data_files
  10. 1 where tablespace_name = 'LMT_BMB_AUTO';
  11. FILE_NAME FILE_ID BYTES/1024/1024
  12. -------------------------------------------------------------------------------- ---------- ---------------
  13. F:\PROGRAM_FILES\ORACLE\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\LMT_BMB_AUTO.dbf 41 100
  14. SQL> create table test4 tablespace lmt_bmb_auto
  15. 2 as select * from all_objects where 1=0;
  16. Table created
  17. SQL> select tablespace_name, segment_name, file_id, extent_id, block_id, blocks
  18. 2 from dba_extents
  19. 3 where tablespace_name='LMT_BMB_AUTO';
  20. TABLESPACE_NAME SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS
  21. ------------------------------ ------------- ---------- ---------- ---------- ----------
  22. LMT_BMB_AUTO TEST4 41 0 9 8
  23. SQL>
SQL> select tablespace_name, extent_management, allocation_type 2 from dba_tablespaces 3 where tablespace_name = 'LMT_BMB_AUTO'; TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE ------------------------------ ----------------- --------------- LMT_BMB_AUTO LOCAL SYSTEM SQL> --allocate_type = system 表示是系统自动分配extent的大小 SQL> select file_name, file_id, bytes/1024/1024 from dba_data_files 1 where tablespace_name = 'LMT_BMB_AUTO'; FILE_NAME FILE_ID BYTES/1024/1024 -------------------------------------------------------------------------------- ---------- --------------- F:\PROGRAM_FILES\ORACLE\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\LMT_BMB_AUTO.dbf 41 100 SQL> create table test4 tablespace lmt_bmb_auto 2 as select * from all_objects where 1=0; Table created SQL> select tablespace_name, segment_name, file_id, extent_id, block_id, blocks 2 from dba_extents 3 where tablespace_name='LMT_BMB_AUTO'; TABLESPACE_NAME SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS ------------------------------ ------------- ---------- ---------- ---------- ---------- LMT_BMB_AUTO TEST4 41 0 9 8 SQL>
可以看出这个区大小为64KB,来看具体的位图信息
  1. SQL> alter system dump datafile 41 block min 1 block max 3;
  2. System altered
  3. SQL>
  4. ############################# trc的位图信息
  5. File Space Bitmap Block:
  6. BitMap Control:
  7. RelFno: 41, BeginBlock: 9, Flag: 0, First: 1, Free: 63487
  8. 0100000000000000 0000000000000000 0000000000000000 0000000000000000
  9. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  10. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  11. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
SQL> alter system dump datafile 41 block min 1 block max 3; System altered SQL> ############################# trc的位图信息 File Space Bitmap Block: BitMap Control: RelFno: 41, BeginBlock: 9, Flag: 0, First: 1, Free: 63487 0100000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 000000000000000001转换成二进制是0000 0001,高低位转换后为1000 0000。由于是自动大小分配管理的区间,这里的1并不能代表分配了一个区间,只说代表分配了64K大小,因为区间尺寸可能是64K,1MB,8MB,64M或更大,不过都有一个通用尺寸64KB,所以64KB就是该表空间的一个位标记大小。通过向表中插入数据,可以查看出来。
  1. SQL> insert into test4 select * from all_objects;
  2. 68385 rows inserted
  3. SQL> select segment_name, blocks * 8 extent_size_KB, sum(blocks) * 8 total_KB, count(1) total_extents
  4. 2 from dba_extents
  5. 3 where segment_name = 'TEST4' and tablespace_name='LMT_BMB_AUTO'
  6. 4 group by segment_name, blocks;
  7. SEGMENT_NAME EXTENT_SIZE_KB TOTAL_KB TOTAL_EXTENTS
  8. --------------- -------------- ---------- -------------
  9. TEST4 1024 7168 7
  10. TEST4 64 1024 16
  11. SQL>
  12. ############## trc 位图信息
  13. File Space Bitmap Block:
  14. BitMap Control:
  15. RelFno: 41, BeginBlock: 9, Flag: 0, First: 128, Free: 63360
  16. FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 0000000000000000 0000000000000000
  17. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
  18. 0000000000000000 0000000000000000 0000000000000000 0000000000000000
SQL> insert into test4 select * from all_objects; 68385 rows inserted SQL> select segment_name, blocks * 8 extent_size_KB, sum(blocks) * 8 total_KB, count(1) total_extents 2 from dba_extents 3 where segment_name = 'TEST4' and tablespace_name='LMT_BMB_AUTO' 4 group by segment_name, blocks; SEGMENT_NAME EXTENT_SIZE_KB TOTAL_KB TOTAL_EXTENTS --------------- -------------- ---------- ------------- TEST4 1024 7168 7 TEST4 64 1024 16 SQL> ############## trc 位图信息 File Space Bitmap Block: BitMap Control: RelFno: 41, BeginBlock: 9, Flag: 0, First: 128, Free: 63360 FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 一共23个区,总大小7个1M的区,16个64K的区, 总大小7168 + 1024 = 8192KB.
可以看出exent的大小从64K变为了1M。
看看位图信息. 也就是 1111 1111 一共16组,每位代表64K,总大小: 16 * 8 * 64KB = 8192KB 跟统计的一样.
阅读(2360) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~