9
Copyright © Oracle Corporation, 2002. All rights reserved.
Storage Structure and Relationships
9-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
•
Describe the logical structure of the database
•
List the segment types and their uses
•
List the keywords that control block space usage
•
Obtain storage structure information
9-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Storage and Relationship Structure
Database
PROD
DISK1/SYS1.dbf
DISK2/
USER1.dbf
DISK3/
USER2.dbf
DISK1/
UNDO1.dbf
DISK1/
TEMP.dbf
D.D.

Table
Data
Seg
D.D.
Index
Index
Seg
RB
Seg
S_DEPT
Data
Seg
S_EMP
Data
Seg
Temp
Temp
Seg
1 2 1 2 1 2 1 1 2 2 1 FREE 1 1 2 2 1
EXTENTS
S_DEPT
(cont'd)
Data
Seg
S_EMP
FIRST_N
AME
Index
Index
Seg

Oracle DATA BLOCKS
RBS1
(cont'd)
RB
Seg
RBS2
(cont'd)
RB
Seg
RBS1
RB
Seg
RBS2
RB
Seg
SEGMENTS
SYSTEM USER_DATA RBS TEMP
TABLESPACES
DATA FILES
9-4
Copyright © Oracle Corporation, 2002. All rights reserved.
Types of Segments
Table
Cluster
Table
partition
Index
9-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Types of Segments

Index-organized
table
Index
partition
Undo
segment
Temporary
segment
9-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Types of Segments
LOB
segmen
t
Bootstrap
segment
Nested
table
9-8
Copyright © Oracle Corporation, 2002. All rights reserved.
Storage Clause Precedence
Oracle default
Tablespace
Segment
9-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Extent Allocation and Deallocation
•
An extent is a chunk of space used by a segment
within a tablespace.

•
An extent is allocated when the segment is:
–
Created
–
Extended
–
Altered
•
An extent is deallocated when the segment is:
–
Dropped
–
Altered
–
Truncated
9-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Used and Free Extents
Data file
Free extentUsed extentFile header
9-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Block
•
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
9-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Multiple Block Size Support
•
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 KB and 32 KB.
9-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Standard Block Size
•
Set at database creation using the DB_BLOCK_SIZE
parameter; cannot 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 (4 MB or 16 MB)
–
Default value = 48 MB
9-14
Copyright © Oracle Corporation, 2002. All rights reserved.
9-15

Copyright © Oracle Corporation, 2002. All rights reserved.
Nonstandard Block Size
•
Configure additional caches with the following
dynamic parameters:
–
DB_2K_CACHE_SIZE for 2 KB blocks
–
DB_4K_CACHE_SIZE for 4 KB blocks
–
DB_8K_CACHE_SIZE for 8 KB blocks
–
DB_16K_CACHE_SIZE for 16 KB blocks
–
DB_32K_CACHE_SIZE for 32 KB blocks
•
DB_nK_CACHE_SIZE is not allowed
if nK is the standard block size.
•
Minimum size for each cache is one granule.
9-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating Nonstandard
Block Size Tablespaces
CREATE TABLESPACE tbs_1
DATAFILE 'tbs_1.dbf'
SIZE 10M BLOCKSIZE 4K;
DESCRIBE dba_tablespaces
Name Null? Type


TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER

9-19
Copyright © Oracle Corporation, 2002. All rights reserved.
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-organized table overflow and out-of-line LOB
segments can be stored in a tablespace with a block
size different from the base table.
9-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Block Contents
Header
Free space
Data
9-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Block Space
Utilization Parameters
INITRANS
MAXTRANS
PCTFREE

PCTUSED
9-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Data Block Management
Two methods are available for managing data blocks:
•
Automatic segment-space management
•
Manual management
9-24
Copyright © Oracle Corporation, 2002. All rights reserved.
Automatic Segment-Space Management
•
It is a method of managing free space inside
database segments.
•
Tracking in-segment free and used space is done
using bitmaps as opposed to using free lists.
•
This method provides:
–
Ease of management
–
Better space utilization
–
Better performance for concurrent INSERT operations
9-25
Copyright © Oracle Corporation, 2002. All rights reserved.
Automatic Segment-Space Management
•

Bitmap segments contain a bitmap that describes
the status of each block in the segment with respect
to its available space.
•
The map is contained in a separate set of blocks
referred to as bitmapped blocks (BMBs).
•
When inserting a new row, the server searches the
map for a block with sufficient space.
•
As the amount of space available in a block
changes, its new state is reflected in the bitmap.
9-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Configuring Automatic
Segment-Space Management
•
Automatic segment-space management can be
enabled at the tablespace level only, for locally
managed tablespaces.
•
After a tablespace is created, the specifications
apply to all segments created in the tablespace.
CREATE TABLESPACE data02
DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;
9-27
Copyright © Oracle Corporation, 2002. All rights reserved.
Manual Data Block Management

•
Allows you to configure data blocks manually using
parameters such as:
–
PCTFREE
–
PCTUSED
–
FREELIST
•
The only method available in previous Oracle
versions
9-28
Copyright © Oracle Corporation, 2002. All rights reserved.
Block Space Usage
80%
80%
40%
Inserts
1 2
3 4
Inserts
PCTFREE=20 PCTUSED=40
Insert
(on freelist)
Inserts
(off freelist)
9-29
Copyright © Oracle Corporation, 2002. All rights reserved.
Obtaining Storage Information

Information about storage can be obtained by querying
the following views:
•
DBA_EXTENTS
•
DBA_SEGMENTS
•
DBA_TABLESPACES
•
DBA_DATA_FILES
•
DBA_FREE_SPACE