Lesson 1

This module will teach you the basics of managing tablespaces and data files for your database. When you have finished this module, you should be able to:

1. Create new tablespaces
2. Query the data dictionary for information on a tablespace
3. Add space to a tablespace
4. Take tablespaces and data files offline
5. Make a tablespace read-only
6. Drop a tablespace
7. Move data files
8. Use temporary tablespaces

How a DBA configures the layout of the tablespaces in a database directly affects the performance and manageability of the database. In this module, we’ll review the different types of tablespaces as well as how temporary tablespace usage can drive the size and number of tablespaces in a database leveraging the temporary tablespace group feature introduced in Oracle 10g. We’ll also show how Oracle’s Optimal Flexible Architecture (OFA), supported since Oracle 7, helps to standardize the directory structure for both Oracle executables and the database files themselves; Oracle Database 11g further enhances OFA to complement its original role of improving performance to enhancing security and simplifying cloning and upgrade tasks. A default installation of Oracle provides the DBA with a good starting point, not only creating an OFA-compliant directory structure but also segregating segments into a number of tablespaces based on their function. We’ll review the space requirements for each of these tablespaces and provide some tips on how to fine-tune the characteristics of these tablespaces. At the end of the module, we will provide some guidelines to help place segments into different tablespaces based on their type, size, and frequency of access, as well as ways to identify hotspots in one or more tablespaces.
Tablespace Architecture
A prerequisite to competently setting up the tablespaces in your database is understanding the different types of tablespaces and how they are used in an Oracle database. In this section, we will review the different types of tablespaces and give some examples of how they are managed. In addition, we’ll provide an overview of Oracle’s Optimal Flexible Architecture and how it provides a framework for storing tablespace datafiles as well as Oracle executables and other Oracle components, such as redo log files, control files, and so forth. We’ll also review the types of tablespaces by category:

1. SYSTEM tablespaces,
2. the SYSAUX tablespace,
3. temporary tablespaces,
4. undo tablespaces, and
5. bigfile tablespaces

and describe their function.
In the next lesson, you will learn how to create a tablespace and how to specify default storage options for that tablespace.