ASSM tablespace three level bmp block questions.

I have one question about ASSM tablespace. The ASSM tablespace has three level BMP blocks to
indicate if block is used. How many blocks does one L1 BMP block manages? After do one test, I found
the number increases when the data increases. Is there any rule about it?

How many L1 BMP blocks does one L2 BMP block manages. After do the test, I found the number 241 when
the blocksize is 2048 bytes. Is that right?

Any answers are appreciated.

Below is test case.

1. drop tablespace and create tablespace drop tablespace aaa including contents and datafiles; CREATE TABLESPACE aaa DATAFILE 'a' SIZE 20m AUTOEXTEND ON LOGGING PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10k BLOCKSIZE 2K SEGMENT SPACE MANAGEMENT AUTO;
2. create table create table tt ( x number ) tablespace aaa storage (initial 1000K); ;
3. get first block id exec show_space ( 'TT','AUTO', 'TABLE' );
4. insert enough data and make sure the table has more than 1 L2 BMP begin for i in 1 .. 5500000 loop insert into tt values(i); end loop; commit; end; /

exec show_space ( 'TT','AUTO', 'TABLE' );

5. dump the segment header
alter system dump datafile 7 block min 33 block max 35;

6. get three L2 BMP block
frmt: 0x02 chkval: 0x41dd type: 0x23=PAGETABLE SEGMENT HEADER

   Second Level Bitmap block DBAs

   DBA 1:   0x01c00022
   DBA 2:   0x01c032a4
   DBA 3:   0x01c06b20

7. dump all L2 BMP blocks and get all list of L1 BMP blocks

8. create table test (

seq -- sequence
chex -- hex dba
cdec -- decimal dba
diff -- L1 BMP block DBA range number

9, load hex dba of the L1 BMP blocks into table test

1. update decimal dba update exte set cdec = to_number(chex,'XXXXXXXX');
2. get L1 BMP block DBA range number update exte AA set diff = cdec - (select cdec from exte BB where AA.seq = BB.seq+1);
3. get below test result seq hex DBA dec DBA DBA range number

   ---

   1 01c00021 29360161 15
   ...
   36 01c0025b 29360731 60
   ...
   573 01c0804b 29392971 80
4. L2 BMP has max 241 L1 range number

Thanks
Jacky Received on Mon Mar 30 2009 - 20:11:04 CDT