CIS 421b: Database Administration I

Chapter 6: Basic Storage Concepts and Settings


This chapter discusses tablespaces and database data files. The objectives important to this chapter are:

  1. Understanding logical and physical file structures
  2. Creating tablespaces
  3. Configuring storage for files and tablespaces
  4. Undo

The chapter begins with a review that an Oracle9i instance is the Oracle programs that are running, and the memory space allocated to them.

The database that is accessed through an instance is composed of the files discussed in previous chapters (control files and redo log files), and the actual data files. The control files, redo log files, and database files are stored as actual physical files on the drives of a computer. These files are called physical structures, because they have an actual location in the real world.

Oracle complicates the issue by adding logical aspects to the files. A list of logical structures is presented:

  • data block - like the concept of storage blocks on a hard drive, but related to Oracle programs. Oracle reads and writes data in blocks that can range in size from 2 KB to 32 KB. This block size can be set by the administrator. Set larger sizes if you have lots of RAM and fast hard drives, set smaller block sizes for slower drives and less RAM
  • extent - a contiguous group of data blocks. Object in the database, such as tables, are allocated an "extent" when created. When they grow beyond their initial allocation, they must be allocated another extent to expand into.
  • segment - an object can have one or more extents, but all of its extents are contained in its segment. Each object will have one segment (except for partitioned tables and indexes, which have one segment per partition)
  • schema object - defined in earlier chapters; any object that you can create and store in the database
  • tablespace - the collection of all schema objects needed for a database. Database objects must be associated with one tablespace.

So, Oracle stores data in blocks. There are typically several blocks in an extent. There could be several extents for an object as it grows. All of an object's extents are contained in its segment. Segments usually have a one to one correspondence with schema objects. There could be any number of schema objects in a tablespace.

Before you can create a datafile, you must have a tablespace to put the datafile in. To create a tablespace, you must specify several settings for it. The text offers an example of the syntax for such a command. The first two lines are:

CREATE TABLESPACE name_of_tablespace
DATAFILE name_of_datafile SIZE xx AUTOEXTEND on_off

You must provide a name for the tablespace and the first datafile. You may also specify a default size for a datafile, and set the autoextend feature to on or off. If you are using an existing file, you can omit the size clause, and use the keyword REUSE, leaving the file size the same. You could, however, use the REUSE command with the SIZE command, which would erase the file. The AUTOEXTEND option can be left off if you are using OMF.

The command continues:

The next line will have either the word TEMPORARY or PERMANENT. Temporary tablespaces are only good for the current session. Permanent tablespaces are persistent between sessions.

EXTENT MANAGEMENT can be set to LOCAL or DICTIONARY. The text tells us that a locally managed tablespace has a "bitmap" of all used and open space, as well as all extent locations. The use of the word bitmap is unfortunate, since it is the name of a graphic file type, totally unrelated to this discussion. The author means that there is a collection of metadata about these objects, not a picture of them. If the management type is set to dictionary, then this data is stored in the Oracle data dictionary.

ONLINE marks the tablespace as available. OFFLINE marks it as unavailable. The default when a tablespace is created is ONLINE.

If you set EXTENT MANAGEMENT LOCAL, you can set the value of SEGMENT MANAGEMENT. AUTO is the recommended setting. The text discusses the storage problems created by extents that are used, then released to free storage (deallocated), and sit unused until coalesced into units large enough to be used by other schema objects. The process of coalescing extents is done by SMON, but does not happen automatically in the background unless you use DICTIONARY managed extents. Another argument for setting Oracle to manage itself, with some guidelines about how you want it done.

When setting EXTENT MANAGEMENT DICTIONARY, you should follow the offered syntax to set a minimum extent size for all extents that are subsequently created
and set default values for initial sizes, incremental sizes for the next extent in a segment, and the minimum and maximum number of extents for any schema object.

Oracle allows you to set the initial size to one value, the next size to another, and to allow each subsequent extent to be bigger by specifying a percentage to increase over the last size with the PCTINCREASE value. Although this is allowed, Oracle recommends that this value be set to 0, to keep extents the same size.

The text presents a list of several types of segments:

  • data segment - stores datafile data
  • index segment - stores data for indexes
  • temporary segment - created automatically as needed by SQL commands
  • rollback segment - stores undo information; created automatically if using automatic undo management
  • LOB segment - holds data about Locator Objects (objects that are pointers to other objects, such as large files)

The chapter continues with a discussion of storage settings. Some have been discussed already, those that are used when creating tablespaces. The text offers a list of settings that can be changed and actions you can perform with the ALTER TABLESPACE command:

  • DEFAULT STORAGE settings for objects
  • Toggle between LOGGING and NOLOGGING
  • Toggle between PERMANENT and TEMPORARY
  • Toggle between READ ONLY and READ/WRITE
  • Coalesce space (must be contiguous)
  • Add or rename datafiles or temporary files

There is a discussion of taking the tablespace offline. It explains three options for doing so:

  • NORMAL - waits for all pending data to be written to files, then goes offline
  • TEMPORARY - to be used when a error is generated with the NORMAL option, in order to repair the datafile that caused the error
  • IMMEDIATE - takes the tablespace offline immediately, which prevent pending data from being written, which causes the need to recover the tablespace from a backup, and to apply the changes from archived and online redo logs. This kind of recovery is called a media recovery.

Oracle recommends storing tables that rarely change in read-only tablespaces. This kind of tablespace cannot be altered by users, and only needs a backup after any change is made by an administrator.

Tablespaces can be dropped whether they contain data or not. To drop everything from a tablespace use this format:

DROP TABLESPACE name_of_tablespace

Several views are available for examining tablespaces. Note the ones called DBA_DATA_FILES, DBA_TABLESPACES, DBA_EXTENTS, and DBA_SEGMENTS.

The last topic in the chapter is undo data. We are told that when changes are made to data, the old version of the data is kept in an undo block which is used as the official copy, shown in any query except those run by the user who made the change, until that user finally issues a COMMIT command that includes that change.

Oracle recommends automatic management of an UNDO tablespace.