Less05 Storage TB3
Content
5
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
5-2
•
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)
Copyright © 2005, Oracle. All rights reserved.
Storage Structures Logical
P h ys i c a l
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
5-6
Database Tablespace
Data files
56
Copyright © 2005, Oracle. All rights reserved.
Oracle Managed Files (OMF)
Specify file operations in terms of database objects rather than file names. Parameter DB_CREATE_FILE_DEST
Description Defines the location of the default file system directory for data files and temporary files
DB_CREATE_ONLINE_LOG_DEST_n
Defines the location for redo log
DB_RECOVERY_FILE_DEST
files and control file creation Defines the location for RMAN backups
Example: SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE TABLESPACE tbs_1;
57
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 Dictionarymanaged 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.
58
Copyright © 2005, Oracle. All rights reserved.
Exploring the Storage Structure
Click the links to view detailed information.
59
Copyright © 2005, Oracle. All rights reserved.
Creating a New Tablespace
5-10
Copyright © 2005, Oracle. All rights reserved.
Creating a New Tablespace (Notes Only Slide)
5-11
Copyright © 2005, Oracle. All rights reserved.
Storage for Locally Managed Tablespaces
5-12
Copyright © 2005, Oracle. All rights reserved.
Storage for Locally Managed Tablespaces Full Notes Page
5-13
Copyright © 2005, Oracle. All rights reserved.
Tablespaces in the Preconfigured Database • •
SYSTEM SYSAUX
•
UNDOTBS1
•
USERS
•
•
TEMP
EXAMPLE
5-14
Copyright © 2005, Oracle. All rights reserved.
Tablespaces in the Preconfigured Database Full Notes Page
5-15
Copyright © 2005, Oracle. All rights reserved.
Altering a Tablespace
5-16
Copyright © 2005, Oracle. All rights reserved.
Altering a Tablespace Full Notes Page
5-17
Copyright © 2005, Oracle. All rights reserved.
Altering a Tablespace Full Notes Page
5-18
Copyright © 2005, Oracle. All rights reserved.
Actions with Tablespaces
5-19
Copyright © 2005, Oracle. All rights reserved.
Actions with Tablespaces Full Notes Page
5-20
Copyright © 2005, Oracle. All rights reserved.
Dropping Tablespaces
5-21
Copyright © 2005, Oracle. All rights reserved.
Viewing Tablespace Information
5-22
Copyright © 2005, Oracle. All rights reserved.
Gathering Storage Information
5-23
Copyright © 2005, Oracle. All rights reserved.
Viewing Tablespace Contents
12061_1_sel_ts_3
5-24
Copyright © 2005, Oracle. All rights reserved.
Enlarging the Database
You can enlarge the database in the following ways: • Creating a new tablespace •
Adding a data file to an existing tablespace
•
Increasing the size of a data file
•
Providing for the dynamic growth of a data file Database
SYSTEM tablespace
INVENTORY tablespace
5-25
Copyright © 2005, Oracle. All rights reserved.
What Is Automatic Storage Management?
Automatic Storage Management • Is a portable and high-performance cluster file system • Manages Oracle database files •
Spreads data across disks to balance load
•
Mirrors data
•
Solves many storage management challenges
Application Database File system Volume manager
ASM
Operating system
5-26
Copyright © 2005, Oracle. All rights reserved.
ASM: Key Features and Benefits
ASM • Stripes files, but not logical volumes •
Provides online disk reconfiguration and dynamic rebalancing
•
Allows for adjustable rebalancing speed
•
Provides redundancy redundancy on a per-file basis
• •
Supports only Oracle database files Is cluster aware
•
Is automatically installed
5-27
Copyright © 2005, Oracle. All rights reserved.
ASM: Concepts ASM disk group
Database
Tablespace
Data file
Segment
Extent
Oracle data block
ASM file
ASM disk
File system file or raw device
Allocation unit
Physical block
5-28
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 Automatic Storage Management (ASM)
5-29
Copyright © 2005, Oracle. All rights reserved.
Practice Overview: Managing Database Storage Structures This practice covers the following topics: • Creating tablespaces •
Gathering information about tablespaces
