This chapter discusses tablespaces and database data files. The objectives important to this chapter are:
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:
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
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
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:
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:
There is a discussion of taking the tablespace offline. It explains three options for doing so:
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.