Managing Tablespaces   «Prev  Next»
Lesson 13

Managing Tablespaces File | Conclusion

This concludes the module on managing tablespaces and data files. You should now be comfortable performing the following tasks:
  1. Creating new tablespaces
  2. Enforcing tablespace quotas on users
  3. Querying the data dictionary for information on tablespaces
  4. Adding space to a tablespace
  5. Taking tablespaces and data files offline
  6. Making a tablespace read-only
  7. Dropping a tablespace
  8. Moving data files
  9. Creating temporary tablespaces

Properly managing tablespaces and data files is important to the efficient operation of your database. Always monitor free space so that you know (before your users do) when to increase it. Also, remember that Oracle can sort using temporary tablespaces more efficiently than by using permanent ones, so use temporary tablespaces for sorting wherever possible.

Using Multiple Tablespaces

Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
  1. Separate user data from data dictionary data to reduce I/O contention.
  2. Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
  3. Store the datafiles of different tablespaces on different disk drives to reduce I/O contention.
  4. Take individual tablespaces offline while others remain online, providing better overall availability.
  5. Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
  6. Back up individual tablespaces.

Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles. Review your data in light of these factors and decide how many tablespaces you need for your database design.

Assigning Tablespace Quotas to Users

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.

Glossary

In this module, you were introduced to the following terms:
  2. Data file:
  3. Checkpoint

How is a 'data file' used in Oracle?

In Oracle, a data file is a fundamental component of the database that is used to store data permanently. It is a physical file that resides on the file system of the server machine and contains the actual data of the database. When creating a database in Oracle, you will need to specify the data files that will be used to store the data. The size of the data files will depend on the size of your database and the amount of data that you plan to store in it.
Data files in Oracle are used in several ways. First, they are used to store the actual data of the database. This includes tables, indexes, and other objects that make up the database. The data files are organized into logical structures called tablespaces, which are used to manage the storage of the data files. Secondly, data files are used to manage the storage of undo data and redo data. Undo data is used to roll back transactions in the database, while redo data is used to recover the database in the event of a failure. The data files used for undo and redo data are typically located in separate tablespaces from the data files used for storing the actual data.
Thirdly, data files are used to store temporary data. Temporary data includes sort results, intermediate query results, and other temporary data generated by the database during processing. The data files used for temporary data are located in temporary tablespaces.
Finally, data files are used for backup and recovery purposes. Oracle provides tools for creating backups of the data files, which can be used to recover the database in the event of a failure.Data files are used to store the actual data, undo and redo data, temporary data, and for backup and recovery purposes. Proper management of data files is essential for maintaining the performance, reliability, and integrity of the database.
In the next module, you will about undoing transactions through the use of rollback segments.