Chinaunix首页 | 论坛 | 博客
  • 博客访问: 927724
  • 博文数量: 358
  • 博客积分: 8185
  • 博客等级: 中将
  • 技术积分: 3751
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 16:27
个人简介

The views and opinions expressed all for my own,only for study and test, not reflect the views of Any Company and its affiliates.

文章分类

全部博文(358)

文章存档

2012年(8)

2011年(18)

2010年(50)

2009年(218)

2008年(64)

我的朋友

分类: Oracle

2009-02-03 12:21:41

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
 
 
阅读(800) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~