Transcript Oracle Database 10g: Administration Workshop I

Managing Database Storage Structures
Copyright © 2005, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Describe how table row data is stored in blocks
• Define the purpose of tablespaces and data files
• Create and manage tablespaces
• Obtain tablespace information
• Describe the main concepts and functionality
of Automatic Storage Management (ASM)
5-2
Copyright © 2005, Oracle. All rights reserved.
Storage Structures
Logical
Physical
Database
Tablespace
OS file
Segment
Extent
Oracle data
block
5-3
OS
block
Copyright © 2005, Oracle. All rights reserved.
How Table Data Is Stored
Columns
Table A
Blocks
Table B
Rows
Segment
Segment
Table
Tablespace
Row piece
5-4
Copyright © 2005, Oracle. All rights reserved.
Extent
Anatomy of a Database Block
Block header
Growth
Free space
Row data
5-5
Copyright © 2005, Oracle. All rights reserved.
Tablespaces and Data Files
The Oracle database stores data logically in
tablespaces and physically in data files.
• Tablespaces:
– Can belong to only one database
– Consist of one or more data files
– Are further divided into logical units of storage
•
Data files:
– Can belong to only one
tablespace and one database
– Are a repository for schema
object data
Database
Tablespace
Data files
5-6
Copyright © 2005, Oracle. All rights reserved.
Space Management in Tablespaces
•
Locally managed tablespace:
–
–
–
–
–
•
Free extents are managed in the tablespace.
A bitmap is used to record free extents.
Each bit corresponds to a block or group of blocks.
The bit value indicates free or used extents.
The use of locally managed tablespaces is
recommended.
Dictionary-managed tablespace:
– Free extents are managed by the data dictionary.
– Appropriate tables are updated when extents are
allocated or unallocated.
– These tablespaces are supported only for backward
compatibility.
5-7
Copyright © 2005, Oracle. All rights reserved.
Creating a New Tablespace
5-8
Copyright © 2005, Oracle. All rights reserved.
Storage for Locally Managed Tablespaces
5-10
Copyright © 2005, Oracle. All rights reserved.
Tablespaces in the Preconfigured
Database
•
•
•
5-12
SYSTEM
SYSAUX
TEMP
•
•
•
UNDOTBS1
USERS
EXAMPLE
Copyright © 2005, Oracle. All rights reserved.
Altering a Tablespace
5-14
Copyright © 2005, Oracle. All rights reserved.
Actions with Tablespaces
5-17
Copyright © 2005, Oracle. All rights reserved.
Dropping Tablespaces
5-19
Copyright © 2005, Oracle. All rights reserved.
Viewing Tablespace Information
5-20
Copyright © 2005, Oracle. All rights reserved.
Where We Are
•
•
•
•
•
5-21
Using tablespaces to separate data
Creating various types of tablespaces
Managing tablespaces
Obtaining tablespace information
Describe the main concepts and functionality
of Automatic Storage Management (ASM)
Copyright © 2005, Oracle. All rights reserved.
What Is Automatic Storage Management?
•
•
•
•
•
Is a portable and high-performance
cluster file system
Manages Oracle database files
Application
Spreads data across disks
to balance load
Database
Mirrors data
File
system
Solves many storage
ASM
Volume
management challenges
manager
Operating system
5-22
Copyright © 2005, Oracle. All rights reserved.
ASM: Key Features and Benefits
•
•
•
•
•
•
•
5-23
Stripes files, but not logical volumes
Provides online disk reconfiguration and dynamic
rebalancing
Allows for adjustable rebalancing speed
Provides redundancy on a per-file basis
Supports only Oracle database files
Is cluster aware
Is automatically installed
Copyright © 2005, Oracle. All rights reserved.
ASM: Concepts
ASM
disk group
Database
Tablespace
ASM file
Data file
Segment
Extent
Oracle data
block
5-24
ASM disk
File system
file
or
Raw device
Allocation unit
Physical
block
Copyright © 2005, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe how table row data is stored in blocks
• Define the purpose of tablespaces and data files
• Create and manage tablespaces
• Obtain tablespace information
• Describe the main concepts and functionality of
ASM, such as:
– Disk mirroring
– Dynamic rebalancing
– ASM disk groups
5-25
Copyright © 2005, Oracle. All rights reserved.
Practice Overview:
Managing Database Storage Structures
This practice covers the following topics:
• Creating tablespaces
• Gathering information about tablespaces
5-26
Copyright © 2005, Oracle. All rights reserved.