Management and distribution of Oracle Space Management Experiment (II) area

Content based on LMT management of the table space, the dictionary management has not been used.

This article mainly validates these questions:

1.LMT managed tablespace, there are two ways to allocate the area:

System allocation and uniform fixed size--> See experiment

2. Verify Oracle's search for available areas:

Obtain the available area information from the bitmap block at the beginning of the data file, which is visible in the dump First:3 this indicates that 3 extents have been used. See: Click to open the link

3. Build the first table in the table space (note, the first one), this table starts with a few blocks of data file

11G, LMT management of the table space, data files in block 0-127 to do bitmap area, the 128th block to begin to hold the table data. See:

4. Minimum table-How large is the smallest area?

5 blocks, if the block size is 8K, then the smallest table is 40K. --See the following experiment

5. How do multiple data files in a table space allocate space?

Average distribution on multiple data files--see the following experiment

#####################################################################

Experiment one: What is the difference between the size of the system admin area and the size of the unified region, and how to verify the difference?

The system Admin area size is automatically allocated by the system to the extents of the expansion.

In the front 1M space of the segment: The district size is 8 blocks =64k, the first 16 districts are such.

Between segment 1M---64M: District size 1m,128 Block

After segment 64M, the area size is 8M.

You can create a table in the table space in the System Admin area and then manually assign 1 extent, then expand the 960K space, 1M space, 62M space, and then extend a extent to test. ALTER TABLE A1 allocate extent (SIZE 1m);

The uniform size is specified by the uniform size 40k when the table space is created.

You can create a table space of two different ways of managing it, and then create two tables in this two-table space, manually expand the area, and then view it through dba_segments.

bys@ Bys3>select Tablespace_name,block_size,initial_extent,next_extent,extent_management,allocation_type, Segment_space_management from dba_tablespaces where tablespace_name like ' test_ ';

Tablespace_name block_size initial_extent next_extent Extent_man allocatio

--------------- ---------- -------------- ----------- ---------- --------- ------

TEST1 8192 65536 Local SYSTEM AUTO

TEST2 8192 40960 40960 Local uniform AUTO

TEST3 8192 65536 Local SYSTEM AUTO

bys@ Bys3>select * from Cat;

TABLE_NAME Table_type

------------------------------ -----------

DEPT TABLE

bys@ bys3>create table test1 (aa int) tablespace test1;

Table created.

bys@ bys3>create table test2 (aa int) tablespace test2;

Table created.

bys@ Bys3>insert into test1 values (789);

bys@ Bys3>insert into test2 values (789);

bys@ bys3>commit;

Commit complete.

bys@ Bys3>select segment_name,tablespace_name,bytes,blocks,extents,initial_extent,next_extent from dba_segments where owner= ' BYS ' and segment_name like ' test_ ';

Segment_name tablespace_name BYTES BLOCKS extents initial_extent

--------------- --------------- ---------- ---------- ---------- -------------- -----------

TEST1 TEST1 65536 8 1 65536 1048576

TEST2 TEST2 40960 5 1 40960 40960

bys@ Bys3>alter table Test1 allocate extent;

Table altered.

bys@ Bys3>alter Table Test2 allocate extent;

Table altered.

bys@ Bys3>select segment_name,tablespace_name,bytes,blocks,extents,initial_extent,next_extent from dba_segments where owner= ' BYS ' and segment_name like ' test_ ';

Segment_name tablespace_name BYTES BLOCKS extents initial_extent

--------------- --------------- ---------- ---------- ---------- -------------- -----------

TEST1 TEST1 131072 16 2 65536 1048576

TEST2 TEST2 81920 10 2 40960 40960

bys@ Bys3>alter Table Test2 allocate extent (size 2m); --2M, the system expands automatically, at this time a zone size is 1M, so this statement expands two extents, at this time 4 districts

Table altered.

bys@ Bys3>alter Table Test1 allocate extent (size 2m),-uniform size 40k,2048/40=51.2, allocated 51 districts-rounded. At this time is 54 districts.

Table altered.

bys@ Bys3>select segment_name,tablespace_name,bytes,blocks,extents,initial_extent,next_extent from dba_segments where owner= ' BYS ' and segment_name like ' test_ ';

Segment_name tablespace_name BYTES BLOCKS extents initial_extent

--------------- --------------- ---------- ---------- ---------- -------------- -----------

TEST1 TEST1 2228224 272 4 65536 1048576

TEST2 TEST2 2170880 265 53 40960 40960

bys@ Bys3>alter Table Test2 allocate extent (size 110k); The distribution range here seems to be rounded, like the 110k/40k=2.75 here, with three districts allocated.

Table altered.

bys@ Bys3>select segment_name,tablespace_name,bytes,blocks,extents,initial_extent,next_extent from dba_segments where owner= ' BYS ' and segment_name like ' test_ ';

Segment_name tablespace_name BYTES BLOCKS extents initial_extent

--------------- --------------- ---------- ---------- ---------- -------------- -----------

TEST1 TEST1 2228224 272 4 65536 1048576

TEST2 TEST2 2293760 280 56 40960 40960