Local Tablespace L1 BMP DBA Range number question

From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 29 Mar 2009 04:54:58 -0700 (PDT)
Message-ID: <f2090a70-6584-404f-8b84-551a67e63b8c_at_f41g2000pra.googlegroups.com>

Hi All

Local tablespace L1 BMP DBA Range

The L1 BMP DBA Range number increases, What is the rule?

L2 BMP L1 Range keeps same about 241 when the blocksize is 2048. is that right?

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

Jacky Received on Sun Mar 29 2009 - 06:54:58 CDT

Original text of this message