ITPub博客

首页 > 数据库 > Oracle > Tablespace in Oracle

Tablespace in Oracle

原创 Oracle 作者:lazyzxm 时间:2007-09-06 16:40:30 0 删除 编辑

Oracle is a kind of database. One of database's function is manage persistent data. Persistent data is stored on some device, such hard disk, tape.

In current technology, Oracle database mainly stores persistent data on harddisk. Oracle use tablespace as an interface to manage persistent data. We will spend some time on tablespaces, and learn how oracle stores data on file in HD.

[@more@]

There are different kinds of tablespaces in Oracle: temp tablespace, rollback tablespace, and permanent tablespace. As usual, there are some variants.

We can get the information about tablespaces from dictionary.

select * from dba_tablespaces;

We can get the name of tablespace in the tablespace_name column. We can see the contents column of a tablespace.

Tablespace_name Contents
TEMP TEMPORARY
UNDOTBS1 UNDO
RIVER PERMANENT

Temp tablespace is used to store temporary data, for external sort and temp tables, .etc. The contents in a temp tablespace may only vaid in a transaction or a session.

Undo tablespace is used to store undo data. When ever you want to modify something, oracle need to know how to undo the change. For example, if you want to delete a record from a table, oracle need to know how to undo the delete, that is like insert the record. Oracle will put the undo in undo tablespace.

Permanent tablespace stores permanent data, like data for table, index. In this time, we mainly talk about permanent tablespace.

At first, we create a tablespace.

create tablespace river datafile '/u02/oradata/zoo/river01.dbf' size 10304k extent management local uniform size 1m;

Please refer to Oracle document for details of how to create a tablespace. After the tablespace is created, we can get the information of it from dictionary.

select * from dba_tablespaces where tablespace_name = 'RIVER';

TABLESPACE_NAME RIVER
BLOCK_SIZE 8192
INITIAL_EXTENT 1048576
NEXT_EXTENT 1048576
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
MIN_EXTLEN 1048576
STATUS ONLINE
CONTENTS PERMANENT
LOGGING LOGGING
FORCE_LOGGING NO
EXTENT_MANAGEMENT LOCAL
ALLOCATION_TYPE UNIFORM
PLUGGED_IN NO
SEGMENT_SPACE_MANAGEMENT AUTO
DEF_TAB_COMPRESSION DISABLED
RETENTION NOT APPLY
BIGFILE NO

From the last ariticle, we know that table treasure is created in tablespace river. That is, the data of treasure is stored in tablespace river, and in file river_01.dbf in this case. Data stored in tablespace are grouped in to segments. A segment lives in a tablespace. For example, the data of table treasure are in segment TREASURE in tablespace RIVER. And the data of index treasure_idx are in segment TREASURE_IDX. We can get this information from dictionary.

select * from dba_segments where tablespace_name = 'RIVER' and segment_name like 'TREASURE%';

OWNER FISH
SEGMENT_NAME TREASURE
PARTITION_NAME
SEGMENT_TYPE TABLE
TABLESPACE_NAME RIVER
HEADER_FILE 32
HEADER_BLOCK 268
BYTES 1048576
BLOCKS 128
EXTENTS 1
INITIAL_EXTENT 1048576
NEXT_EXTENT 1048576
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
FREELISTS
FREELIST_GROUPS
RELATIVE_FNO 32
BUFFER_POOL DEFAULT
OWNER FISH
SEGMENT_NAME TREASURE_IDX
PARTITION_NAME
SEGMENT_TYPE INDEX
TABLESPACE_NAME RIVER
HEADER_FILE 32
HEADER_BLOCK 396
BYTES 1048576
BLOCKS 128
EXTENTS 1
INITIAL_EXTENT 1048576
NEXT_EXTENT 1048576
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
FREELISTS
FREELIST_GROUPS
RELATIVE_FNO 32
BUFFER_POOL DEFAULT

A table may have many segments (partitioned table, a table with clob or blob columns). In our simple sample, table treasure has only one segment, treasure. The name of the segment of a table is like the name of the table, Oracle choose the name automaticlly.

How can you figure out how big a table is? You may estimate it via a simple fomula: number_of_rows * size_of_a_single_row. You can also get the information from dictionary. The size of a table is the sum of the size of every segments. In our simple sample,

select sum(bytes) / 1024 / 1024 size_m from dba_segments where segment_name = 'TREASURE';

A segment can only lives in one tablespace, but a tablespace may have many data files under it, so a segment may live in many data files.

A segment is consisted of extents. A extent is part of a segment. We can get this kind of information from dictionary.

select * from dba_extents where segment_name = 'TREASURE';

OWNER FISH
SEGMENT_NAME TREASURE
PARTITION_NAME
SEGMENT_TYPE TABLE
TABLESPACE_NAME RIVER
EXTENT_ID 0
FILE_ID 32
BLOCK_ID 265
BYTES 1048576
BLOCKS 128
RELATIVE_FNO 32

Think about this: Why a segment shoud be consisted of extents?

Since a segment may have many extents, should the size of each extent of a segment be the same? Or different extent may have different size? When a segment is created, how many extents should be assigned to it? How many extents should be added to a segment when the segment grows (extend)?

Extent is consisted of blocks. The size of blocks in a extent (and in a segment) are of the same size.

How Oracle manages extents in a segment?

There are dictionary managed extents. Each extent repsents a row in dictionary. When you require a extent, Oracle may need to issue SQL statements to query dictionary for information of extents. These SQL are called recusive SQL, and it's expensive.

There are local managed extents. Extents infomation are stored in each datafile. It is adviced to use local managed extents.

When we create a tablespace, we specify 'extent management local' or 'extent management dictionary'.

If the system tablespace is local extent management, we can't create dictonary extent management tablespace.

Try the following SQL:

select tablespace_name, extent_management, allocation_type, segment_space_management from dba_tablespaces;

TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPAC
--------------- -------------------- ------------------ ------------
SYSTEM LOCAL SYSTEM MANUAL
UNDOTBS1 LOCAL SYSTEM MANUAL
SYSAUX LOCAL SYSTEM AUTO
TEMP LOCAL UNIFORM MANUAL
USERS LOCAL SYSTEM AUTO
EXAMPLE LOCAL SYSTEM AUTO
RIVER LOCAL UNIFORM AUTO
SEA LOCAL UNIFORM AUTO

If allocation_type is system, Oracle'll specify the size of each extent. If allocation_type is uniform, the size of each extent is the same, which is specified in the create tablespace statement 'uniform size xxx'.

The segment_space_management column indicate how space is allocated to segments in a tablespace. We can specify this in the create tablespace statement via 'segment space management auto' or 'segment space management manual'. The default is auto.

We only talk about local extent management. In this case, each segment has a segment header, and each extent has a extent header.

For segment TREASURE, we already know that HEADER_FILE is 32
and HEADER_BLOCK is 268. This block contains the segment header. Let's look at what's in this block:

alter system dump datafile 32 block min 268 block max 268;

Start dump data blocks tsn: 17 file#: 32 minblk 268 maxblk 268
buffer tsn: 17 rdba: 0x0800010c (32/268)
scn: 0x0000.36e462b1 seq: 0x01 flg: 0x04 tail: 0x62b12301
frmt: 0x02 chkval: 0xf16e type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
...
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 128
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x08000149 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0800010d ext#: 0 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x08000109
Level 1 BMB for Low HWM block: 0x08000109
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0800010b
Last Level 1 BMB: 0x0800010a
Last Level II BMB: 0x0800010b
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 59577 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x08000109 length: 128

Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x08000109 Data dba: 0x0800010d
--------------------------------------------------------

Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0800010b

End dump data blocks tsn: 17 file#: 32 minblk 268 maxblk 268

And we know the only extent in treasure:

FILE_ID 32
BLOCK_ID 265

So let's dump the block.

alter system dump datafile 32 block 265.

Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x0800010b poffset: 0
unformatted: 44 total: 64 first useful block: 4
owning instance : 1
instance ownership changed at 08/28/2007 04:14:22
Last successful Search 08/28/2007 04:14:22
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 16

Extent Map Block Offset: 4294967295
First free datablock : 4
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 59577
HWM Flag: HWM Set
Highwater:: 0x08000149 ext#: 0 blk#: 64 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 60
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x08000109 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
4:unformatted 5:unformatted 6:unformatted 7:unformatted
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted
16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
28:75-100% free 29:75-100% free 30:75-100% free 31:75-100% free
32:unformatted 33:unformatted 34:unformatted 35:unformatted
36:unformatted 37:unformatted 38:unformatted 39:unformatted
40:unformatted 41:unformatted 42:unformatted 43:unformatted
44:unformatted 45:unformatted 46:unformatted 47:unformatted
48:unformatted 49:unformatted 50:unformatted 51:unformatted
52:unformatted 53:unformatted 54:unformatted 55:unformatted
56:unformatted 57:unformatted 58:unformatted 59:unformatted

We'll not introduce the meaning of the dump file. It provides a way to us to observe Oracle. When you have interest, you can research what's in these dump files.

We can also dump table data and index.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7745048/viewspace-968249/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Test of html
下一篇: test sytle
请登录后发表评论 登录
全部评论
  • 博文量
    4
  • 访问量
    4351