'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 5 - Managing Your Database Space

Using Oracle8

Chapter 5

Managing Your Database Space

Space Management Fundamentals

The basic storage unit in an Oracle database is the Oracle block (or database block). This is the smallest unit of storage that will be moved from disk to memory and back again. Oracle block sizes range from 2KB to 32KB. You obviously couldn't store a complete table on a single block, so blocks are grouped into segments to store amalgams of data. There are various types of segments:

The DBA's role in space management
Space management is an ongoing task for most of you. Unless you have a completely static database, tables and indexes will regularly grow, or shrink, in size. You need to ensure that sufficient space is available for this to occur without interruption to the ongoing processing. You also need to help ensure that the space is being used efficiently.

SEE ALSO
See how to use clusters on

You may have very large segments in your database, and it may be impossible to put the whole thing into a set of contiguous Oracle blocks. Oracle therefore builds its segments out of extents, which are sets of logically contiguous blocks. "Logically contiguous" means that the operating system and its storage subsystems will place the blocks in files or in raw partitions so that Oracle can find them by asking for the block offset address from the start of the file. For example, block 10 would begin at the (10

X
Oracle block size) byte in the data file. It doesn't matter to Oracle if the operating system has striped the file so that this byte is on a completely different disk than the one immediately preceding it. The database always accesses blocks by their relative position in the file.

A large segment may have several, or even several hundred, extents. In some cases it will be too big to fit into a single file. This is where the last Oracle storage construct plays a part. Rather than force users to deal with individual files, Oracle divides the database into logical units of space called tablespaces. A tablespace consists of at least one underlying operating system file (or database data file). Large tablespaces can be composed of two or more data files, up to 1,022 files.

Every segment or partition of a partitioned segment must be entirely contained in a single tablespace. Every extent must fit entirely inside a single data file. However, many extents can comprise a partition or a non-partitioned segment, and the different extents don't all have to be in the same data file. Only one type of database object, a BFILE (binary file), is stored directly in an operating system file that's not part of a tablespace.

There are six reasons to separate your database into different tablespaces:

Recommendation for using multiple tablespaces
Although Oracle doesn't prevent you from creating all your segments in a single tablespace, Oracle strongly recommends against it. You can control your space more easily by using different tablespaces than you can if everything were placed in a single tablespace. Multiple files let you build your tablespace in a manner that helps you improve space usage as well as database performance.

The first reason to use multiple tablespaces is to keep the segments owned by user SYS away from any other segments. The only segments SYS should own-and the only ones that Oracle will create and manage for you-are those belonging to the data dictionary.

The data dictionary
The data dictionary is the road map to all the other database segments, as well as to the data files, the table-space definitions, the Oracle user-names, passwords and related information, and many other types of database objects. The dictionary needs to be modified as objects are added, dropped, or modified, and it must be available at all times. By keeping it in its own tablespace, you're less likely to run out of room (which would bring the database to a complete halt if it prevented SYS from modifying the dictionary).

A second reason to use different tablespaces is to control how much space different schemas can take up with their segments. Each user can be assigned just so much space in any tablespace and doesn't need to be assigned any space at all in some tablespaces. Some end users may have no space allocated to them at all because their database access consists solely of manipulating segments that belong to the application owner.

The third reason to manage your segments in different tablespaces has to do with space usage by the extents belonging to different segments. As you can imagine, most databases have segments that are large and some that are small. To make effective use of the space, you would assign different-sized extents to these objects. If you mix these extents in a single tablespace, you may have problems if you need to drop or shrink the segments and try to reuse the freed space.

Consider the example of a kitchen cabinet where you keep all your canned goods on one shelf (see Figure 5.1). After a trip to the grocery store, you can almost fill the shelf with cans of different sizes. Suppose that during the week you take out half a dozen or so of the smaller cans. After another trip to the grocery store, you couldn't simply put large cans in the spaces where the small ones came from, even if there were fewer large cans. You might have enough space if you rearrange the cans, but none of the spots is initially big enough for a single, large can.

Figure 5.1 : You may have space management problems if you try storing different-sized objects together.

Now suppose that you take out only large cans and then put medium-sized cans in their place. You still have some space around the medium-sized cans, but not enough to store even a small can. Again, you could find room for a small can, but only by shifting things around, as shown in Figure 5.2.

Figure 5.2 : You'll run into space problems when replacing objects of different sizes.

When large and small extents try to share limited tablespace storage space, they can "behave" like the cans. Space can be freed when extents are removed, but it's not necessarily of a useful size. Unlike you reorganizing the kitchen cupboard, however, the database can't easily shift the remaining extents around to make the space more useful. When space in a tablespace consists of an irregular checkerboard of used and free block groups, it's said to be fragmented. Much of the free space may never be reusable unless the extents are somehow reorganized. You can prevent such fragmentation by allowing only extents of the same size in the tablespace. That way, any freed extent is going to be exactly the right size for the next extent required in the tablespace.

Avoiding different free space extent sizes requires different tablespaces
To allow different-sized extents in your database without mixing them in the same storage space, you need different table-spaces.

A variant of the fragmentation problem provides a fourth value for multiple tablespaces. Some segments are very unlikely to be dropped or truncated. For example, a mail-order business' CURRENT_ORDERS table unlikely will do anything but grow-or at least stay about the same size-as new orders are added and filled orders removed. On the other hand, you may have a data warehouse in which you keep the last five years' order records. If you build this table as a series of 60 month-long partitions, you'll be able to drop the oldest one each month as you add the latest month's records.

Tables with different propensity to fragment free space
The CURRENT_ORDERS table will never contribute to a fragmentation problem because its extents never go away. The data warehouse partitions, however, are dropped on a regular basis, so they'll have a high propensity to cause fragmentation.

Thus, the fourth reason to keep segments in different tablespaces is to separate segments with a low, or zero, propensity to fragment space and those with a high likelihood of causing fragmentation. This way, the long-term objects, if they do need to grow, won't have to hunt around for free space of the right size.

The fifth reason to use different tablespaces is to help distribute the data file reads and writes across multiple disk drives. You can use lots of different data files in a tablespace and place them on different disk drives, but you may not be able to control which extents are placed in which file after doing that. If you're lucky, the amount of disk access will be even across all the drives. If you aren't so lucky, you might have a situation where the two busiest extents in your database are in the same file.

For example, if the mail-order house is going into its busy holiday season sale period, it will probably need to use the empty blocks at the end of the CURRENT_ORDER table for the additional orders. If the extent holding these blocks is in the same data file as the index blocks where the newest order numbers are being saved, you'll have excessive disk contention; each new order will use a block from the table extent and a block from the index extent.

If you keep segments that will likely cause concurrent disk access (such as tables and the indexes on those tables) in different tablespaces, you can guarantee that the files making up the different tablespaces are stored on separate disk drives.

A database-management issue is the final reason to use different tablespaces. During its life, a database will need to be backed up and possibly repaired if a disk crashes or otherwise corrupts data.

Tablespace damage can be pervasive
If any part of a tablespace is damaged, the entire tablespace becomes unusable until the damage is fixed. If every segment belonging to an application were stored in a single tablespace and that tablespace was damaged, nobody could use that application.

However, if you split segments that belong to different functional areas of the application (such as order entry and accounts receivable) and use different tablespaces for these, a data file problem may not be so intrusive. A failure with a data file in the accounts-receivable tablespace could be undergoing repair without any impact being felt by the order takers using the order-entry tablespace.

Similarly, a tablespace containing tables that rarely change-such as lookup tables for state codes, part number and part name references, and so on-may not need regular backing up, whereas CURRENT_ORDERS may need very frequent backups to reduce the tablespace recovery time if there were a failure.

Backing up tablespaces on different schedules
To minimize the time it takes to back up less-often used segments, back up different table-spaces on different schedules. In fact, you can define a truly read-only tablespace to the database and then back it up only once when you've finished loading the required data into it. Of course, if you mix the static tables and the busy tables in the same tablespace, you have to back them all up equally often.

SEE ALSO
To learn more about objects, LOBs, and BFILEs,

Suggested Tablespaces

I recommend that every DBA create certain tablespaces for a production database. The reasons for these different tablespaces stem from the previous discussion. Let's begin with the SYSTEM tablespace, the only mandatory tablespace in every Oracle database.

SYSTEM Tablespace

Every Oracle database must have one tablespace-SYSTEM. This is where the user SYS stores the data dictionary information needed to manage the database. You should create additional tablespaces based on the expected use of your database. If you don't do this and use only the SYSTEM tablespace, you'll violate most of the reasons for using different tablespaces recommended in the previous section. Several other things will happen as well:

Maintain the integrity of the SYSTEM tablespace
You should never need to create any object directly in the SYSTEM tablespace, regardless of which userid you use to connect to the database. This table-space should be reserved for the recursive SQL executed behind the scenes as part of database creation or the execution of standard SQL statements.

I hope this list of possible problems has convinced you to use additional tablespaces, such as those described in the next few pages.

Rollback Segment Tablespaces

The tablespace for rollback segments will contain all the database's rollback segments with one exception-the SYSTEM rollback segment. It is maintained automatically in the SYSTEM tablespace.

Keep your rollback segments separate from other database objects for a number of reasons:

Rollback segment shrinkage
Although you can define rollback segments to shrink by themselves if they grow too large, you can also use a special ALTER ROLLBACK SEGMENT command option to shrink them manually.

SEE ALSO
For details of the ALTER ROLLBACK SEGMENT command,

Rollback segments can be defined to shrink themselves automatically if they grow larger than needed. Thus, they have a very high propensity for fragmenting the free space. Fortunately, a rollback segment is required to use the same size extents as it grows and shrinks, so it can reclaim any empty space it leaves behind when shrinking.

Maintaining multiple rollback segment tablespaces
If all the rollback segments in a single tablespace are sized identically, they can even claim the space released by the other segments. You shouldn't have many problems with space overuse or waste in a rollback segment tablespace with this arrangement, as long as they don't try to grow too much all at the same time. Should you need roll-back segments of different sizes, therefore, you should consider building a new rollback segment tablespace and keeping the larger ones in one tablespace and the smaller ones in the other.

Another reason for using a tablespace for rollback segments is that you create them for use by anyone. Users don't need to have a space quota on the tablespace where the rollback segments reside. This allows the rollback segments to exist without having to contend for space with the segments that belong to an application (suddenly having their free space taken up by a table created with excessive storage requirements, for example).

The final reason for keeping rollback segments in their own tablespace(s) is that you can help avoid contention for the disk. When a table is being modified, not only does Oracle modify the contents of the table block(s), but a set of rollback entries is also stored in a rollback segment. If the table and the rollback segment belonged to the same tablespace, the blocks being changed in each of them could be in the same data file on the same disk.

Temporary Tablespaces

Temporary tablespaces are for the temporary segments built by Oracle during sorting and related operations, when the amount of memory available to the server is insufficient to complete the task. The important characteristic of temporary segments is that Oracle creates and manages them without any input from the users. Consequently, unlike the other segments (except the bootstrap segment), there's no CREATE syntax to identify which tablespace the segment is created in, nor what size or number of extents to use. Neither is there any DROP command, or any other command, that lets you release the space from temporary segments.

Default behavior of temporary segments
Temporary segments obtain their storage characteristics solely from the tablespace definition. Hence, unless you have a tablespace for other types of segments that need identical storage characteristics to your temporary segments, you'll need a dedicated, temporary segment tablespace. Even if you have a tablespace that appears to be able to share storage characteristics for regular segments and for temporary segments, you may want to create a separate one for your temporary segments. The reason for this is that temporary segments are so named because of their default behavior. Because they're being used just to store intermediate values of an ongoing sort, they aren't really needed by the server process when the sort is complete. Hence, they can easily fragment the space in a tablespace as they are created and dropped.

By default, temporary segments are dropped as soon as their work is complete. Obviously, the dropping of these segments on a transaction boundary makes them very prone to fragmenting free space. You can help mitigate the fragmentation problems by ensuring that the extents used by the temporary segments are all exactly the same size.

The ephemeral nature of temporary segments by itself makes them candidates for their own tablespace. However, there's one further consideration-you can create a tablespace or alter an existing tablespace to contain only temporary segments. By doing this, you change the default behavior of temporary segments. Rather than drop the segment when it's no longer needed, the server simply records in a special data dictionary table that the extents in the segment are now free. Any other server needing to use temporary space can query this table and locate available temporary extents. In this way, the database will save the space allocated to temporary segments indefinitely and assign it for use as needed by various servers.

Reduce database overhead with TEMPORARY-type tablespaces
The characteristic of preserving temporary segments in TEMPORARY-type tablespaces saves a lot of recursive SQL associated with creating and managing the space for temporary processing. In the long run, reducing recursive SQL speeds the processing of the entire data-base. It's essential that you have a tablespace dedicated to temporary segments if you want to take advantage of this behavior. You aren't allowed to place anything other than temporary segments in a table-space defined as a temporary type.

As with the other tablespaces being discussed, you aren't limited to just one temporary tablespace (other than SYSTEM). You can add as many as you need to avoid contention for the space in a single temporary tablespace. Temporary space is assigned to users as part of the user definition. If you need to, you can subdivide your user community to access different temporary tablespaces.

A final benefit of keeping your temporary tablespaces separate from other tablespaces is that because the use of temporary space is the result of processing being performed behind the scenes for the user, you don't need to assign space in temporary tablespaces to your users. You can keep your temporary tablespace(s) clear of other user-created segments by disallowing any storage on them. As with the SYSTEM tablespace, this will avoid problems that could occur should the required space be taken up by segments that don't belong in this reserved space.

User Data Tablespaces

For most of you, the largest amount of storage in your database will be taken by rows in your applications' tables. You should realize that these data segments don't belong in the SYSTEM tablespace, nor should they be stored with your rollback segments or your temporary segments. I recommend that you build one or more tablespaces to store your database tables.

You would need more than one user data tablespace for a number of reasons, all related to the discussion in the section "Identifying Tablespace Uses":

Managing tablespace extent size
You may want to standardize your tables to three or four extent sizes. This will reduce the number of different tablespaces you'll need to manage while allowing you to realize the benefits of having all the extents in a tablespace be the same size. In particular, you won't have to concern yourself with the frequency at which extents are added and dropped. Such activity won't lead to poor space allocation because every dropped extent leaves free space exactly the same size a new extent would require.

By the time you finish planning your user data tablespaces, you may have divided them for a combination of the reasons discussed here. It wouldn't be unreasonable to have two or three tablespaces holding tables with same-sized extents (each with a different backup frequency requirement), and another two or three with the same extent sizes containing tables that have the same backup requirements but have a high contention potential.

Index Tablespaces

Indexes on tables are often used by many concurrent users who are also accessing the tables as part of the same transaction. If you place the indexes in the same tablespace as the tables they support, you're likely to cause disk contention; this would occur as queries retrieved index blocks to find where the required rows are stored and then retrieved the required data blocks. To avoid such contention, you should create a separate set of tablespaces for your indexes.

As with tables, you may find that you need to size your index extents differently, and that you may have indexes supporting tables from different applications. Just as with your user data tablespaces, therefore, you should plan on building multiple index tablespaces to support different extent sizes and backup requirements and to maintain application independence in case of disk failure.

If you use the Oracle8 partitioning option, you may need to revise your index and user-data tablespace design. In some cases it's beneficial to build locally partitioned indexes in the same tablespaces as the parent partition. This helps maintain the availability of the partitions during various tablespace maintenance activities.

SEE ALSO
For additional details on rollback segment management,

Understanding File Types: File Systems Versus Raw Devices

Some operating systems allow you to create raw partitions and use these for your Oracle database files instead of standard file-system files. In certain cases, raw devices can offer you improved performance. In addition, if you're using a UNIX system that doesn't support a write-through cache, you'll have to use raw devices to ensure that every write performed during a checkpoint or a log buffer flush is actually written to the physical disk.

Raw devices and Oracle Parallel Server
If you're going to use the Oracle Parallel Server option on UNIX or Windows NT, you must create all your database files-including the redo log files, control files, and data files-on raw devices. The various instances can't share the files if you don't do this.

There are some drawbacks to raw devices:

Understanding the Benefits of Striping Data

In some situations, no matter how well you've segregated your data into separate tablespaces, particularly busy tables or indexes will cause "hot spots" on one or more disks. To resolve this, you may need to build your data files by using an operating system striping mechanism. Such mechanisms include logical volume manager (LVM) software or redundant arrays of inexpensive disks (RAID). By striping the data file across many disks, you reduce the likelihood that any particular table or index will have all its busiest blocks on one single disk.

If you decide to stripe your data files, you need to determine an appropriate stripe size. Optimal stripe sizes depend on a number of factors, the three key factors being the implementation of the striping software, the Oracle block size, and the type of database processing against the data.

Stripe sizes for query-intensive data
If your database is query-intensive- that is, the users are usually executing queries and very infrequently performing INSERTs, UPDATEs, or DELETEs-you need a stripe size that will support sequential processing of data. This will help queries that need to access indexes via range scans (many records with the same value or a set of values between an upper and lower bound) or tables via full table scans. You should set the stripe size for such databases to be a minimum of two times the value of the parameter DB_FILE_MULTIBLOCK _READ_COUNT and, if larger, to an integer multiple of this parameter's value. This will help ensure that all the blocks requested in a single read, when performing a full table scan, will be on the same disk, requiring only one disk read/write head to be moved to the required starting position.

Operating system or disk-striping mechanisms differ widely between vendors. Some offer large, guaranteed cache areas used for reads, writes, or both. A cache can overcome some performance slowdowns that can occur when you need to sequentially read blocks that are scattered across many different disks. Others, such as RAID, provide striping as a side benefit of various levels of disk failure resilience, but can slow certain activities to provide you protection against disk failure. Certain levels of RAID are better left unused for particular file types, such as those with large amounts of data written to them sequentially. For example, redo logs, while not part of a tablespace, may suffer a performance penalty if stored on RAID Level 5. If you have any tables that collect data in a similar sequential fashion, however, you should also try to avoid placing their data files on RAID Level 5 devices.

Oracle block size is an important factor when striping data files that contain tables or indexes that will typically be accessed via random reads or random writes. You'll usually see this type of activity when the database is used primarily for transaction processing. In these cases, you should plan to make your stripe size at least twice the size of an Oracle block but, all things being equal, not too much larger. Whatever stripe size you choose, however, make sure it's an integer multiple of your Oracle block size.

Adding New Tablespaces

You automatically create your SYSTEM tablespace when you build your database. As discussed earlier in this chapter, you should add further tablespaces to meet your database's specific requirements. The following sections go over the process of creating tablespaces with various characteristics.

Creating a Tablespace

The very first tablespace you create is the SYSTEM tablespace, always part of an initial database creation. Additional tablespace creation isn't that much different from the SYSTEM tablespace creation. As with the CREATE DATABASE command, the CREATE TABLESPACE command uses a DATAFILE clause to identify the data file(s) and size(s) you want to associate with the tablespace. The syntax for the CREATE TABLESPACE command is as follows:
Identifies file(s) to be used and their characteristics
Sets minimum size of used and free extents in table-space
Determines whether certain SQL commands will avoid creating standard redo log entries
Controls extent behavior for segments created without defined storage options
Determines status of table-space after creation
Defines tablespace to hold regular segments
Defines tablespace to hold only temporary segments

We'll examine the DEFAULT STORAGE clause in the following section. In the meantime, look at the DATAFILE clause in more detail. This clause can be applied to any tablespace's data files (including the SYSTEM tablespace), although most DBAs are content to use it simply to name and size the data file(s) for this tablespace. The DATAFILE clause's full syntax is as follows: 

DATAFILE filename
     [SIZE integer [K|M]]
     [REUSE]
     [AUTOEXTEND OFF|ON [NEXT integer [K|M]]
     [MAXSIZE [UNLIMITED|integer [K|M]]]]

Recall from the earlier section, "Understanding File Types: File Systems Versus Raw Devices," that you can use native file system files or raw partitions for your tablespace's files. The data file's name will therefore be a file-system filename, a raw partition name, or possibly a link name pointing to one or the other types of file. In the case of a file-system file, the file will be created for you unless you use the REUSE clause. In this case, Oracle will create the file if it doesn't already exist, but will overwrite an existing file as long as the SIZE clause, if included, matches the size of the existing file. If you don't specify REUSE and the file already exists, you get an error message, and the tablespace won't be created.

The REUSE option can be destructive
Be careful with REUSE; any current entries in the file will be overwritten and lost when it's implemented. A raw partition can always be reused, destroying its existing content-even if you don't include the REUSE keyword.

If you name a raw partition (directly or via a link), the partition must already exist; otherwise, Oracle will attempt to create a standard file with the name of the partition. Because Oracle expects raw partitions to exist before being named in the CREATE TABLESPACE command, the REUSE clause really has no effect. The SIZE clause with raw partitions must be a few blocks smaller than the actual partition size; this allows space for operating system header information. Two operating system blocks are usually sufficient.

Simplify your raw partition sizing
You may want to keep your arithmetic simple when sizing raw partitions for Oracle files by allowing 1MB for the overhead in each partition. Thus, you would create a 101MB partition to hold a 100MB file.

The AUTOEXTEND option determines whether a data file can grow automatically should a new extent be required by a segment and there's an insufficient number of contiguous free blocks. You don't have to use this clause (when you create the tablespace) to be able to grow your tablespace, as discussed later in the "Adding and Resizing Data Files" section. If you decide you want your files to be able to grow automatically, you should be aware of the impact of the following behaviors:

SEE ALSO
For more information on creating a database,
To learn about temporary segments and how they're used in TEMPORARY-type tablespaces,

Setting Default Storage Values

As noted in the earlier sections "Identifying Tablespace Uses" and "Suggested Tablespaces," a tablespace should ideally contain only segments with equal-sized extents. One way to help you maintain such a scheme is to assign to each tablespace the desired storage options as defaults. The CREATE TABLESPACE command's DEFAULT STORAGE clause is the means to achieve this.

Your default storage values can be overridden!
After a user has the privileges necessary to build segments, you can't prevent them from overriding the tablespace default storage values. You need to take on the responsibility of building all segments yourself if you don't want to risk this. To reduce the burden of work for your-self you can have the users build script files containing the required CREATE commands, which you can simply execute on their behalf. Of course, you should check that

Whenever a database segment such as a table, a rollback segment, or an index is created, a set of storage-related information is activated or stored with the segment definition. This storage information defines the size of the first extent belonging to the segment, the size of the second extent, the size of the subsequent extents, and the initial and the maximum number of extents that will be assigned to the segment. In the case of rollback segments, there's also a value associated with the optimal size of the rollback segment, which, if used, will cause extents to be dropped automatically if the overall size exceeds the desired maximum.

SEE ALSO
To learn more about managing rollback segments and the OPTIMAL storage option,

Although each user who creates a segment can assign these storage values individually, they also can be inherited from the tablespace's definition. Temporary segments are a little different in that users don't get to create these; they're built as needed by the system on behalf of a user and, as such, always inherit the tablespace storage values. If you've built your tablespaces such that each one is designed to hold only one extent size, you can define the tablespace to provide this size by default. You can then advise those users who create segments (if it's someone other than yourself) that they shouldn't include the STORAGE clause in their CREATE statements. This not only simplifies their work, but keeps your tablespace extents defined as you planned.

You define the inheritable storage values for a tablespace with the DEFAULT STORAGE clause. Here is the syntax for that clause: 

DEFAULT STORAGE (
Sets size of initial extent in bytes, with optional K or M to specify kilobytes or megabytes
Sets size of second extent in bytes, with optional K or M
Defines increase, measured as a percentage, by which each extent beyond the second will grow
Sets number of extents each segment will be assigned when created
Sets greatest number of extents that segment will be assigned

You need to set INITIAL equal to NEXT and PCTINCREASE equal to 0 in order for the tablespace to create every extent, by default, with the same size. Remember that even though you set these defaults, every CREATE statement that builds a segment in the tablespace can override them. This is true even if you allow users to include a STORAGE clause simply to change the number of preliminary or maximum extents (MINEXTENTS and MAXEXTENTS). As soon as they can use a CREATE command, you can't restrict what's included in the related STORAGE clause.

The following listing shows a command being used to create a tablespace with three data files, one of which is auto-extendible, and with a default storage clause to build all extents with 10MB of storage: 

CREATE TABLESPACE extra_room
     DATAFILE     '/d1/oracle/exrm01.dbf' SIZE 1000M,
                  '/d2/oracle/exrm02.dbf' SIZE 1000M,
                  '/d3/oracle/exrm03.dbf' SIZE 1000M
                  AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
     DEFAULT STORAGE (
          INITIAL 10M
          NEXT 10M
          PCTINCREASE 0)
/

Tablespace Management

After you create your tablespaces, you may find that they aren't quite what you needed. To rectify this situation, you can drop and recreate the tablespace. In some cases you can modify it. The latter tends to be the easier solution if segments are already created in the tablespace, because dropping such a tablespace generally requires you to find a way to save and reload these segments.

Changing the Characteristics of a Tablespace

You can see a tablespace's current characteristics by viewing the data dictionary table DBA_TABLESPACES. Many of these characteristics can be changed with the ALTER TABLESPACE command. The syntax for the command is as follows: 

ALTER TABLESPACE tablespace_name option;

Table 5.1 summarizes the options available with the ALTER TABLESPACE command. (You can only use one option at a time with the command.) Because some of the options are a little more complex than you might infer from Table 5.1, the following sections explain why you might want to use them.

Table 5.1  ALTER TABLESPACE options
Option:
Purpose:
OFFLINEMakes a tablespace unavailable for use and prevents access to its contents
ONLINEReturns a tablespace from OFFLINE to ONLINE accessible status
BEGIN BACKUPReadies the files in the tablespace for hot backup
END BACKUPReturns the tablespace's files to normal status following a hot backup
LOGGING or NOLOGGING Sets the default logging behavior of new objects created in the tablespace
RENAME DATAFILEIdentifies the new name of a data file to a tablespace when the file itself has been changed in the operating system
COALESCECoalesces contiguous areas of free space into a single free extent
MINIMUM EXTENTSets the minimum size of any extent, used or free, in the tablespace
READ ONLYPrevents further writes into the tablespace
READ WRITEAllows writes into the tablespace after it's read-only
TEMPORARYConverts a tablespace to one that holds only temporary segments
PERMANENTConverts a temporary tablespace to a permanent one
DEFAULTChanges the default extent characteristics assigned to any new STORAGE segments built in the tablespace
ADD DATAFILECreates one or more additional data files for the tablespace

Removing Access to a Tablespace

You may need to prevent access to a tablespace for a number of reasons. For example, you may want to back it up without users being able to change its contents, or you may need to perform maintenance or recovery on one of its data files. You can take a tablespace offline to prevent further read and write access.

The ALTER TABLESPACE OFFLINE command that you use to accomplish this has three options: NORMAL, TEMPORARY, and IMMEDIATE. When you take a tablespace offline with the NORMAL option, Oracle immediately prevents further retrieval from that tablespace. However, it will complete a checkpoint on its data files before shutting it down completely; any changed blocks belonging to the tablespace still in the database buffer cache will be copied back to disk. This results in an internally consistent tablespace, so it can be brought back online at any time without any further processing.

Bringing a tablespace back online
The ALTER TABLESPACE ONLINE command will bring an offline tablespace back online, provided that it was successfully check-pointed when it went offline and that all its files are currently online. If one or more of these conditions isn't true, the data file(s) will need recovery before the tablespace can be brought back online. Tablespace and data file recovery are discussed in Chapter 14, "Performing Database Recovery."

The TEMPORARY and IMMEDIATE options of the OFFLINE command don't necessarily complete checkpoints. This can result in a tablespace inconsistent with the rest of the database that therefore may need media recovery when it's brought back online. To guarantee that the redo information required for this recovery is available when needed, the database must be running in ARCHIVELOG mode. The difference between TEMPORARY and IMMEDIATE is that the former will attempt to complete checkpoints on all the files, ignoring any not available for writes, whereas IMMEDIATE won't even attempt to process any checkpoints.

Hot Backups of a Tablespace

A hot tablespace backup is one made while access to the tablespace's data files continues. Even though making a copy of all data files associated with a tablespace may take a number of minutes, Oracle will allow users to read blocks from those files and modify those blocks, as well as allow DBWR to write the changes back to disk. This can result in apparent anomalies in the backup set. A table with blocks in two different data files could have some blocks in each file modified by a single transaction. The backup copy of one file could contain blocks as they were before the change, whereas the backup of the second file could contain changed images of other blocks.

Oracle can resolve such anomalies by applying redo records to the backup files if they're used to replace damaged online files. To do this, the file needs to record the earliest time at which a block may have been changed but not copied into the backup file. This information is automatically available in a file header block, but normally this information will change over time. To prevent such a change from occurring, so as to lock in the time at which the physical backup begins, Oracle needs to freeze the header block for the duration of the backup.

As the DBA, you need to issue ALTER TABLESPACE...BEGIN BACKUP before starting the physical backup of files in the tablespace. This will accomplish the freeze of the header blocks in the data files belonging to the tablespace as discussed earlier. You need to unfreeze these blocks when the backup is completed. You can achieve this with the ALTER TABLESPACE...END BACKUP command. Although you can place a number of tablespaces in backup mode simultaneously, you should understand one other characteristic of a tablespace's backup mode. While in backup mode, Oracle has to create additional redo information to guarantee data consistency within blocks.

Block consistency during hot backups
During a hot backup of a data file, it's possible for the operating system to copy different parts of an Oracle block to the backup medium in two separate read/write operations. If DBWR happened to write a new image of a block to the data file between the two operations, the backup would contain a fuzzy block image-part of the block would represent integral data at one point in time, while the remainder of the block would contain data from a different time. To ensure that a complete valid block image can be restored when recovering from this backup, Oracle places a complete block image into the redo log before any changes can be made to a block from a tablespace in backup mode. When recovering from the log, this valid block image is first copied over the possibly inconsistent block from the backed-up data file, and then the changes recorded in the redo are applied as usual.

The redo logs needed to bring the data back to a consistent state must be available in order for the backed-up files to be useful in a recovery effort. To ensure this, you have to be running your database in ARCHIVELOG mode, which guarantees that all redos written to the online redo logs are copied elsewhere before the entries are overwritten by later transactions. You'll receive an error message if you try to place a tablespace into backup mode and you aren't archiving your redo.

Controlling Logging Behavior

A number of SQL commands can execute in Oracle without generating redo logs. These commands work with an existing set of data and therefore can be re-executed if they fail against the same data source. For this reason, you wouldn't have to rely on the existence of redo entries if there were an instance failure part of the way through the execution of the command. In addition, the SQL*Loader utility can run without logging because-again-the data source will still be available if the instance should fail before the load completes. These commands can be executed without the need for redo log generation:

You can set the whole tablespace to a non-logging mode if your tablespace is going to contain many segments that you'll typically want to manipulate with these commands (and not generate redo entries). You must do this before you build the segments, however, because a segment will acquire only the tablespace's logging mode at the time it's created.

You set the default logging mode for the tablespace with the ALTER TABLESPACE command, using the LOGGING or NOLOGGING option. When set, each new segment you create can accept this default behavior, or you can override it with the appropriate logging clause in the CREATE command.

Moving Data Files

There are generally two reasons to move a data file:

After you move a data file, you need to let the database know that the file has moved. You do this with a RENAME option of either the ALTER DATABASE or the ALTER TABLESPACE command. Generally, you use the former when the database is in a NOMOUNT mode, and you are in the process of recovering from media failure; you use the latter when you've completed a planned file move. In the latter case, you need to take the tablespace offline before physically moving the file and issuing the ALTER TABLESPACE...RENAME 'new_filename' TO 'old_filename' command.

You can rename more than one data file in a single statement as long as they all belong to the same tablespace. Use a comma-separated list of filenames on each side of the TO keyword, ensuring that there's a one-to-one match between the names. For example, the following command will move three files from the /d1 device to three different devices:

Oracle won't perform operating system file commands
It's important to remember that renaming a file is a two-step process. Oracle doesn't physically move or rename the file at the operating system level; you are responsible for making this change yourself before issuing the ALTER TABLESPACE ...RENAME command. 
ALTER TABLESPACE prod_tables
       RENAME '/d1/prod02.dbf',
              '/d1/prod03.dbf',
              '/d1/prod04.dbf'
       TO     '/d2/prod02.dbf',
              '/d3/prod03.dbf',
              '/d4/prod04.dbf'

Coalescing Free Space Manually

When there are multiple adjacent extents of free space in a tablespace, it can take longer for a new extent that spans these free extents to be created. If you monitor DBA_FREE_SPACE and notice that such free extents exist, you can manually coalesce them into one large free extent. You can issue the ALTER TABLESPACE...COALESCE command to combine the contiguous free extents in the tablespace on demand.

Automatic free-space coalescing
If you don't coalesce contiguous free space extents yourself, it will automatically be done for you by the background process SMON. The ALTER TABLESPACE... COALESCE option is provided because SMON may not work soon enough to be useful.

Avoiding Free Space Fragmentation

One way to avoid having free space extents of various sizes is to prevent anyone from creating segments in the tablespace without your supervision. You can then ensure that they use extents of the same size for every segment. If this isn't an option, you can help minimize the problem by setting a "model" size for extents in the tablespace. This model size represents the smallest extent allowed and also controls the size of larger extents by ensuring that they are all integer multiples of the model size. If you decide to set such a model value for your tablespace, use the ALTER TABLESPACE...MINIMUM EXTENT command, providing an integer for the size, in bytes, of the smallest allowable extent.

When MINIMUM EXTENT is set, every new extent added to the tablespace will be exactly the requested size, rounded up to the next Oracle block, or an integer multiple of that number of blocks. This sizing will override the tablespace's default storage clause, if necessary, as well as the storage options of the segment itself. Even manual extent allocations using such commands as ALTER TABLE...ALLOCATE EXTENT (SIZE...) will be controlled by the value MINIMUM EXTENT.

Managing Query-Only Tables

To avoid having to make backups of data files that contain non-changing data, you can define a tablespace and, consequently, its data files as read-only. Similar to putting a tablespace into backup mode, this freezes the related data files' header blocks. However, because there can be no changes to them, Oracle knows that these data files are current copies, no matter how long ago they were modified as read-only. Consequently, you can take a backup of such files and restore them, following media failure, at any time in the future without them needing any recovery information from the redo log files.

Backup guidelines for read-only tablespaces
You should back up the files in the tablespace as soon as possible every time you make a tablespace read-only; an earlier backup will still need to have redo applied to ensure that all changes before the change in status have been applied. Following a change to read/write again, you can still restore from the backup taken while it was read-only, provided that you have the redo generated following its change back to a read/write status.

If you later need to make changes to one or more tables in a read-only tablespace, you have to make the tablespace accessible for writes again. You use the commands ALTER TABLESPACE...READ ONLY and ALTER TABLESPACE...READ WRITE to make these changes.

Storage for Temporary Segments

Temporary segments-used to complete sorts too large for the memory allocated to them-are ephemeral objects. They're created when needed and dropped when their work is done. In some databases-particularly query-intensive ones-the overhead of creating and dropping temporary segments can cause a significant performance problem. You can alter this default behavior by defining the tablespace where the temporary segments are stored to contain only this type of segment. Now, rather than drop a temporary segment when its work is finished, Oracle will preserve it for use by another sort in the future.

If you didn't create the tablespace with this characteristic, you can issue the ALTER TABLESPACE...TEMPORARY to convert it to contain non-disappearing temporary segments. If the tablespace should happen to contain another type of segment, such as a table or index, you can't make this change. You can convert the tablespace if you need to add non-temporary segments or change the storage characteristics of the temporary segments in TEMPORARY tablespace. In this case you use the keyword PERMANENT in the ALTER TABLESPACE command. Any existing temporary segments will be dropped as they are when following their default behavior, and you'll be able to add any other type of required segment to the tablespace. You'll have to drop any of these segments ahead of time to reconvert the tablespace to TEMPORARY.

Closing your database releases all temporary segments
Temporary segment space isn't held over database shutdowns and restarts. Even the temporary segments stored in TEMPORARY-type tablespaces will have disappeared when you reopen a closed database.

Modifying Default Storage Values

The command ALTER TABLESPACE DEFAULT STORAGE allows you to change the default values assigned to the storage characteristics of segments created in the tablespace without their own, overriding, STORAGE clauses. You need to take care when issuing this command for a couple of reasons:

Adding and Resizing Data Files

You'll occasionally have to add space to an existing tablespace. This may be a planned or an unplanned occurrence:

For planned expansion, particularly those involving the addition of new disks, adding more data files is the best method for adding space to a tablespace. This allows you to add exactly the amount of space you need and to place it onto different disks from the existing files, thus avoiding possible disk contention. File-system files and raw partitions can be added by using the ALTER TABLESPACE...ADD DATAFILE command. As with the CREATE TABLESPACE command, you can add one or many files with the same statement. The file name(s) and size(s) specifications are just the same as in the CREATE TABLESPACE command discussed earlier in this chapter.

You can also use additional data files, as just discussed, for an unplanned expansion. In such cases, you may not be able to place the files on new, unused disk drives; you may have to find whatever space is available in the disk farm for the time being. Also, if you need to use raw partitions, you'll have to be able to create them yourself or have the system administrator build them for you-unless you already have spares available.

An alternative for an unplanned expansion is to let the data files grow themselves. This has to be done when you first add them to the tablespace, using the AUTOEXTEND clause with the CREATE or ALTER TABLESPACE commands' file specification. If you didn't set this option when you added the files to the tablespace, you can still increase the file's size by extending it manually. This command is ALTER DATABASE DATAFILE...RESIZE. (Notice that this is ALTER DATABASE, not ALTER TABLESPACE.) The RESIZE clause takes a single argument, indicating the number of bytes that you want the file to contain following successful execution of the command. This can either be a simple integer or an integer followed by K or M for kilobytes or megabytes, respectively.

Shrinking oversized data files
You can use ALTER DATABASE DATAFILE ...RESIZE to shrink, as well as to increase, the size of a data file. You can't reduce a file, however, unless there's empty space at the end of the file sufficient to remove the number of bytes needed to reach your desired size. The RESIZE option can't remove empty space from the middle of a file, and it won't remove blocks now assigned to a database object.

The ALTER DATABASE DATAFILE...RESIZE command will manipulate only the space requested. It won't cause the file to expand, or shrink, automatically in the future.

Dropping Tablespaces

Although not a common requirement, you may need to drop a tablespace. There are a few reasons you might need to do this:

In order to drop a tablespace, it must not contain any rollback segments being used by an active transaction. If it contains any segments at all, you must use the INCLUDING CONTENTS option to force Oracle to drop these segments along with the tablespace.

Dropping online tablespaces isn't recommended
Although you can drop a table-space while it's still online, I advise you to take it offline first. This will avoid interference with ongoing transactions that are using the contents of the table-space and save you from drop-ping a segment that's really still being used.

This is the DROP TABLESPACE command's full syntax: 

DROP TABLESPACE tablespace_name
     [INCLUDING CONTENTS]
     [CASCADE CONTRAINTS]

You'll need the CASCADE CONSTRAINTS option if the tablespace contains tables being dropped with the INCLUDING CONTENTS option and these tables are the parents, via referential integrity constraints, of tables in another tablespace.

SEE ALSO
For a complete discussion of database ARCHIVELOG modes,
To learn more about temporary segments,
To learn about referential integrity constraints and the concepts of parent/child tables,

Extent Allocation

After you build your tablespaces, you or your users will use them to store various types of segments. Some of these will almost certainly be added by you and some will be automatically created by the Oracle kernel. The others may be created by you or by the users, but their maintenance and space management may still be under your control in either case.

Part of the work involved in managing segment space allocation should be completed during the physical design of your database because it's related to the number and arrangement of your tablespaces. This topic is discussed earlier, in the section "Space Management Fundamentals"; the discussion that follows here assumes that you've already decided what type of segment is being placed where and concentrates on how new extents are added to these segments after they're created, or how unneeded space can be retrieved from segments to which it has already been allocated.

Comparing Dynamic and Manual Extent Allocation

Every type of Oracle segment (except the bootstrap segment, which is fixed in size at database-creation time) can grow automatically by default. The following sections examine how each segment type can have its growth controlled and how best to manage any required growth.

Temporary Segments

We begin this discussion with temporary segments because in many ways these are the easiest to manage-you have so little control over them. Temporary segments are created as needed by Oracle while it's processing SQL statements on behalf of a user's process. Temporary space is generally required by a process if performing a sort operation, although some types of joins and related activities also use temporary segments. This temporary disk space is used only when the SQL statement has insufficient memory in which to complete its processing.

All extent allocation to temporary segments is dynamic. In other words, it occurs automatically, without any specific commands from users. This is true whether a new temporary segment is being created or new extents are being added because the original size was insufficient to complete the task. Due to this completely automatic behavior, users in no way can give explicit instructions about the sizing or the number of extents in a temporary segment. Oracle always uses the storage information defined in the DEFAULT STORAGE clause of the tablespace for its temporary segments. In Chapter 6 "Managing Redo Logs, Rollback Segments, and Temporary Segments," you can find guidelines on how to determine good storage values for temporary segments.

By default, as soon as the operation that required the disk space is finished, the temporary segment is dropped and the blocks used by its extents are returned to tablespaces as free blocks. It's this behavior that gave temporary segments their name; they only acquire space for a short time and then return it.

Another option you should consider for your temporary tablespaces is creating or converting them to the TEMPORARY type. This will prevent Oracle from dropping temporary segments in the tablespace following the completion of the related SQL statements. Instead, the extents used in the segment are tracked in the data dictionary and made available to any server process that needs temporary space. New extents are added to the segments only if all the current extents are now in use by one or more users.

Closing your database releases all temporary segments
Temporary segment space isn't held over database shutdowns and restarts. Even the temporary segments stored in TEMPORARY-type tablespaces will have disappeared when you reopen a closed database.

By not forcing users to create and recreate temporary segments each time they're needed, their work can be completed much faster. In fact, you can save your users a lot of time when you first create a TEMPORARY-type tablespace by prebuilding all the extents the tablespace can hold. You can do this by performing a massive sort (if you have a table or set of tables large enough to join), or by running large sorts in a number of concurrent sessions. Make sure that the userid you use for these sorts is allotted to the temporary tablespace you're planning to populate.

Another benefit to using TEMPORARY-type tablespaces for your temporary segments is that Oracle enforces the use of same-size extents. All extents in such tablespaces are built based on the value of the NEXT parameter in the DEFAULT STORAGE clause.

Rollback Segments

As the DBA, you should create and manage rollback segments. You initially create a rollback segment with two or more extents and with extent sizes taken from the tablespace default values or from CREATE TABLESPACE's STORAGE clause. The behavior of the extents allocated to rollback segments is of interest here.

SEE ALSO
You can find detailed information about creating rollback segments on

Rollback segments store information that would be needed if a transaction were to roll back. Every part of a single transaction must be stored in the same rollback segment, and many transactions can share the same segment. In most cases, transactions generate the same amount of rollback information, so when a rollback segment reaches a certain size, its space is sufficient to support all the needed concurrent transactions. As these transactions complete, the space they were using is recycled and made available to new transactions. However, if the database gets very busy or suddenly needs to support one or more very long-running transactions, a rollback segment may need to grow by adding one or more extents. As with temporary segments, this allocation is dynamic; users have no control over it.

Rollback segments have one unique characteristic of space management not possessed by any other type of segment: They can shrink in size by dropping unnecessary extents. Suppose a rollback segment grew by adding extents in response to an unusual combination of concurrent long-running transactions. If before this it could handle its work load without additional space, it should be able to do so again without the need for additional space. If it's sharing a tablespace with other rollback segments, this space might be better used by one of the others, maybe also for a sudden increase in work. You can cause a rollback segment to return to this preferred size whenever it exceeds it by setting an OPTIMAL parameter value.

OPTIMAL is a special parameter
Whereas all the other storage parameters for a rollback segment can be inherited from the tablespace definition, OPTIMAL must be set with the STORAGE clause of the CREATE ROLLBACK SEGMENT or the ALTER ROLLBACK SEGMENT command.

Data and Index Segments

Segments designated to store table or index data can be created by you or by userids responsible for the applications that will use them. These segments can inherit all their tablespace's storage characteristics, just some of them, or none of them. When created, their storage characteristics can be changed for the most part; only the INITIAL and MINEXTENTS values are fixed for the life of the segment.

If a data or index segment runs out of space in its current extents, one of two things can occur: A new extent will be added by Oracle dynamically, or the SQL statement that required the extra space will fail. There are a number of reasons dynamic allocation could fail:

Adding space to allow a new extent to be created automatically when the failed SQL statement is re-executed was discussed earlier in "Adding and Resizing Data Files." This would address the two first causes of dynamic space allocation failure. Another option for handling the second problem is to change the value of the segment's NEXT storage option, causing it to create an extent that fits into a remaining free extent. A third option would be to allocate the extent manually. You use the ALTER TABLE ALLOCATE EXTENT clause to do this. The complete syntax is as follows:
Identifies which freelist group will manage blocks in extent (used for databases that use Oracle Parallel Server option)
Sets extent size, regardless of table's storage values
Identifies into which data file extent will be placed

This command has one additional benefit over changing the NEXT value. If you want, you can execute it a number of times, each time choosing a different size for the extent and a different data file into which it goes. This will allow you to prebuild extents that precisely fit the available free space until you have sufficient space allocated; this allows work on the segment to continue while a more permanent solution, such as additional disk space, is found.

You can take advantage of manual extent allocation with the ALLOCATE EXTENT option for reasons other than overcoming space limitations. For example, you may want to build a segment in a tablespace with many data files so that you guarantee that blocks from each data file will be used by the segment. To do this, you can create the table or index with a single extent and then use the DBA_EXTENTS data dictionary view to find out which data file contains this extent. Then, by successive use of the ALTER TABLE...ALLOCATE EXTENT command, you can place an additional extent into each data file belonging to the tablespace.

A final note on manual extent allocation
If you don't provide a size when manually allocating an extent, the extent will be sized as though it were created dynamically. If you use the SIZE clause, however, it won't override the dynamic sizing that would have occurred. If a table were going to build its next dynamic extent with 1,000 blocks and you manually add an extent of just 50 blocks, the next dynamically allocated extent would still acquire 1,000 blocks.

Releasing Unused Space

Occasionally you'll build a segment far larger than you need it to be-perhaps because the initial estimates made during the analysis and design phase were wrong, or because the nature of the application changed. If you need to regain the unused space, you have a variety of options.

First, when dealing with the case of rollback segments, you can use the ALTER ROLLBACK SEGMENT command to change the value of OPTIMAL. As long as you don't try to shrink it to a size less than its original size (that is, size of extent 1 + size of extent 2 + ... + size of extent MINEXTENTS), the rollback segment will, as it's used in future transactions, return to this size. As long as it doesn't consistently run out of space, it will attempt to maintain this size even if it temporarily grows beyond it. For a one-time fix, you do have the option of issuing the following command:

SHRINK can cause free extents of unequal size
This command will remove partial extents. Therefore, even if you've carefully built your tablespaces and segments to have equal-sized extents, the end result of this command can be an extent of a size smaller than planned and a piece of free space larger than the expected extent size.

ALTER ROLLBACK SEGMENT...SHRINK [TO integer [K|M]]

For table, cluster, and index segments, you can remove unused space with the ALTER command's DEALLOCATE UNUSED clause. This command removes any unused extents and blocks as long as the original extents, set with the MINEXTENTS value in the CREATE command, aren't involved. You can even remove some empty space with the optional KEEP clause. This will save some allocated space to allow for some future growth without further extent allocation.

A second option to remove excessive space from a table-one that will preserve extent sizes-is to move the data into a temporary table, drop all the extents (other than the original ones) from the table, and then move the rows back into it. The following code shows a session that performs exactly these actions on the UNFILLED_ORDERS table. The key commands are the CREATE TABLE...AS SELECT and TRUNCATE commands. 

CREATE TABLE temp AS SELECT * FROM unfilled_orders
/
TRUNCATE TABLE unfilled_orders
/
INSERT INTO unfilled_orders SELECT * FROM temp
/
DROP TABLE temp
/

To drop unused space from an index, you can simply use the ALTER INDEX command's REBUILD option. The only restriction you need to be concerned with when using this command is that the original and the replacement index copies will temporarily have to exist at the same time. This means that you'll need space for the new version of the index to be built in the target tablespace, which may not be the same as the current one.

Defragmenting Free Space

If you have a database that for whatever reason doesn't follow the guidelines discussed in this chapter about maintaining equal-sized extents in a tablespace, you may find yourself with a badly fragmented tablespace. This tablespace contains a lot of free space, but each piece, or extent, of free space is only a few blocks big, too small to be usefully added to any of the segments in the tablespace.

For tablespaces of type TEMPORARY, this will only occur should the default storage settings have been changed before the tablespace is completely filled. To fix it, you can alter the tablespace to be a PERMANENT tablespace again. This will cause all the temporary segments to be freed over time and new ones built in their place. When all the odd-sized extents have been removed, you can convert the tablespace back to type TEMPORARY and allow the new segments to grow back to the necessary sizes by using fixed-sized extents.

For rollback segment tablespaces, your best option is to temporarily provide additional rollback segments to give you a chance to drop and recreate the current rollback segments, choosing appropriate storage values in your CREATE command. If you already have rollback segments available in another tablespace, you may be able to make the changes without adding temporary ones. If you plan to do this, try to drop and recreate the problem rollback segments during a period of low use. That way users aren't held up because an insufficient number of rollback segments are available to support them.

The most difficult fragmentation problems to fix are those associated with tables and indexes. If you can afford to drop all the indexes in the tablespace temporarily and then rebuild them, this is the easiest way to solve the problem. However, if the tablespace contains tables or other types of segments besides the indexes, you need to deal with the larger problem. Similarly, if the indexes can't all be dropped, you don't have a simple method to solve the issue.

Need to defragment a tablespace?
If you have to defragment a table-space, I strongly recommend that you reconsider your tablespace usage before reloading anything. You may want to change all the segment storage clauses so that they all have equal-sized extents, or you may want to add more tablespaces to meet the design suggestions offered earlier in this chapter. As your database grows in size, the inconvenience to you and to your users will increase should you need to perform future defragmentation processing.

Fragmented tablespaces containing tables, or tables and other types of objects, are very difficult to handle. Some third-party tools are available. Without them, you're going to use a tool to store the contents of the tablespace in some type of temporary storage, drop the tablespace contents, and then restore the original contents by using new segments with appropriate sizes. Oracle offers the Export and Import utilities to help you do this. You can also build your own tools to unload data, table definitions, and the like, and then use a combination of SQL, SQL script files, and SQL*Loader to reload the tablespace.

© Copyright, Macmillan Computer Publishing. All rights reserved.