2009.02.03_31_32_33_34 StorageStru&Relationship

Objectives:

■Segement types: (11)

Table

Table patition

Cluster

Index

Index patition

Index-organized table

undo segement

Temporary segment

Lob segment

Nested table

Bootstrap segment;

■Strorage Clause Precedence

①If storage parameters are altered,the new options apply only to the extents not yet allocated.

②Some parameter cannot be specified at the tablespace level.These parameter must be specified at the segement level only.

③If minimum extent size has been specified for the tablespace,this size applies to all extents that are allocated for segements in talespace in the future.

■Extent allocate & Deallocate

・An extent is allocated when the segment is :created ,Extended,Altered

・An extent is deallocate when the segment is :Dropped,Truncated,Altered

■Database blocks

・Minimum unit of I/O

・Consists of one or more operating system blocks

・Set at tablespace creation

・DB_BLOCK_SIZE is the default block size

$strings spfilealan.ora | grep -i db_block

■Multiple Block size

A database can be created with a standard block size and up to four nonstandard block sizes

Block sizes can have any power-of-two value between 2 and 32 kb

・Standard Bock size

 Set at database cration using th DB_BLOCK_SIZE parameter;can't be changed without re-creating the database

 Used for SYSTEM and TEMPORARY tablespaces

 DB_CACHE_SIZE  specifies the size of the DEFAULT buffer cache for standard block size: Minimum size = one granule(4M or 16M),Default value =48M

・Nonstandard Block size

 DB_2K_CACHE_SIZE 

 DB_4K_CACHE_SIZE 

 DB_8K_CACHE_SIZE 

 DB_16K_CACHE_SIZE 

 DB_32K_CACHE_SIZE 

 DB_nK_CACHE_SIZE is not allowed if nK is the standard block size.

Minimum size for eache cache is one granule

・Creating Nonstandard Block Size Tablespaces

CRATE TABLESPACE tbs_1 DATAFILE 'tbs_1.dbf'SIZE 10m BLOCKSIZE 4k

SQL>desc dba_tablespaces;

SQL>select tablespace_name,block_size from dba_tablespaces;

・Multiple Block sizing Rules

 ALL partitions of a partitioned object must reside in tablespaces of the same block size.

 All temporary tablespaces,including the permanent ones that are being used as default temporary tablespaces,must be of standard block size

 Index-orgnized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from base table.

 

■Database Block Contents

Header

↓

Free space

↑

Data

・Block spcace utilization parameters

INITRANS & MAXTRANS : Specify the initial and the maximum number of transaction slots that are created in an index or a data block.

PCTFREE

PCTUSED

 

■Data Block Management

・Automatic segments-space management

tracking in-segment free and used space is done using bitmaps  as opposed to using free lists.

Auto matic segment-space management can be enabled at the tablespace level only,for locally managed tablespaces.

SQL>CREATE TABLESPACE data02 'tbs_02.dbf'EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64k

SEGEMENT SPACE MANAGEENT AUTO;

・Manual management

Allows you to configure data blocks data blocks manually using parameters such as :PCTFREE,PCTUSED,FREELIST

The manual method is the default

HWM(High-Water Mark)

SQL>select * from user_bjects;

SQL>select segment_name from user_segments;

SQL>create table m(id integer,name chare(10));

SQL>select segment_name from user_segments;

SQL>set autotrace on traceonly;

SQL>select count(*) from m;

SQL>begin

for i in 1 .. 1000000

loop

  insert into m values(i,'booboke');

end loop;

commit;

end;

SQL>select count(*) from m;

SQL>delete from m;

SQL>commit;

SQL>select count(*) from m;

SQL>truncate table m;

 

■Obtaining Strorage Information

・DBA_EXTENTS

・DBA_SEGMENTS

・DBA_TABLESPACES

・DBA_DATA_FILES

・DBA_FREE_SPACE