1 / 23

Managing Database Storage Structures

Managing Database Storage Structures. Objectives. After completing this lesson, you should be able to: Describe the storage of table row data in blocks Define the purpose of tablespaces and data files Create and manage tablespaces Obtain tablespace information

ismet
Download Presentation

Managing Database Storage Structures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing Database Storage Structures

  2. Objectives • After completing this lesson, you should be able to: • Describe the storage of table row data 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)

  3. Storage Structures Logical Physical Database Data file Schema Tablespace Segment Extent OS block Oracle datablock

  4. How Table Data Is Stored Columns Blocks Table A Table B Rows Segment Segment Table Tablespace Extent Row piece

  5. Database Block: Contents Block header Growth Free space Row data

  6. Database Tablespace Data files 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 onetablespace and one database • Are a repository for schema object data

  7. 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. • Use of locally managed tablespaces is recommended. • Dictionary-managed tablespace: • Free extents are managed within the Data Dictionary. • Appropriate tables are updated when extents are allocated or unallocated. • These tablespaces are supported only for backward compatibility.

  8. Click the links to view detailed information. Exploring the Storage Structure

  9. Creating a New Tablespace

  10. Storage for Locally Managed Tablespaces

  11. UNDOTBS1 USERS EXAMPLE SYSTEM SYSAUX TEMP Tablespaces in the Preconfigured Database

  12. Altering a Tablespace

  13. Actions with Tablespaces

  14. Dropping Tablespaces

  15. Viewing Tablespace Information

  16. Viewing Tablespace Contents

  17. Oracle Managed Files (OMF) • Specify file operations in terms of database objects rather than file names. Example: SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata'; SQL> CREATE TABLESPACE tbs_1;

  18. Database SYSTEM tablespace INVENTORY tablespace 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

  19. Automatic Storage Management • Is a portable and high-performancecluster file system • Manages Oracle database files • Spreads data across disksto balance load • Mirrors data • Solves storage-managementchallenges Application Database Filesystem ASM Volumemanager Operating system

  20. ASM: Key Features and Benefits • 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

  21. ASM: Concepts ASMdisk group Database ASM file Data file Tablespace Segment ASM disk Extent File-system file orraw device Allocation unit Oracleblock Physicalblock

  22. Summary • In this lesson, you should have learned how to: • Describe the storage of table row data 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)

  23. Practice 6 Overview:Managing Database Storage Structures • This practice covers the following topics: • Creating tablespaces • Gathering information about tablespaces

More Related